Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
General question about using "worksheet" when select from the VBA Editor?
question about Private Sub Worksheet_SelectionChange(ByVal Target As
Range) This probably seems basic to you, but Im trying to create a spreadsheet that would need to be updated as the user changes/manipulates the worksheet. The only way I see to change things as the user manipulates is to use the if else statement. Is there a more general statement that can be used. I have tried ..selection. The for, while, case select dont work as I intended When something is changed/moved/manipulated on that sheet does what you have coded run from the very top all the time? This is difficult to explain, but any advice is appreciated Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
General question about using "worksheet" when select from the VBA Editor?
Hi Susan,
See Chip Pearsons notes on Event procedures at: http://www.cpearson.com/excel/events.htm --- Regards, Norman "Susan Hayes" wrote in message ... question about Private Sub Worksheet_SelectionChange(ByVal Target As Range) This probably seems basic to you, but Im trying to create a spreadsheet that would need to be updated as the user changes/manipulates the worksheet. The only way I see to change things as the user manipulates is to use the if else statement. Is there a more general statement that can be used. I have tried .selection. The for, while, case select dont work as I intended When something is changed/moved/manipulated on that sheet does what you have coded run from the very top all the time? This is difficult to explain, but any advice is appreciated Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
General question about using "worksheet" when select from the VBA Editor?
Susan,
I am not really sure what you are asking, but a few thoughts. Worksheet_SelectionChange is triggered when a cell is selected. If you want to catch when a cell is changed, then you need the Worksheet_Change event. In itself this has nothing to do with IF/Else, that just becomes a control mechanism within the code, albeit there are others. Often you only want to test for changes within a certain area of the worksheet and you can use the intersect method for this If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then which is only true if the changed cell is in the range A1:H10. If you want to test for it has been changed to a particular value, you can use If/ElseIf/Else/Endif If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then With Target If .Value =2 Then .Value = 100 ElseIf .Value = 3 Then .Value = 1000 Else .Value = "Error" End If End With End If which should be quite simple to follow. But you can also use Case statements If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then With Target Select Case .Value Case 2: .Value = 100 Case 3: .Value = 1000 Case Else: .Value = "Error" End Select End With End If which is more elegant IMO. Another thing to be wary of is stopping your changes re-firing the event. This is done by adding Application.EnableEvents = False at the start, and re-set at the end. Putting all this together, here is a simple example that checks a range for a value (planetary in this case, and sets the cell colour accordingly) Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit If Not Intersect(Target, Range("A3:H9")) Is Nothing Then With Target Select Case LCase(Target.Value) Case "mars": .Interior.ColorIndex = 3 'Red Case "moon": .Interior.ColorIndex = 5 'Blue Case "sun": .Interior.ColorIndex = 6 'Yellow Case "saturn": .Interior.ColorIndex = 10 'Green Case "venus": .Interior.ColorIndex = 45 'orange End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Susan Hayes" wrote in message ... question about Private Sub Worksheet_SelectionChange(ByVal Target As Range) This probably seems basic to you, but Im trying to create a spreadsheet that would need to be updated as the user changes/manipulates the worksheet. The only way I see to change things as the user manipulates is to use the if else statement. Is there a more general statement that can be used. I have tried .selection. The for, while, case select dont work as I intended When something is changed/moved/manipulated on that sheet does what you have coded run from the very top all the time? This is difficult to explain, but any advice is appreciated Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
General question about using "worksheet" when select from the
dear bob,
i read you informative reply to susain's query about tracking changes in an execl file. Actually i am working on a project where i have to maintain audit trail on a web-based excel . Do you think that this intersect method will be helpful in web based project's environment ? i'll be greatfull if you can take out some time and reply to this query . others are also equally welcome to give their opinions so that i can proceed with my project. thanks in advance with kind regards anugrah atreya Softsolvers Technologies "Bob Phillips" wrote: Susan, I am not really sure what you are asking, but a few thoughts. Worksheet_SelectionChange is triggered when a cell is selected. If you want to catch when a cell is changed, then you need the Worksheet_Change event. In itself this has nothing to do with IF/Else, that just becomes a control mechanism within the code, albeit there are others. Often you only want to test for changes within a certain area of the worksheet and you can use the intersect method for this If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then which is only true if the changed cell is in the range A1:H10. If you want to test for it has been changed to a particular value, you can use If/ElseIf/Else/Endif If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then With Target If .Value =2 Then .Value = 100 ElseIf .Value = 3 Then .Value = 1000 Else .Value = "Error" End If End With End If which should be quite simple to follow. But you can also use Case statements If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then With Target Select Case .Value Case 2: .Value = 100 Case 3: .Value = 1000 Case Else: .Value = "Error" End Select End With End If which is more elegant IMO. Another thing to be wary of is stopping your changes re-firing the event. This is done by adding Application.EnableEvents = False at the start, and re-set at the end. Putting all this together, here is a simple example that checks a range for a value (planetary in this case, and sets the cell colour accordingly) Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit If Not Intersect(Target, Range("A3:H9")) Is Nothing Then With Target Select Case LCase(Target.Value) Case "mars": .Interior.ColorIndex = 3 'Red Case "moon": .Interior.ColorIndex = 5 'Blue Case "sun": .Interior.ColorIndex = 6 'Yellow Case "saturn": .Interior.ColorIndex = 10 'Green Case "venus": .Interior.ColorIndex = 45 'orange End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Susan Hayes" wrote in message ... question about Private Sub Worksheet_SelectionChange(ByVal Target As Range) This probably seems basic to you, but Im trying to create a spreadsheet that would need to be updated as the user changes/manipulates the worksheet. The only way I see to change things as the user manipulates is to use the if else statement. Is there a more general statement that can be used. I have tried .selection. The for, while, case select dont work as I intended When something is changed/moved/manipulated on that sheet does what you have coded run from the very top all the time? This is difficult to explain, but any advice is appreciated Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
General question about using "worksheet" when select from the
dear bob,
i read you informative reply to susain's query about tracking changes in an execl file. Actually i am working on a project where i have to maintain audit trail on a web-based excel . Do you think that this intersect method will be helpful in web based project's environment ? i'll be greatfull if you can take out some time and reply to this query . others are also equally welcome to give their opinions so that i can proceed with my project. thanks in advance with kind regards anugrah atreya Softsolvers Technologies "Bob Phillips" wrote: Susan, I am not really sure what you are asking, but a few thoughts. Worksheet_SelectionChange is triggered when a cell is selected. If you want to catch when a cell is changed, then you need the Worksheet_Change event. In itself this has nothing to do with IF/Else, that just becomes a control mechanism within the code, albeit there are others. Often you only want to test for changes within a certain area of the worksheet and you can use the intersect method for this If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then which is only true if the changed cell is in the range A1:H10. If you want to test for it has been changed to a particular value, you can use If/ElseIf/Else/Endif If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then With Target If .Value =2 Then .Value = 100 ElseIf .Value = 3 Then .Value = 1000 Else .Value = "Error" End If End With End If which should be quite simple to follow. But you can also use Case statements If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then With Target Select Case .Value Case 2: .Value = 100 Case 3: .Value = 1000 Case Else: .Value = "Error" End Select End With End If which is more elegant IMO. Another thing to be wary of is stopping your changes re-firing the event. This is done by adding Application.EnableEvents = False at the start, and re-set at the end. Putting all this together, here is a simple example that checks a range for a value (planetary in this case, and sets the cell colour accordingly) Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit If Not Intersect(Target, Range("A3:H9")) Is Nothing Then With Target Select Case LCase(Target.Value) Case "mars": .Interior.ColorIndex = 3 'Red Case "moon": .Interior.ColorIndex = 5 'Blue Case "sun": .Interior.ColorIndex = 6 'Yellow Case "saturn": .Interior.ColorIndex = 10 'Green Case "venus": .Interior.ColorIndex = 45 'orange End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Susan Hayes" wrote in message ... question about Private Sub Worksheet_SelectionChange(ByVal Target As Range) This probably seems basic to you, but Im trying to create a spreadsheet that would need to be updated as the user changes/manipulates the worksheet. The only way I see to change things as the user manipulates is to use the if else statement. Is there a more general statement that can be used. I have tried .selection. The for, while, case select dont work as I intended When something is changed/moved/manipulated on that sheet does what you have coded run from the very top all the time? This is difficult to explain, but any advice is appreciated Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
General question about using "worksheet" when select from the
dear bob,
i read you informative reply to susain's query about tracking changes in an execl file. Actually i am working on a project where i have to maintain audit trail on a web-based excel . Do you think that this intersect method will be helpful in web based project's environment ? i'll be greatfull if you can take out some time and reply to this query . others are also equally welcome to give their opinions so that i can proceed with my project. thanks in advance with kind regards anugrah atreya Softsolvers Technologies |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
General question about using "worksheet" when select from the
dear bob ,
i read your informative articale. can you please advice that if i can use this same approach to generate audit trail of changes made to any workbook in web-based excel environment. thanks in advance. with kind regards anugrah atreya "Bob Phillips" wrote: Susan, I am not really sure what you are asking, but a few thoughts. Worksheet_SelectionChange is triggered when a cell is selected. If you want to catch when a cell is changed, then you need the Worksheet_Change event. In itself this has nothing to do with IF/Else, that just becomes a control mechanism within the code, albeit there are others. Often you only want to test for changes within a certain area of the worksheet and you can use the intersect method for this If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then which is only true if the changed cell is in the range A1:H10. If you want to test for it has been changed to a particular value, you can use If/ElseIf/Else/Endif If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then With Target If .Value =2 Then .Value = 100 ElseIf .Value = 3 Then .Value = 1000 Else .Value = "Error" End If End With End If which should be quite simple to follow. But you can also use Case statements If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then With Target Select Case .Value Case 2: .Value = 100 Case 3: .Value = 1000 Case Else: .Value = "Error" End Select End With End If which is more elegant IMO. Another thing to be wary of is stopping your changes re-firing the event. This is done by adding Application.EnableEvents = False at the start, and re-set at the end. Putting all this together, here is a simple example that checks a range for a value (planetary in this case, and sets the cell colour accordingly) Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit If Not Intersect(Target, Range("A3:H9")) Is Nothing Then With Target Select Case LCase(Target.Value) Case "mars": .Interior.ColorIndex = 3 'Red Case "moon": .Interior.ColorIndex = 5 'Blue Case "sun": .Interior.ColorIndex = 6 'Yellow Case "saturn": .Interior.ColorIndex = 10 'Green Case "venus": .Interior.ColorIndex = 45 'orange End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Susan Hayes" wrote in message ... question about Private Sub Worksheet_SelectionChange(ByVal Target As Range) This probably seems basic to you, but Im trying to create a spreadsheet that would need to be updated as the user changes/manipulates the worksheet. The only way I see to change things as the user manipulates is to use the if else statement. Is there a more general statement that can be used. I have tried .selection. The for, while, case select dont work as I intended When something is changed/moved/manipulated on that sheet does what you have coded run from the very top all the time? This is difficult to explain, but any advice is appreciated Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format changes from "General" to "Text" | Excel Discussion (Misc queries) | |||
how do i reset the default from "general" to "number" | Excel Discussion (Misc queries) | |||
"general" cells turned into "accounting" - why and how to reverse | Excel Worksheet Functions | |||
"Control" plus "click" doesn't allow me to select multiple cells | New Users to Excel | |||
Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming |