Passing Userform as Argument of Sub

[animas]

In module I have:

Public Sub LoadUserForm(ByVal iform As UserForm)
    '//set form position
    With iform
        .Top = Application.Top + 170
        .Left = Application.Left + 30
    End With
    
    iform.Show vbModeless
End Sub

in worksheet module I have:
Private Sub Worksheet_Activate()
LoadUserForm (frmMenubar)
End Sub

Note that frmMenubar is the userform that i already created.

Error shows Type Mismatch. How to do this?

 

[Weaver]

try passing the form name as a string then use:

With forms(iform)

        .Top = Application.Top + 170
        .Left = Application.Left + 30
    End With

 

[RoryA]

You have two issues - use:

Public Sub LoadUserForm(ByVal iform As Object)

and call it without the parentheses:

LoadUserForm frmMenubar

 

[animas]

Thanks
I tried rorya's code. It's working.

One issue i can't resolve.

    With iform
        .Top = Application.Top + 170
        .Left = Application.Left + 30
    End With

Above code was working when i put it in UserForm_Activate event sub, inside userform module code.

But now it has no effect.

 

[RoryA]

You need to override the startupposition:

Public Sub LoadUserForm(ByVal iform As Object)
    '//set form position
    With iform
        .startupposition = 0
        .Top = Application.Top + 170
        .Left = Application.Left + 30
    End With
    
    iform.Show vbModeless
End Sub

 

[animas]

Thank you. Forms are positioned in right place now. I put LoadUserForm frmMenubar inside worksheet_activate event of mySHEET module. I used Unload frmMenubar inside worksheet_deactivate event. Problem is When I activated mySHEET and selected another workbook frmMenubar still remains loaded. I needed it to be shown on mySHEET only. Nowhere else.

Where should I change?

 

[Jon von der Heyden]

You could use the workbook_active and workbook_deactivate events too. On the workbook activate, check which sheet is the active one and then load your userform if the particular sheet is active.

 

[animas]

Users may copy my worksheet multiple times and add new sheets too. The above procedure will force me to hardcode the sheets name each time user adds sheet. I wanted to keep the code dynamic. Any workaround to this?

 

[RoryA]

Depends - how do you determine which sheets need the userform?

 

[snb_]

Not relevant any more