Why does this work ?
Posted by Al on December 28, 2001 5:02 AM
Ive got the following tip from this board. It works fine when you enter the values on the second column manually, but why does it work when the values entered by a OptionsButton from FORMS menu ?
thanks
-al
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 2 Then
ThisRow = Target.Row
If Target.Value = 1 Then
Range("A" & ThisRow).Interior.ColorIndex = 3
ElseIf Target.Value = 2 Then
Range("A" & ThisRow).Interior.ColorIndex = 4
ElseIf Target.Value = 3 Then
Range("A" & ThisRow).Interior.ColorIndex = 5
ElseIf Target.Value = 4 Then
Range("A" & ThisRow).Interior.ColorIndex = 6
Else
Range("A" & ThisRow).Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub
Posted by Al on December 28, 2001 5:41 AM
Re: Why DOESNT this work ?
Posted by Mark O'Brien on December 28, 2001 6:41 AM
Re: Why DOESNT this work ?
The code is run when an "event" takes place. The event that occurs for this code to run is a change on the worksheet. e.g. change of value when you manually type it in a cell, or from a custom userform.
The reason your code doesn't run when you use a Data|Forms form, is because entering data in this way does not trigger a "Change" event. I discovered this last week when trying to help someone with the exact same problem. I still have no reason why the change event isn't raised no matter what method is used to update the spreadsheet. To be honest I don't really know a workaround either, other than to use a custom userform instead of the built in Data|Forms. : Ive got the following tip from this board. It works fine when you enter the values on the second column manually, but why does it work when the values entered by a OptionsButton from FORMS menu ? : thanks
Posted by Al on December 28, 2001 7:21 AM
Re: Why DOESNT this work ?
Mark, How do i build custom userforms then ?
or is there anyway to fool XL to raise its change event? Better yet, can i associate this macro with another event such that OptionButton action is recognized ? I have 200+ OptionButton in a single sheet :( The code is run when an "event" takes place. The event that occurs for this code to run is a change on the worksheet. e.g. change of value when you manually type it in a cell, or from a custom userform. The reason your code doesn't run when you use a Data|Forms form, is because entering data in this way does not trigger a "Change" event. I discovered this last week when trying to help someone with the exact same problem. I still have no reason why the change event isn't raised no matter what method is used to update the spreadsheet. To be honest I don't really know a workaround either, other than to use a custom userform instead of the built in Data|Forms.
Posted by Mark O'Brien on December 28, 2001 9:23 AM
Re: Why DOESNT this work ?
Al,
Could you post some code that is run when the option buttons are selected/deselected.
I misread your question before and I might have a solution. (It's a slow day at work). : Ive got the following tip from this board. It works fine when you enter the values on the second column manually, but why does it work when the values entered by a OptionsButton from FORMS menu ? : thanks
Posted by Ivan F Moala on December 28, 2001 1:48 PM
Re: Why DOESNT this work ?
Use the calculate event to trigger it
Just have formulas linked to the cells sothat
they calculate and therefore triggers the
calculate event.
Ivan
Posted by Al on January 01, 2002 3:06 AM
Re: Why DOESNT this work ?
Ivan,
works now !!! thanks a bunch Use the calculate event to trigger it
