Passing a UserForm name to a Sub

  • I think I am near the end of my project, but I have a few final tweaks before I call it done. In this case, I have a sub with the following arguments passed to it

    • a ListObject (specifically the name of a table on...)
    • a Worksheet (where the table resides)
    • a String (which is what I am hunting for on a separate and constant worksheet)
    • a UserForm name (that gets called for the user to enter some related information about the String that will be entered into the ListObject on the Worksheet)


    This task is done for several different Strings (call these s1, s2, s3, ...) and each of these Strings corresponds to a different ListObject (= Table) in different Worksheets and they all use different UserForms. All the "guts" of the tasks behind the scenes is exactly the same and the userforms are the ones that actually do different tasks.


    This is why I wanted to create a MasterSub where all I have to input these 4 qualities and it would run beautifully... if... I could figure out how to pass the name of the userform!!!


    I am thinking of something like...


    I would then call it with


    Code
    Call MasterNamer("DIV", Worksheets("Divisions_Master"), Worksheets(Divisions_Master).ListObjects("Divisions_Master_Table"), DivisionNameForm)


    Unfortunately, the last piece is escaping me. How do I pass a userform name through a sub? OR Should I pass DivisionNameForm as a string and then somehow turn it into the front end name within the sub (i.e., pass DivisionNameForm to the sub as a string, the sub calls it fUserForm [as a string], and some magic happens so I can use the following?


    Code
    fUserForm.ShortNameLabel.Caption = sShortName
    fUserForm.NewRowNumLabel.Caption = iCurLocTbl
    fUserForm.Show


    Thank you so much ahead of time!

  • Re: Passing a UserForm name to a Sub


    Try using this syntax instead:


    Code
    Sub LaunchMe(frmName As String)
        VBA.UserForms.Add(frmName).Show
    End Sub



    Code
    Sub TestCallForm()
        LaunchMe ("UserForm2")
    End Sub

    Matt Mickle
    Using Excel 2010,2013 & 2016

  • Re: Passing a UserForm name to a Sub


    I gave it a whirl and, while it did bring up the form, it did not change the captions via the following code


    Code
    VBA.UserForms.Add(fUserForm).ShortNameLabel.Caption = sShortName


    Do you know of any way to change the captions using a variable form name?

  • Re: Passing a UserForm name to a Sub


    Actually, I decided to modify the code to have a Select Case group. It does the job and it still compacts several subs into one. I am happy. Thank you mrmmickle1.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!