Application.Caller syntax for worksheets?

[Ouka]

It's got to be an obvious mistake, but I'm having trouble with the
following code:


Code:
--------------------
Private Sub cmdInventory_Click()

Dim selOpt As OptionButton
Dim studyName As String

Set selOpt = ActiveSheet.OptionButtons(Application.Caller)

If selOpt.Value = True Then
studyName = selOpt.Name
MsgBox studyName
End If

End Sub
--------------------


Execution from the worksheet yields a:

"Run-time error '1004':

Unable to get the OptionButtons property of the Worksheet class"

Execution from the VBA editor yields a:

"Run-time error '1004':
Application-defined or object-defined error"

What syntax have I goofed??

The examples I have use the above syntax, but I can't seem to get them
to go either...

 

[Norman Jones]

Hi Hi Ouka,

Try changing:

If selOpt.Value = True Then

to

If selOpt.Value = xlOn Then

 

[Dave Peterson]

From your variable names, it looks like cmdInventory is a commandbutton (from
the control toolbox???).

If that's the case, application.caller won't work.

if cmdInventory is a sub in a general module that's assigned to a bunch of
optionbuttons (from the Forms toolbar), then never mind.

 

[Ouka]

Norman - Changing "true" to "xlOn" did not work, getting the same error.


Dave - the option buttons and the command buttons are all on a
worksheet, not a user form.

The command button is built at design time, but the option buttons are
built by the user at run time, hence I cannot know what they are going
to be named ahead of time for any "if optionbutton1.value = true then"
code.

I need VBA to return to me the name of selected option button so I can
assign that name as a variable to use in a calling procedure in my
code.

 

[Dave Peterson]

You could have used optionbuttons from the Forms Toolbar or optionbuttons from
the Control toolbox toolbar on your worksheet.

It sounds like you used the optionbuttons from the Forms toolbar.

dim myOptBtn as optionbutton
for each myOptBtn in activesheet.optionbuttons
if myoptbtn.value = xlon then
msgbox myoptbtn.caption
exit for
end if
next myoptbtn

Just in case they were from the Control toolbox toolbar:

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.OptionButton Then
If OLEObj.Object.Value = True Then
MsgBox OLEObj.Object.Caption
Exit For
End If
End If
Next OLEObj

 

[Ouka]

Oooooh.

I didn't realize that they were different. Figured an option button
was an option button.

Thank you very much, lot of confusing frustrations suddenly make sense
now.