Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller syntax for worksheets?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller syntax for worksheets?
Hi Hi Ouka,
Try changing: If selOpt.Value = True Then to If selOpt.Value = xlOn Then --- Regards, Norman "Ouka" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller syntax for worksheets?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller syntax for worksheets?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller syntax for worksheets?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller syntax for worksheets?
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. -- Ouka ------------------------------------------------------------------------ Ouka's Profile: http://www.excelforum.com/member.php...o&userid=23988 View this thread: http://www.excelforum.com/showthread...hreadid=502311 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application.Caller | Excel Discussion (Misc queries) | |||
Row = Application.Caller.Row | Excel Worksheet Functions | |||
Application.Caller | Excel Programming | |||
application.caller | Excel Programming | |||
Application.caller | Excel Programming |