How do I open a UserForm with the same name as a button? VBA language

0

My program consists of 20 buttons and 20 UserForms. I wanted to simplify the program and make a unique Sub Routine.

Each time I press a button, the CheckBoxPreview routine analyzes the button name and opens the UserForm with the same button name.

Buttons: Photo1, Photo2, ..., Photo20

UserForms: Photo1, Photo2, ... Photo20

Could anyone help me?

    
asked by anonymous 29.08.2018 / 14:06

1 answer

0

Using the ShowAnyForm function of Cpearson you can do this with a class module.

An example with the following VBA project tree:

Form

AnexamplewiththefollowingForm:

Aformwithtwobuttonswiththenameoftheformthatshouldbeopened.

Andwiththefollowingcode:

PrivatecollBtnsAsCollectionPrivateSubUserForm_Initialize()'Créditos:www.andypope.info'www.cpearson.com/excel/Events.aspxDimcls_btnAsClasse1SetcollBtns=NewCollectionForEachctrlInMe.ControlsIfTypeName(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 into 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 click event is declared in btn_Click() .

With the click of the button, it will open the form with the same name as the Button with the function ShowAnyForm .

The options that 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

Result

    
29.08.2018 / 15:40