How can I create a new sheet with multiple command buttons(OLEObject)?

Question

I am working on a code that has two command buttons:

1) USER INPUT

2) Execute

When clicked, the USER INPUT button makes a UserForm appear. Based on the UserForm input, the worksheet format adjusts and the user inputs data to the worksheet where prompted. The Execute button performs calculations and fills out the rest of the sheet and then graphs the results OR opens a new sheet that then contains the same two buttons.

I am able to create the new sheet but the sheet only contains one command button. My code is below:

Dim obj As Object
Dim Code As String
Dim obj2 As Object
Dim code2 As String

    With Sec_Delay

        Set obj = .OLEObjects.Add(classType:="Forms.CommandButton.1", _
                                Link:=False, DisplayAsIcon:=False, Left:=279, _
                                Top:=210.75, Width:=109.5, Height:=24)
        obj.Name = "ButtonTest"
        obj.Object.Caption = "USER INPUT"

        Code = "Sub ButtonTest_Click()" & vbCrLf & _
            "UF_input.Show" & vbCrLf & _
            "End Sub"

        Set obj2 = .OLEObjects.Add(classType:="Forms.CommandButton.2", _
                                Link:=False, DisplayAsIcon:=False, Left:=277.5, _
                                Top:=236.25, Width:=111, Height:=24)
        obj2.Name = "Execute2Test"
        obj2.Object.Caption = "Execute"

        code2 = "Sub Execute2Test_Click()" & vbCrLf & _
                "Cells(8,1) = 1" & vbCrLf & _
                "End Sub"

        With .Parent.VBProject.VBComponents(.CodeName).CodeModule
            .insertlines .CountOfLines + 1, Code
        End With
    End With

This code is within my sub that creates the new sheet. The new sheet is called "Sec_Delay" and it only has one command button. I lifted the code for the first command button from somewhere else on stackoverflow so I am not familiar with whatever the last part does:

    With .Parent.VBProject.VBComponents(.CodeName).CodeModule
        .insertlines .CountOfLines + 1, Code
    End With

But I understand mostly how the OLEObject works. I just don't get how to make a second command button for the new sheet.

How can I create a second command button? Why does "Forms.CommandButton.2" not do anything? What does the ".1" mean anyways? Is it possible to have two OLEObjects in the same sub?

Tim Williams · Accepted Answer · 2018-04-27T17:27:48.330

1

You're almost there...

"Forms.CommandButton.1" is the classname for the control: it determines what type of control gets created, and you shouldn't alter the value or excel won't recognize it.

The last part of the code adds event handlers for the buttons to the sheet's code module: after you create the sheet you can view the code in the VB editor.

Dim obj As Object
Dim Code As String
Dim obj2 As Object
Dim code2 As String

With Sec_Delay

    Set obj = .OLEObjects.Add(classType:="Forms.CommandButton.1", _
                            Link:=False, DisplayAsIcon:=False, Left:=279, _
                            Top:=210.75, Width:=109.5, Height:=24)
    obj.Name = "ButtonTest"
    obj.Object.Caption = "USER INPUT"

    Code = "Sub ButtonTest_Click()" & vbCrLf & _
        "UF_input.Show" & vbCrLf & _
        "End Sub"

    'edit: use "Forms.CommandButton.1" again
    Set obj2 = .OLEObjects.Add(classType:="Forms.CommandButton.1", _
                            Link:=False, DisplayAsIcon:=False, Left:=277.5, _
                            Top:=236.25, Width:=111, Height:=24)
    obj2.Name = "Execute2Test"
    obj2.Object.Caption = "Execute"

    code2 = "Sub Execute2Test_Click()" & vbCrLf & _
            "Cells(8,1) = 1" & vbCrLf & _
            "End Sub"

    With .Parent.VBProject.VBComponents(.CodeName).CodeModule
        .insertlines .CountOfLines + 1, Code
        .insertlines .CountOfLines + 1, code2 '<< added
    End With
End With

EDIT: if you want to call code which is located in a sheet module from a regular module, you need to include the module name in the call.

Sub TestCall()
    Sheet1.Tester
End Sub

...and make sure you're using the sheet's codename, not the tab name:

edited Apr 27 '18 at 17:27

answered Apr 26 '18 at 20:38

Tim Williams

122,926
8
79
101

Thanks @Tim that worked! I am trying to set the Execute2Test button to run the same code from the first button which is called ExecuteButton_Click(). However, when I type `"Call ExecuteButton_Click"` I get an error that says: Sub or Function not defined. But it's defined as `Public Sub ExecuteButton_Click()` on sheet 1. How do I make it run that code that is on sheet 1? I thought that calling it a Public Sub would make it be able to be referenced anywhere. – Dives Apr 27 '18 at 15:33
Try `Sheet1.ExecuteButton_Click` (or whatever is the codename for the Sheet1 module). The `Sheet1` code module represent a Worksheet object, so you need to scope your calls accordingly. – Tim Williams Apr 27 '18 at 16:09
When I try that, it still says "Object Required." The next thing I tried was I made all of the math code into it's own sub, so that `ExecuteButton_Click` only has one line of code `Call MathCode`. Then I tried calling `Sheet1.MathCode`, `MathCode`, and `Sheet1.ExecuteButton_Click` in the `OLEObject` and none of it worked. They all return the same error. – Dives Apr 27 '18 at 17:10
If you get "object required" then maybe you're not using the correct sheet module name: make sure it matches what's displayed in the tree in the VB editor – Tim Williams Apr 27 '18 at 17:21
Someone told me that you cannot reference code from a different worksheet module. Is that true? He recommended that I put all my code in a regular module and then call the subs in the worksheet module. That threw up different errors that I could not figure out. I don't understand why I can't just call my Sub anywhere since it's Public. – Dives Apr 27 '18 at 20:12
There's no problem referencing code from a worksheet module (in the same workbook) as long as you prefix it with the sheet module code name (as in my example above). Making a Sub Public just means it's visible: it doesn't let you ignore the scoping requirements. In my example `Tester` is a method of `Sheet1`, so you have to call it like `Sheet1.Tester` – Tim Williams Apr 27 '18 at 20:20

How can I create a new sheet with multiple command buttons(OLEObject)?

1 Answers1