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