Code to run on worksheet selection change

[dan winterton]

I am programming code to run whenever the user changes
cells within an excel spreadsheet. How can I have the
code check to see what cell the user just selected, assign
this to a variable, then run a select case statement on
that variable?

Here's where I am so far:

Private Sub Worksheet_SelectionChange(ByVal Target As
Excel.Range)
Dim CurrentRange As Range

'CurrentRange = ********This is where I need help,
assigning the variable**********

Select Case CurrentRange
Case... 'do stuff depending on which cell is selected
Case... '
End Select
End Sub

Any help is much appreciated.
Dan Winterton

 

[Bernie Deitrick]

Dan,

"Target" is the range object that is set to the newly selected range - from
the (ByVal Target As Excel.Range) part of the declaration line.

Usually, you can do something like this to avoid multiple cell selections,
as the first line of code within your event:

If Target.Cell.Count > 1 Then Exit Sub

Then simply use Target.Value in your Select Case

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Sorry,

If Target.Cell.Count > 1 Then Exit Sub

Should be

If Target.Cells.Count > 1 Then Exit Sub

HTH,
Bernie
MS Excel MVP

 

[Tim Williams]

Dan,

In addition to Bernie's advice you might also consider arranging your sub as
below. When using the worksheet_change event with fairly complex code I
sometimes run into problems when the user hold down an arrow key and the
event seems to fire again before it's finished processing the first one.
The static variable prevents this "reentry". Just make sure to reset the
flag following any error which might occur in the routine.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Static bProcessing as boolean 'flag to prevent "re-entry"

if bProcessing then

exit sub

else
bProcessing=true
'..... do your stuff here
bProcessing=false
end if

End Sub

Cheers,
Tim.