VBA - Accessing Worksheets via Dynamically Created Buttons

Question

I am attempting to create a simple programme to consolidate information on Excel via VBA into a user-friendly programme. I am a beginner in VBA, hence do require your kind help in this matter.

First of all, a workbook is comprised of many different worksheets, each one containing different categories of information. The user will click on a button to select which worksheet title is most suitable for their circumstance, before the programme retrieves information within that worksheet. I want to make the programme such that the manager in future can continue to customise the workbook in future by altering the number of worksheets and the programme would respond accordingly.

I have thus decided that I need dynamically created buttons on a userform. My current code in a module goes:

Sub Layer1()

Dim ws As Worksheet
Dim maincats() As Variant
Dim i As Long

ReDim maincats(ThisWorkbook.Sheets.Count)

For Each ws In ThisWorkbook.Sheets
    maincats(i) = ws.Range("A1").Value
    i = i + 1
Next ws


Dim ub, As Integer
Dim text As String
ub = UBound(maincats) - 1   


Dim x As Integer
Dim btn As CommandButton
Dim topPos As Integer


topPos = 100

For x = 0 To ub
    Set btn = UserForm1.Controls.Add("Forms.CommandButton.1")
    With btn
        .Caption = maincats(x)
        .Left = 50
        .Top = topPos
        .Width = 100
        .Height = 30
    End With
    topPos = topPos + 40

Next x

UserForm1.Show

End Sub

The above code appears to suit my elementary needs decently, having creating a userform with the correct number of buttons that correspond to the correct number of worksheets. Now I need to actually link the buttons to the corresponding worksheets, so what can I do in this case? I have attempted to comb through the web for information, but most guides appear to clarify on doing so with buttons already created in the UserForm, which I believe in my case does not apply.

May I know if anyone has any help to offer in this case? Feel free to let me know if you need more clarifications from me! Any help will be greatly appreciated.

Thank you in advance!

Answer 1

Since you're going through the sheets, you can use ws.Name instead of ws.Range("A1").Value. That way, even if a user changes the sheet name, your dynamically created button will refer to the correct sheet. Been trying to make this work for a bit now with Brett's answer but it was only with Darren's answer that I got your code to work.

Just like in his answer, you'll have to add a class module with in it:

Public WithEvents MyButton As MSForms.CommandButton

Private Sub MyButton_Click()
    ThisWorkbook.Worksheets(MyButton.Caption).Activate
    UserForm1.Hide
End Sub

and in your userform's code:

Public MyEvents As New Collection

Private Sub UserForm_Initialize()

    Dim tmpCtrl As Control
    Dim CmbEvent As clsMyEvents
    Dim ws As Worksheet
    Dim maincats() As Variant
    Dim i As Long
    ReDim maincats(ActiveWorkbook.Sheets.Count)
    
    For Each ws In ActiveWorkbook.Sheets
        maincats(i) = ws.Name
        i = i + 1
    Next ws
    
    
    Dim ub As Integer
    Dim text As String
    ub = UBound(maincats) - 1
    
    
    Dim x As Integer
    Dim btn As Object
    Dim topPos As Integer
    
    topPos = 100
    
    For x = 0 To ub
        Set btn = UserForm1.Controls.Add("Forms.CommandButton.1")
        With btn
            .Caption = maincats(x)
            .Left = 50
            .Top = topPos
            .Width = 100
            .Height = 30
        End With
        topPos = topPos + 40
        
        Set CmbEvent = New clsMyEvents
        Set CmbEvent.MyButton = btn
        MyEvents.Add CmbEvent
    Next x
    
End Sub

And then you can use UserForm1.Show at Workbook_Open in ThisWorkbook:

Private Sub Workbook_Open()
    UserForm1.Show
End Sub

or just add it to a button or however you want the users to get the opportunity to see the userform again.

Would've loved being able to use Brett's answer as it seems easier to use (imho) but not going to keep staring blind at it ^^

Answer 2

This should get you started:

Create a class module myButton. Add this code to the module:

Public WithEvents mBtn As MSForms.CommandButton

Public Sub init(ByVal caption As String, top As Integer)
    mBtn.caption = caption
    mBtn.top = top
End Sub

Private Sub mBtn_Click()
    Sheets(mBtn.caption).Select
End Sub

Private Sub Class_Initialize()
    Set mBtn = UserForm1.Controls.Add("Forms.CommandButton.1")
    With mBtn
        .Left = 50
        .Width = 100
        .Height = 30
    End With
End Sub

And edit your code like this:

Sub Layer1()
    Dim ws As Worksheet
    Dim i As Long
    
    Dim btn As myButton
    Dim btns() As myButton
    
    Dim topPos As Integer
    
    ReDim btns(ThisWorkbook.Sheets.Count)
    
    topPos = 100
    For Each ws In ThisWorkbook.Sheets
        Set btn = New myButton
        Set btns(i) = btn
        With btns(i)
            .init ws.Name, topPos
        End With
        topPos = topPos + 40
        i = i + 1
    Next ws
    
    UserForm1.Show
End Sub

I adjusted your code slightly. I grab the Worksheet names dynamically with ws.Name instead of reading cell A1. This also eliminates the need for an array that hold the worksheet names.