Best practice for executing code

[Steven Sutton]

I am rather new to programming in Excel so I am hoping someone will help me
out here. I want to execute certain subroutines depending on which cell the
user has just entered data into. Basically I want to check the user's input
for validity. From what little I know of Excel programming I am thinking of
doing something like this in the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target as Excel.Range)

Dim t as Range
Dim r as Range
Dim i as integer

t = Target
r = Range("A:C") 'where columns A through C are the ones I want to check

i = Intersect(t,r)

Select Case i
Case 1
'insert code for if user has left Column A
Case 2
'Code for if user has left Column B
Case 3
'Code for if user has left Column C
Case Else
exit sub
End Select


Does this make sense? Is there a better way to execute code when a user
leaves a cell?

As always, I thank everyone for there time and assistance.

 

[Chip Pearson]

Unless you're coding in NET, you're missing some Set functions.

t = Target
should be
Set t = Target

r = Range("A:C")
should be
Set r = Range("A:C")

i = Intersect(t,r)
should be
Set i = Intersect(t,r)

Next, you need to get the column number of i in your Select statement.

Select Case i
should be
Select Case i.Column

Beyond that, your code looks reasonably good. You'll want to disable events
before your code makes any changes to the worksheet.

Application.EnableEvents = False
' code
Application.EnableEvents = True

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Ronald R. Dodge, Jr.]

Target.Column gives you the column number of the first column of the range
the user modified data.

Most times, user will edit one cell at a time, but be careful for those
times when users may edit multiple cells at a time such as when pressing
"Ctrl-Enter" (Enter the same data/formula into all selected cells),
"Alt-Ctrl-Enter" (Enter an array formula into all selected cells), "Ctrl-D"
(FillDown within selected cells, though from the row above, if only one row
is selected), Ctrl-Insert (Paste into selected cells), and so on.

For this reason, you may need to use

Target.Columns.Count to know how many different columns were editted at one
time with Target.Column being the left most column editted.

There may even be one of those rare cases of multiple different ranges
editted at a time, thus would need to use:

Target.Areas.Count

to see if just one range was used or if multiple ranges were used at the
time of edited cells.

Target.Areas(I)

The above is a range object, which you can use for the same set of
properties as on a single range.

If you need to set a range to a range object, just as shown in the
Sheet_Change event, you can use the following lines to declare, then
initialize.

Dim rngCurrent as Excel.Range, I as Long

For I = 1 to Target.Areas.Count Step 1
Set rngCurrent = Target.Areas(I)
<Perform your statements as needed>
Next I
Set rngCurrent = Nothing

As you can see, once an object variable is no longer in use, it's best to
reset it back to nothing.