Form controls vs. ActiveX controls

[SleestakKing]

Hi all,

I have a question. I'm trying to put some Option Buttons on my sheets and am having some problems I'm hoping you can help with.

Basically, I want to put a series of Option Buttons in several consecutive columns, that when a user chooses between Y or N buttons, macro code is called to handle different events for a column. And I'd like to use one set of code such that when a user clicks the button, the macro code will be able to figure out what cell the button resides in and act on the column that the button resides in.

My problem is that using the Option Buttons as a form control vs. an ActiveX control presents different problems.

If the Option Buttons are form control Option Buttons, I can't figure out a way to have the switching options be only effective for a particular group. (As an example, in Cell D2 are Buttons Y and N. In Cell E2 are other Buttons Y and N. Yet, Excel will only allow one of those four buttons to be chosen. What I want is for the buttons in D2 to operate independently from E2.) Is there a way to group these buttons when they are form controls?

If the Option Buttons are ActiveX controls, I can't figure out how to determine in which cell the ActiveX control resides. Is there a function call that will tell me what cell the Option Button is in?

Any help would be greatly appreciated. Thanks.

Robert

 

[Tom Urtis]

You explained your question very well. The only thing I wonder is, why bother with Forms option buttons if you have access to the more versatile ActiveX controls.

Regarding the Forms option button questions, you can use a group box from that same Forms toolbar and group each set of 2 yes & no option buttons, and edit the text of the group box to identify whatever significance each set has. Keep in mind what I said above, regarding ActiveX versatility, which is absent in Forms controls. I hate group boxes but if you have your heart set on Forms controls then you are limiting your options (no pun intended).

As to identifying which button was clicked and which column the button was on, assign each option button to this one macro, then click a button to see 2 message boxes pop up with the info you were asking about:


Sub Test1()
Dim myRow&, myCol%
With ActiveSheet.OptionButtons(Application.Caller)
myRow = .TopLeftCell.row: myCol = .TopLeftCell.Column

MsgBox _
"The row is " & myRow & vbCrLf & _
"The column is " & myCol & vbCrLf & _
"The cell address is " & Cells(myRow, myCol).Address(0, 0), _
64, "Location info for " & .Name

End With

Select Case myCol
Case 4: MsgBox "Do something for option button in columm D."
Case 5: MsgBox "Do something for option button in columm E."
Case 6: MsgBox "Do something for option button in columm F."
End Select

End Sub



Now all that being said, you can make your life easier with a class module for all ActiveX option buttons, so you don't need to assign each bloody one to the same macro. Also, the GroupName property can bypass the Group Box (called a Frame control in the ActiveX orbit), and the row and / or column would be identified by the object's TopLeftCellRow and TopLeftCellColumn properties. The Class module would ID the object name and column and hence carry out your dependent code. There are plenty of examples on how to do this if you search for keywords class and module.

 

[SleestakKing]

Hi and thanks for the response.

To be honest, I was bothering with form controls vs. ActiveX controls simply because I could get some things to work under once control, but not the other. And vice versa. Hence my question.

As you say, I'd rather use ActiveX, so let's table the Form control issue for now.

I tried your code and received an error-- the error I kept getting when I was trying something similar. The problem is with the line:

With ActiveSheet.OptionButtons(Application.Caller)

Upon execution, I get the error: "Unable to get the OptionsButton property of the Worksheet class". Any clues?

Robert

 

[Tom Urtis]

The code I posted was addressing the question you asked about how to deal with aspects of option buttons from the Forms toolbar. I tested the code before posting it and am sure it works.

If you are receiving errors, it is maybe because you are attempting to run my code while associating it with option buttons from the ActiveX control toolbar, which do not respond to the Application.Caller property. That was why I suggested that the code I posted could be alternatively represented as a class module to identify the object name and its TopLeftColumn property and hence its location.

Just reading into your second post here, I think you are trying to fit Forms-associated code with ActiveX-associated objects. This board and Google are excellent resources for class module code examples if that is what you want to do, that is, to work with ActiveX controls instead of the Forms controls you asked about and received my code for.

 

[SleestakKing]

Thank you, Tom.

Your response actually answers my problem and I've been able to figure it out. I really appreciate you taking the time to respond.

Robert