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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 09:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com