Multiple Apps

Show Userform by its name

Ease of Use

Intermediate

Version tested with

Excel97, Word97, 2003 

Submitted by:

Steiner

Description:

Shows the userform that has a given name. 

Discussion:

Sometime you have just the name of an userform as string during runtime (so you don't have the object where you just could say Userform.Show). This method provides a quite easy way to show the userform with only a given name, which might be just what you need for dynamic navigation based on a user selection. 

Code:

instructions for use 

			
Option Explicit 
Option Compare Text 
Public Sub ShowUserFormByName(FormName As String) 
    On Error GoTo err 
    Dim i% 
     ' Load the Userform
    UserForms.Add FormName 
     'Go through all loaded Userforms
    For i = 0 To UserForms.Count - 1 
         'Find the one with the matching name
        If UserForms(i).Name = FormName Then 
             'Show that
            UserForms(i).Show 
            Exit For 
        End If 
    Next i 
    Exit Sub 
err: 
    Select Case err.Number 
Case 424: 
        MsgBox "The Userform with the name " & FormName & " was not found.", vbExclamation, "Unknown form" 
Case Else: 
        MsgBox err.Description, vbCritical, "A strange error: " & err.Number 
    End Select 
End Sub

How to use:

  1. Open the VBA-IDE by pressing Alt+F11
  2. Copy & Paste the above code into a module
  3. You must call that one from code, as it expects a parameter!
  4. Run it giving it the name of the userform to show: ShowUserFormByName "UserForm2"
 

Test the code:

  1. See the example ;-)
  2. just open the example doc and click on the button in the document
  3. select a formname from the listbox
  4. press the Show-Button
 

Sample File:

userforms.zip 13.39KB 

Approved by mdmackillop


This entry has been viewed 144 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express