ActiveX option buttons...

[bobmc]

OK, I'm delving into new territory with this...

I've figured out how to put ActiveX option buttons into a worksheet and they work great. Is it possible to have the selection from a group of ActiveX option buttons return to a cell on the worksheet?

Here's my example...

I have four option buttons contained in the GroupName "Vegetable". The four buttons are named "Corn", "Carrots", "Peas" & "Beans". When the user makes their selection, I'd like the selection to show up in a cell, say A1. What is the formula in A1?

Thanks.

bobmc

 

[chiello]

Try this solution:
Add the following code into the Code Module relevant to the Sheet containing the option buttons:

Private Sub OptionButton1_Click()
Range("A1") = OptionButton1.Caption
End Sub

Private Sub OptionButton2_Click()
Range("A1") = OptionButton2.Caption
End Sub

Private Sub OptionButton3_Click()
Range("A1") = OptionButton3.Caption
End Sub

Private Sub OptionButton4_Click()
Range("A1") = OptionButton4.Caption
End Sub

In the above code it is supposed that the (Name) Property for the 4 option buttons is OptionButton1, OptionButton2, OptionButton3 and OptionButton4.

Post for feedback

Ciao

 

[bobmc]

Thank you, Chiello!

It works wonderfully!

bobmc

 

[bobmc]

One more question on this...

Is it possible to make the target cell relative in that I would be able to cut and paste the target cell to move it at my leisure without having to go into VB?

Thanks.

bobmc

 

[macleanb]

Hi bobmc

I think maybe chiello has gone for a cup of coffe - so I will step in - I wish I could get a cup of real italian coffe

Basically as it comes - no - you cant - VB does not update with worksheet changes like a formula would.

If you want to be sneaky you could use another cell to hold the adress of the cell you want to interogate - something like:


Range(Range("B1").Value).Value = "whatever"

where B1 contains:

=ADDRESS(ROW(A1),COLUMN(A1))

 

[bobmc]

Ah! Very good, macleanb! I'm always up for sneaky.

I've got another one for your signature...how about,

"Why do people start sentences with "Now then..."?

Thanks macleanb.

bobmc