Option Button "Linked Cell" Property

[K. Georgiadis]

Does the "Linked Cell" property in an Option Button (Activex Control) work in the same fashion as the linked cell in used an option button in the Forms toolbar?

For example, with the invaluable help of Carlos Paleo, I am refining the design of a worksheet whereby two option buttons will hide or unhide two sets of rows. Depending on the selection made by the user, one of the two sets of rows will be aggregated on the bottom of the worksheet.

If I were working with an option button from Forms, I would enter a simple formula choosing the rows to aggregate depending on whether the linked cell displayed 1 or 2. How does this work with an Activex control?

[Jacob Hilderbrand]

For an ActiveX Option Button I would just add code to the click event. Then check if the value is true or false and add your code for what you want to have happen depending on that.

[K. Georgiadis]

My VBA coding skills are rather rudimentary. Can you show me sample code?

[Jacob Hilderbrand]

Make two Option Buttons the paste this code in the worksheet code module for the worksheet that has the Option Buttons. You can right click on the sheet tab and select View Code to get to the appropriate code window.


Option Explicit

Private Sub OptionButton1_Click()
If Me.OptionButton1.Value = True Then
MsgBox "You presses OptionButton1"
Else
MsgBox "You presses OptionButton2"
End If
End Sub

Private Sub OptionButton2_Click()
If Me.OptionButton1.Value = True Then
MsgBox "You presses OptionButton1"
Else
MsgBox "You presses OptionButton2"
End If
End Sub

[Zack Barresse]

Fwiw, there are subtle differences between ActiveX (control toolbar) and Forms (toolbar) controls. ActiveX controls do not work with Mac's. They also have code associated with them which is located in the sheet on which the control is located. A Forms control can have a macro 'assigned' to it; such code would be located in a Standard Module. So by virtue, ActiveX controls are inherently more associated with events on a more singular (worksheet) level.

If there is not a specific reason to use one or the other, I guess it's just personal preference.

And Jake, tag. You're it (http://www.vbaexpress.com/forum/showthread.php?p=16507#post16507). ;)

[K. Georgiadis]

Jake, thanks for the post. I understand however that your code will simply cause message boxes to pop up, depending on whether the user clicked on Option Button 1 or Option Button 2.

What I really need is for the option buttons to place a distinguishing value in cell G4 (which happens to be available!); this could be a TRUE or FALSE statement, or the values 1 or 2 (indicating whether option box 1 or 2 was clicked). This will enable me to use a logical Excel function to determine which rows should be summed up. For example, if the value in cell G4 is "1", rows 5, 10, and 15 will be aggregated; if it is "2", rows 6, 11, and 16 will be aggregated.

[Paleo]

Hi K.,

you may add one line to the code I had provided you in the other thread.

In Private Sub OptionButton1_Click() add as the last line:


Range("G4") = True

And for the other button:


Range("G4") = False


If this is for another worksheet use:


Private Sub OptionButton1_Click()
Range("G4") = True
End Sub

Private Sub OptionButton2_Click()
Range("G4") = False
End Sub

[Jacob Hilderbrand]

The message box was just an example. You can use a Select Case statement to chech for values.


Private Sub OptionButton1_Click()
If Me.OptionButton1.Value = True Then
Select Case Range("G4").Value
Case Is = TRUE
Case Is = FALSE
Case Is = 1
Case Is = 2
End Select
End If
End Sub

Then you can add whatever code you want for each case.

[K. Georgiadis]

It works great, as does the code you provided for the two option buttons. Since I'm trying to learn as I do, could you explain how your code operates? For example. what does this line do?


For i = 5 To Range("A65536").End(xlUp).Row

Thanks!

[Paleo]

Hi K.,

this line loops from all the rows starting at row 5 and going up till the last row used in column A.

Lets analyse it in parts:

Range("A65536") -> goes to the last row in column A

End(xlUp) -> its the same as pressing end and the up key in the keyboard, this makes your selection go to the last occupied cell in this column

Row -> returns the row number

So it makes: For i = 5 To Last_occupied_row

[K. Georgiadis]

Thank you Carlos and Jake!

[Paleo]

You are welcome!

[Jacob Hilderbrand]

You're Welcome :beerchug:

Take Care