How do I dynamically create a button on my Active Sheet?

Question

So I have created a main menu of sorts in my workbook where if the user clicks a button, a new sheet is created and opened for the user to see some data.

Now, I want to dynamically (through VBA code) create a button on the currently active sheet with data that gives the user to close the sheet (essentially deleting the ActiveSheet and returning to the Main Menu)

I tried looking at VBA help for info on the button control but all I can find is info on CommandButton (which I believe is only to be used in a UserForm).

Am I wrong in this belief and can CommandButton be used outside of Userforms as well?
Or is there a Button object that I am unable to find?

I searched on this topic and only found this post, but it doesn't work. I am using Excel 2010 if that helps. How to add a button programmatically in VBA next to some sheet cell data?

there are form controls and activex both of which can be added to a worksheet (and both have a command button). As mentioned above, simpler to use a Form control. You can explore what these look like via the developer tab. — QHarr, Dec 16 '17 at 17:28

Chrowno · Answer 1 · 2017-12-16T20:17:48.740

0

One way to solve this is using a template. Create a sheet with said button. In the sheet module you use this:

Private Sub CommandButton1_Click()
    Me.Delete
    ThisWorkbook.Worksheets("MainMenu").Activate
End Sub

When creating a sheet you would use this template like this:

Public Sub CopySheet()

    ThisWorkbook.Worksheets("Template").Copy After:=Sheets(Sheets.Count)

    With ActiveSheet
        .Name = "Your Name"
    End With

    'Fill with data etc....

End Sub

The copied template now has your delete button. There are other ways to go about it but this is probably the easiest.

P.s.: To add a button to a sheet "add" either a forms or active-X control to the sheet in the developers tab. Which one you use shouldn't matter with a template.

edited Dec 16 '17 at 20:17

answered Dec 16 '17 at 17:47

Chrowno

198
6

Does your command button code need a re-direct line to ensure user lands on Main Menu tab? – QHarr Dec 16 '17 at 17:53
@QHarr Good catch, I overread that part. Assuming his "main menu" is a sheet and not the only other sheet in that workbook you'd have to activate it, yes. – Chrowno Dec 16 '17 at 20:16

Kwakkers · Answer 2 · 2017-12-17T09:43:13.093

0

Here is another way...

Sub CreateSheetAddButton()

    ' Set the worksheet to a new sheet
    Dim ws As Worksheet
    With ThisWorkbook
        Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
    End With

    Dim btn As Button
    ' Add a button and assign an onaction macro
    With ws
        .Name = "YourName"
        Set btn = .Buttons.Add(71.25, 26.25, 107.25, 63.75)
    End With

    With btn
        .OnAction = "DeleteWS"
        .Caption = "Delete Current Sheet"
        .Name = "btnDel"
    End With

    ' Clean up
    Set ws = Nothing
    Set btn = Nothing

End Sub

Sub DeleteWS()

    Dim ws As Worksheet, wsMain As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    Set wsMain = ThisWorkbook.Worksheets("Main")

    With ws
        Application.DisplayAlerts = False
        ws.Delete
        wsMain.Activate
        Application.DisplayAlerts = True
    End With

    ' Clean up
    Set ws = Nothing
    Set wsMain = Nothing

End Sub

edited Dec 17 '17 at 09:43

answered Dec 16 '17 at 18:07

Kwakkers

71
1
5

Good, straightforward example. One suggestion to get it a tad more OOP(if I may): set the created button as a Button-Object instead of "select". – Chrowno Dec 16 '17 at 20:24
@Chrowno - thanks good point. Edited answer using the an object – Kwakkers Dec 17 '17 at 09:44
It worked! However, I am not able to understand why there is absolutely no documentation on the "Button" object in VBA or even online. Even VBA autocomplete did not give me any suggestions on "Button" when I typed the code manually. It seems that "Buttons" should be a member of "Worksheet" but checking documentation on "Worksheet" too gives no indication of the existence of "Buttons". Any reason for this? Also, is there ANY documentation on the "Button" object for reference? – Aashay Sukhthankar Dec 18 '17 at 14:54
It's part of the [OleoObjects-Collection](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/oleobject-object-excel) – Chrowno Dec 19 '17 at 17:13

How do I dynamically create a button on my Active Sheet?

2 Answers2