Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to run on worksheet selection change
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to run on worksheet selection change
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 "dan winterton" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to run on worksheet selection change
Sorry,
If Target.Cell.Count 1 Then Exit Sub Should be If Target.Cells.Count 1 Then Exit Sub HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 "dan winterton" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to run on worksheet selection change
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. "dan winterton" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Quick VBA Worksheet Change Event or Selection Question: | Excel Worksheet Functions | |||
CHANGE DEFAULT PRINT TO SELECTION INSTEAD OF ACTIVE WORKSHEET | New Users to Excel | |||
Running code on a drop down selection change | Excel Worksheet Functions | |||
Worksheet change code | Excel Programming | |||
Worksheet Selection Change | Excel Programming |