Application.Caller syntax for worksheets?

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

Please Login or Register  to view this content.

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...

Hi Hi Ouka,

Try changing:

> If selOpt.Value = True Then


to

If selOpt.Value = xlOn Then

---
Regards,
Norman


"Ouka" <[email protected]> wrote in message
news:[email protected]...
>
> 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...
>
>
> --
> Ouka
> ------------------------------------------------------------------------
> Ouka's Profile:
> http://www.excelforum.com/member.php...o&userid=23988
> View this thread: http://www.excelforum.com/showthread...hreadid=502311
>

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 wrote:
>
> 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...
>
> --
> Ouka
> ------------------------------------------------------------------------
> Ouka's Profile: http://www.excelforum.com/member.php...o&userid=23988
> View this thread: http://www.excelforum.com/showthread...hreadid=502311

--

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 wrote:
>
> 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.
>
> --
> Ouka
> ------------------------------------------------------------------------
> Ouka's Profile: http://www.excelforum.com/member.php...o&userid=23988
> View this thread: http://www.excelforum.com/showthread...hreadid=502311

--

Dave Peterson

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.