Checking valus in cells

  • Thread starter Thread starter Niklas
  • Start date Start date
N

Niklas

Hi
I have a sheet with several cells that I want to check if
they are valid or not. At the moment I have predefined
cells which I check, but I do not like that solution. I
want a more dynamic one. Is it possible to "tag" thoose
cells I want to check and with some code retrive those
taged cells as a Range? I want the "taged" cells to appear
all over the sheet?
Regards
/Niklas
 
Maybe you could select your cells and give it a nice range name. Then you could
use that range name in your code.
 
Hello Niklas,

With Excel versions before Excel 2002 no single range can contain cells
scattered over a worksheet.

There is a WorksheetChange event that fires when a cell's contents change.
When Excel calls that it provides the range as a "target".
In the Worksheet.Change procedure you can use the

Select Case testexpression
[Case expressionlist-n
[statements-n]] ...
[Case Else
[elsestatements]]
End Select

VBA/VB command to test the target to determine whether it is one of the
cells you are managing.
There is a similar event that fires on Worksheet.SelectionChange
and another for Workbook.SelectionChange which receives the Worksheet
reference and the range reference.
--------------------
Message-ID: <[email protected]>
Date: Thu, 07 Aug 2003 21:34:43 -0500
From: Dave Peterson <[email protected]>
Reply-To: (e-mail address removed)
X-Mailer: Mozilla 4.78 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Checking valus in cells
References: <[email protected]>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.excel.programming
NNTP-Posting-Host: 1Cust13.tnt2.belvidere.il.da.uu.net 67.200.45.13
Lines: 1
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:404997
X-Tomcat-NG: microsoft.public.excel.programming

Maybe you could select your cells and give it a nice range name. Then you could
use that range name in your code.


Hi
I have a sheet with several cells that I want to check if
they are valid or not. At the moment I have predefined
cells which I check, but I do not like that solution. I
want a more dynamic one. Is it possible to "tag" thoose
cells I want to check and with some code retrive those
taged cells as a Range? I want the "taged" cells to appear
all over the sheet?
Regards
/Niklas
Click to expand...
Click to expand...

Regards,
Chris Jensen[MSFT]

This posting is provided “AS IS” with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
<http://www.microsoft.com/security>.
 
With Excel versions before Excel 2002 no single range can contain cells
scattered over a worksheet.
Click to expand...


In xl 2000 and xl 97 this would work fine:

? Range("A1,B9,C21,Z65534,M23").address
$A$1,$B$9,$C$21,$Z$65534,$M$23

Range("A1,B9,C21,Z65534,M23").Name = "Scattered"
? range("Scattered").Address
$A$1,$B$9,$C$21,$Z$65534,$M$23


could you clarify your statement. Did you mean scattered across a workbook
as in being on several sheets?

--
Regards,
Tom Ogilvy

Chris Jensen said:
Hello Niklas,

With Excel versions before Excel 2002 no single range can contain cells
scattered over a worksheet.

There is a WorksheetChange event that fires when a cell's contents change.
When Excel calls that it provides the range as a "target".
In the Worksheet.Change procedure you can use the

Select Case testexpression
[Case expressionlist-n
[statements-n]] ...
[Case Else
[elsestatements]]
End Select

VBA/VB command to test the target to determine whether it is one of the
cells you are managing.
There is a similar event that fires on Worksheet.SelectionChange
and another for Workbook.SelectionChange which receives the Worksheet
reference and the range reference.
--------------------
Message-ID: <[email protected]>
Date: Thu, 07 Aug 2003 21:34:43 -0500
From: Dave Peterson <[email protected]>
Reply-To: (e-mail address removed)
X-Mailer: Mozilla 4.78 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Checking valus in cells
References: <[email protected]>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.excel.programming
NNTP-Posting-Host: 1Cust13.tnt2.belvidere.il.da.uu.net 67.200.45.13
Lines: 1
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:404997
X-Tomcat-NG: microsoft.public.excel.programming

Maybe you could select your cells and give it a nice range name. Then
Click to expand...
you
could
use that range name in your code.
Click to expand...

Regards,
Chris Jensen[MSFT]

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
<http://www.microsoft.com/security>.
Click to expand...
 
Back
Top