Worksheet_Change event not running from Form control.

[Brad E.]

I have a Worksheet_Change event set up and running great when I change cell
C7. Then I set up three (Form) radio buttons linked to C7. When I push the
radio buttons, C7 is changing, but the Worksheet_Change event doesn't get
called anymore. I know I could also assign a macro to the Form, but I want
to end up with 12 sets of 3 radio buttons, linked to 12 individual cells. It
would be easy to keep "Target" useful since these 12 cells are all over the
spreadsheet.

Thanks.

 

[JLGWhiz]

I put a radio button from the Control Tool Box on a sheet and put the code
below behind the sheet. I could not duplicate the problem. The option
button code triggered the change event each time.

Private Sub OptionButton1_Click()
If Range("B3") < 5 Then
Range("B3") = Range("B3").Value + 1
Else
Range("B3") = Range("B3").Value - 1
End If
Sheets(1).OptionButton1 = False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("B3") Then
MsgBox "Bingo"
End If
End Sub

 

[Brad E.]

JLG - If I understand correctly, you are running a macro when you select the
radio button - which in turn changes B3 - which in turn triggers the
Worksheet_Change event. Mine is set up a little differently in that I have
linked my 3 radio buttons (grouped) to cell C7. So when I push the first
radio button, C7 changes to 1, second and C7 = 2, third and C7 = 3. These
changes are not triggering the Worksheet_Change event though.

 

[JLGWhiz]

No, my setup is for the manual click of the radio button to change the value
in cell b3, which then triggers the message box. If you would post your
code, maybe we could help debug the problem.

 

[JLGWhiz]

If you are trying to change the value in the linked cell with the option
button, you could be cancelling you own value. The linked cell for an option
button only returns True or False, boolean results. You would need to use a
Target cell to activate the change event. I cannot determine much more
without seeing the relevant code.

 

[Brad E.]

Sorry to keep second guessing you JLG, but in your first reply you posted
your OptionButton1_Click() coding, so we are still different. I am not
running a macro for the click of the radio button(s). I have right-clicked
on one of the three buttons > Format Control > Control tab > Cell Link = $C$7
is how I am getting C7 to change. I also did a search in this forum before
my original post, and found only one other post which addressed the form
controls (with cell link) not running the Worksheet_Change event.

 

[JLGWhiz]

OK, I think I understand now that you are trying to use the True/Fale value
of the option button as a trigger for the underlying Change event code. I
don't think that will work, because VBA sees the linked cell change as
similar to a calculation and a calculation will not trigger the change event.
I would not use the link, but instead use code behind the button similar to
how I set up the first test. That will trigger the change event.

 

[Brad E.]

Thanks, JLG. I wanted to stay away from that because I will end up with 36
radio buttons (12 sets of 3). I'll do what I have to.

Thanks again,