How to assign a UserForm to a Class (Types)?

user_7994509 0
Viewed 177

I'm trying to create a code that will be executed against all userforms in my workbook. So I wanted to create a Class. However, I'm stuck at this point.

The class name is clsCmdUsrFormsE

Variable declaration within the class:

Public WithEvents EndUserforms As UserForm

Objects assignation code:

Dim UserFormsAll() As New clsCmdUsrFormsE

Sub Cmd_UsrF(Optional var1 As Boolean = False)
    Dim iCmdUsrFrm As Integer, UsrForm As Object
    iCmdUsrFrm = 0
    For Each UsrForm In WBK0001.VBProject.VBComponents
        If UsrForm.Type = 3 Then
            iCmdUsrFrm = iCmdUsrFrm + 1
            ReDim Preserve UserFormsAll(1 To iCmdUsrFrm)
            Set UserFormsAll(iCmdUsrFrm).EndUserforms = UsrForm
        End If
    Next UsrForm
End Sub

& thew intent is to call Cmd_UsrF using a simple call

Sub Test()
    Call Cmd_UsrF
End Sub

The problem I'm having is that it seems the Userform as Object is not compatible with the Class. I've tried as well with

Public WithEvents EndUserforms As msforms.UserForm

But all I get is

Run-time error '13': Type mismatch

Any ideas?

vba excel
1 Answers

You are tying to store VBComponent in MsForm and hence a type mismatch

enter image description here

Is this what you are trying?

Dim UserFormsAll() As New clsCmdUsrFormsE

Sub Cmd_UsrF(Optional var1 As Boolean = False)
    Dim iCmdUsrFrm As Integer, UsrForm As Object
    Dim frmname As String
    
    iCmdUsrFrm = 0
    
    For Each UsrForm In ThisWorkbook.VBProject.VBComponents
        If UsrForm.Type = 3 Then
            iCmdUsrFrm = iCmdUsrFrm + 1
            ReDim Preserve UserFormsAll(1 To iCmdUsrFrm)

            '~~> Get the Name of the form
            frmname = UsrForm.Name
            
            '~~> Load the form
            Set UserFormsAll(iCmdUsrFrm).EndUserforms = UserForms.Add(frmname)
            
        End If
    Next UsrForm
End Sub

In Class Module

Public WithEvents EndUserforms As MSForms.UserForm

Note:

You can also bypass the variable frmname and directly use

Set UserFormsAll(iCmdUsrFrm).EndUserforms = UserForms.Add(UsrForm.Name)
user_11405790
Related