Add Optionbutton with VBA question

[deadfan]

Hello everyone,

I am new to the Mr. Excel message boards. I found them after watching his Livelessons video training series. I've searched the boards, msdn, excel help, books, pdf's, etc and now I turn to the knowledge here on these boards for some help & guidance with the following situaiton.

I've created a macro that should add 3 optionbuttons to an excel spreadsheet. The second part of the macro then modifies the properties of the three option buttons (Caption, LinkedCell, & Width). However, it seems that since the buttons don't exist prior to the code executing them, when the object variable is delcared that should hold the information about the current worksheet, the second half of the macro generates an error condition: Method or data member not found.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/6/2009
'
'
Dim wbo As Workbook
Dim wbs As Worksheet
Set wbo = ActiveWorkbook
Set wbs = wbo.ActiveSheet
 
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.OptionButton.1", Link:=False, _
        DisplayAsIcon:=False, Left:=18.75, Top:=6.75, Width:=108, Height:= _
        19.5).Select
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.OptionButton.1", Link:=False, _
        DisplayAsIcon:=False, Left:=66.75, Top:=6.75, Width:=108, Height:= _
        19.5).Select
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.OptionButton.1", Link:=False, _
        DisplayAsIcon:=False, Left:=113.25, Top:=7.5, Width:=108, Height:= _
        19.5).Select
 
    wbs.OptionButton1.Caption = " "
    wbs.OptionButton1.LinkedCell = "Sheet2!A1"
    wbs.OptionButton1.Width = "13.5"
 
    wbs.OptionButton2.Caption = " "
    wbs.OptionButton2.LinkedCell "Sheet2!B1"
    wbs.OptionButton2.Width = "13.5"
 
    wbs.OptionButton3.Caption = " "
    wbs.OptionButton3.LinkedCell "Sheet2!C1"
    wbs.OptionButton3.Width = "13.5"
 
End Sub

Is there another way to add the optionbuttons to a spreadsheet?

Is there a way to update the object variable during code execution so it is refreshed to know that the three new buttons have been added?

Is there another way to solve this issue?

Thank you in advance!

 

[xld]

This works for me

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/6/2009
'
'
Dim wbo As Workbook
Dim wbs As Worksheet
Dim btn As Object

    Set wbo = ActiveWorkbook
    Set wbs = wbo.ActiveSheet
 
    With ActiveSheet
    
        Set btn = .OLEObjects.Add(ClassType:="Forms.OptionButton.1", _
                                  Link:=False, _
                                  DisplayAsIcon:=False, _
                                  Left:=18.75, _
                                  Top:=6.75, _
                                  Width:=108, _
                                  Height:=19.5)
        btn.Object.Caption = ""
        btn.LinkedCell = "Sheet2!A1"
        btn.Width = "13.5"
    
        Set btn = .OLEObjects.Add(ClassType:="Forms.OptionButton.1", _
                                  Link:=False, _
                                  DisplayAsIcon:=False, _
                                  Left:=66.75, _
                                  Top:=6.75, _
                                  Width:=108, _
                                  Height:=19.5)
        btn.Object.Caption = " "
        btn.LinkedCell = "Sheet2!B1"
        btn.Width = "13.5"
    
        Set btn = .OLEObjects.Add(ClassType:="Forms.OptionButton.1", _
                                  Link:=False, _
                                  DisplayAsIcon:=False, _
                                  Left:=113.75, _
                                  Top:=6.75, _
                                  Width:=108, _
                                  Height:=19.5)
        btn.Object.Caption = " "
        btn.LinkedCell = "Sheet2!C1"
        btn.Width = "13.5"
    End With
 
End Sub

 

[deadfan]

Thank you for the quick reply.

I think that I understand the difference between the way you implemented/used the OLEobject as opposed to the way to macro recorder inserted them in to the macro for me.

You established a variable: btn as Object, I had tried to create a variable as an optionbutton like this:

dim btn as optionbutton
 
set btn = ActiveSheet.OLEObjects.Add(ClassType:="Forms.OptionButton.1", Link:=False, _
        DisplayAsIcon:=False, Left:=18.75, Top:=6.75, Width:=108, Height:= _
        19.5)

Thank you again!

 

[xld]

That was part of it, to avoid the select, and get a button object variable. I also set the caption of the OLEObject Object, the thing (button) within the OLEObject container, this is to avoid the error.

 

[deadfan]

How would I then be able to group those three new buttons?

 

[xld]

Sub Macro1()
Dim wbo As Workbook
Dim wbs As Worksheet
Dim btn As Object

    Set wbo = ActiveWorkbook
    Set wbs = wbo.ActiveSheet
 
    With ActiveSheet
    
        Set btn = .OLEObjects.Add(ClassType:="Forms.OptionButton.1", _
                                  Link:=False, _
                                  DisplayAsIcon:=False, _
                                  Left:=18.75, _
                                  Top:=6.75, _
                                  Width:=108, _
                                  Height:=19.5)
        btn.Object.Caption = ""
        btn.Object.GroupName = "Group1"
        btn.LinkedCell = "Sheet2!A1"
        btn.Width = "13.5"
    
        Set btn = .OLEObjects.Add(ClassType:="Forms.OptionButton.1", _
                                  Link:=False, _
                                  DisplayAsIcon:=False, _
                                  Left:=66.75, _
                                  Top:=6.75, _
                                  Width:=108, _
                                  Height:=19.5)
        btn.Object.Caption = " "
        btn.Object.GroupName = "Group1"
        btn.LinkedCell = "Sheet2!B1"
        btn.Width = "13.5"
    
        Set btn = .OLEObjects.Add(ClassType:="Forms.OptionButton.1", _
                                  Link:=False, _
                                  DisplayAsIcon:=False, _
                                  Left:=113.75, _
                                  Top:=6.75, _
                                  Width:=108, _
                                  Height:=19.5)
        btn.Object.Caption = " "
        btn.Object.GroupName = "Group2"
        btn.LinkedCell = "Sheet2!C1"
        btn.Width = "13.5"
    End With
 
End Sub