How to open a Userform with the same name as a button? VBA Language

Question

My program consists of 20 buttons and 20 Userforms. I wanted to simplify the program and make a single Subroutine.

Each time I press a button, the Checkbutton Checkbutton analyzes the name of the button and opens the Userform with the same name as the button.

Buttons: Foto1, Foto2, ..., Foto20

Userforms: Foto1, Foto2,... Foto20

Could someone help me?

Answer 1

Using the function ShowAnyForm of Cpearson this can be done with a class module.

An example with the following VBA project tree:

Form

An example with the following Form:

A form with two buttons with the name of the form that should be opened.

And with the following code:

Private collBtns As Collection
Private Sub UserForm_Initialize()
    'Créditos: www.andypope.info
    '          www.cpearson.com/excel/Events.aspx
    Dim cls_btn As Classe1
    Set collBtns = New Collection
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CommandButton" Then
            Set cls_btn = New Classe1
            Set cls_btn.btn = ctrl
            collBtns.Add cls_btn, CStr(collBtns.Count + 1)
        End If
    Next ctrl
End Sub

Class module

This code is inserted in the class module Classe1

    Public WithEvents btn As MSForms.CommandButton

Private Sub btn_Click()
    ShowAnyForm (btn.Name)
End Sub

Private Sub ShowAnyForm(FormName As String, Optional Modal As FormShowConstants = vbModal)
    'http://www.cpearson.com/Excel/showanyform.htm
    Dim Obj As Object
    For Each Obj In VBA.UserForms
        If StrComp(Obj.Name, FormName, vbTextCompare) = 0 Then
            Obj.Show Modal
            Exit Sub
        End If
    Next Obj
    With VBA.UserForms
        On Error Resume Next
        Err.Clear
        Set Obj = .Add(FormName)
        If Err.Number <> 0 Then
            MsgBox "Err: " & CStr(Err.Number) & "   " & Err.Description
            Exit Sub
        End If
        Obj.Show Modal
    End With
End Sub

In this, the button clicks event is declared in btn_Click().

That with the click of the button, will open the form of the same name of the Button with the function ShowAnyForm.

The options you can use to display the form are vbModal and vbModeless

Example:

Private Sub btn_Click()
    ShowAnyForm btn.Name, vbModal
End Sub
Private Sub btn_Click()
    ShowAnyForm btn.Name, vbModeless
End Sub

Upshot