Passing a UserForm to a Sub as a Variable

[C_Rieker]

Hello my favourite experts.

I am attempting to run a sub-routine every time a UserForm is initialised. The sub is used to format and position the UserForm elements based on values within a spreadsheet table. The code I use runs exceptionally well if I run it within the UserForm itself. However, my problem is that the code is quite repetitive, and I would rather not have to copy it into every UserForm coding (especially if I have to make a minor change to the code to all the UserForms.

To avoid the repetitive copy-paste, I have put the code into a Sub-Routine, and I pass the UserForm to the Sub as a variable. Only it stops and tells me Runtime Error: 13 - Type-Mismatch.

Any help would be greatly appreciated. Please see below my coding:

My UserForm Code:

Private Sub UserForm_Initialize()

    InitiateUserForm (UF_NewProduct)

End Sub

My Sub-Routine:

Sub InitiateUserForm(UFName As UserForm)

    Dim Table As Object
    Dim ItemName As String
    Dim cntrl As Control
    Dim UFString As String
    
    UFString = Right(UFName.Name, Len(UFName.Name) - 3)
    
    Set Table = Sheets("UF LAYOUT").ListObjects("UFLayout_" & UFString)
    
    With UFName
        On Error Resume Next
            ItemName = .Name
            .Caption = WorksheetFunction.XLookup(ItemName, Table.ListColumns("Name").DataBodyRange, Table.ListColumns("Caption").DataBodyRange).Value
            .Top = WorksheetFunction.XLookup(ItemName, Table.ListColumns("Name").DataBodyRange, Table.ListColumns("Top").DataBodyRange).Value
            .Left = WorksheetFunction.XLookup(ItemName, Table.ListColumns("Name").DataBodyRange, Table.ListColumns("Left").DataBodyRange).Value
            .Height = WorksheetFunction.XLookup(ItemName, Table.ListColumns("Name").DataBodyRange, Table.ListColumns("Height").DataBodyRange).Value
            .Width = WorksheetFunction.XLookup(ItemName, Table.ListColumns("Name").DataBodyRange, Table.ListColumns("Width").DataBodyRange).Value
        On Error GoTo 0
    End With
    
    For Each cntrl In UFName.Controls
        On Error Resume Next
            ItemName = cntrl.Name
            cntrl.Caption = WorksheetFunction.XLookup(ItemName, Table.ListColumns("Name").DataBodyRange, Table.ListColumns("Caption").DataBodyRange).Value
            cntrl.Top = WorksheetFunction.XLookup(ItemName, Table.ListColumns("Name").DataBodyRange, Table.ListColumns("Top").DataBodyRange).Value
            cntrl.Left = WorksheetFunction.XLookup(ItemName, Table.ListColumns("Name").DataBodyRange, Table.ListColumns("Left").DataBodyRange).Value
            cntrl.Height = WorksheetFunction.XLookup(ItemName, Table.ListColumns("Name").DataBodyRange, Table.ListColumns("Height").DataBodyRange).Value
            cntrl.Width = WorksheetFunction.XLookup(ItemName, Table.ListColumns("Name").DataBodyRange, Table.ListColumns("Width").DataBodyRange).Value
            cntrl.Visible = WorksheetFunction.XLookup(ItemName, Table.ListColumns("Name").DataBodyRange, Table.ListColumns("Visible").DataBodyRange).Value
        On Error GoTo 0
    Next cntrl

End Sub

The Error:

 

[Norie]

Pass it as an Object.

Sub InitiateUserForm(UFName As Object)

 

[C_Rieker]

Pass it as an Object.

Sub InitiateUserForm(UFName As Object)

Thank-you for your prompt reply. However, passing it as an object causes Error 438 (Object doesn't support this property or method) and it won't tell me what method/property that caused the error. I can assume it is when I called UFName.Name? or maybe UFName.Controls?

I think I have been looking at this too long. I am just about ready to knock my head against a wall and call it quits haha.

 

[Norie]

When you get the error no line is highlighted?

 

[C_Rieker]

When you get the error no line is highlighted?

Unfortunately not. This is all I get. Pressing OK ends the Routine.

 

[Norie]

Try putting a breakpoint on the first line of InitiateUserForm and then step through the code.

 

[C_Rieker]

Try putting a breakpoint on the first line of InitiateUserForm and then step through the code.

It would appear that if I do anything with the UFName object/userform being passed (eg. UFName.Name or UFName.Width) it throws an Error 438.

I appreciate the effort put into this issue of mine. I think, for the time being, I will have to resort to the mundane copy/paste into all the userforms.

 

[dmt32]

It would appear that if I do anything with the UFName object/userform being passed (eg. UFName.Name or UFName.Width) it throws an Error 438.

Hi,
try removing the brackets around your form name & see if that solves the issue

Private Sub UserForm_Initialize()

    InitiateUserForm (UF_NewProduct)

End Sub

also for info, rather than hardcode the forms name you can use the Me keyword

Private Sub UserForm_Initialize()

    InitiateUserForm Me

End Sub

Dave

 

[C_Rieker]

Hi,
try removing the brackets around your form name & see if that solves the issue

Private Sub UserForm_Initialize()

    InitiateUserForm (UF_NewProduct)

End Sub

also for info, rather than hardcode the forms name you can use the Me keyword

Private Sub UserForm_Initialize()

    InitiateUserForm Me

End Sub

Dave

Dave I could kiss you!!! That fixed my problem. Bless you.

Just one question...Why did the brackets make a difference?

 

[Norie]

Putting brackets round objects causes them to be 'evaluated'.

In this case, i.e. a userform, it appears the userform is evaluated to it's Controls collection.