Forms

When asking the user to select a file, displaying a list of files in the current directory is user friendly. When the number of files is to large to display on the screen the forms technique should be used. An Excel form module can be used to display a large list of candidates to select from. The displayed portion of the list can be adjusted to view the entire list. This document creates Userform1 to select one file from the current directory. The results of this macro is to displayed in a message box the name of the selected file but with additional code the selected file could be opened, copied, or deleted.

The VBA macro, USER_FORM, will accomplish the following:

Their is a delete function for each application (Excel, PowerPoint, and Word) to make the USER_FORM routine re-runable.

The CREATE_FORM VBA macro will create a user form with 2 labels, 2 listboxs, 2 command buttons, and code to give the command buttons function. Each application requires its own Set TempForm statement to use 'ActiveDocument' for Word, 'ActiveWorkbook' for Excel, and 'ActivePresentation' for PowerPoint.

The SELECT_FILE VBA macro will add the name of the current directory to one listbox and every file name in the current directory to another listbox. After starting the USER_FORM macro Userform1 is shown to initiate a response to select a file from the current directory.

To use these macros requires making available the reference "Microsoft Visual Basic for Applications Extensibility 5.3" in Visual Basic Editor. When this reference in not available the Compile Error: User-defined type not defined will interrupt the macro.

To copy the file names in the current directory to an Excel workbook, a Word document or a PowerPoint presentation try the document, files in directory.

VBA Fifth Dimension Macro-Forms

Review the following code, copy it to Excel, Word or PowerPoint and execute it.

Sub USER_FORM()
' Delete UserForm1
    If InStr(Application.Name, "Excel") Then
       RC = DELETE_EXCEL_FORM
    End If
    If InStr(Application.Name, "Word") Then
       RC = DELETE_WORD_FORM
    End If
    If InStr(Application.Name, "PowerPoint") Then
       RC = DELETE_POWERPOINT_FORM
    End If
' Create UserForm1
    RC = CREATE_FORM(TEMPFORM)
    MsgBox TEMPFORM.Name
' Set UserForm1 with file information select file
    MsgBox SELECT_FILE()
End Sub
Function DELETE_EXCEL_FORM()
'   Delete Excel UserForm1
    MAC_BOOK = ActiveWorkbook.Name
    For Each VBComp In Workbooks(MAC_BOOK). _
    VBProject.VBComponents
'vbext_ct_MSForm = 3 using reference
        If VBComp.Type = 3 _
           And Left(VBComp.Name, 9) = "UserForm1" Then
           ActiveWorkbook.VBProject.VBComponents. _
           Remove VBComponent:=VBComp
        End If
    Next VBComp
End Function
Function DELETE_POWERPOINT_FORM()
'   Delete PowerPoing UserForm1
    MAC_BOOK = ActivePRESENTATION.Name
    For Each VBComp In Presentations(MAC_BOOK). _
        VBProject.VBComponents
'vbext_ct_MSForm = 3 using reference
        If VBComp.Type = 3 _
           And Left(VBComp.Name, 9) = "UserForm1" Then
           ActivePRESENTATION.VBProject.VBComponents. _
           Remove VBComponent:=VBComp
        End If
    Next VBComp
End Function
Function DELETE_WORD_FORM()
'   Delete Word UserForm1
    MAC_BOOK = ActiveDocument.Name
    For Each VBComp In Documents(MAC_BOOK). _
    VBProject.VBComponents
'vbext_ct_MSForm = 3 using reference
        If VBComp.Type = 3 _
        And Left(VBComp.Name, 9) = "UserForm1" Then
           ActiveWorkbook.VBProject.VBComponents. _
           Remove VBComponent:=VBComp
        End If
    Next VBComp
End Function
Function CREATE_FORM(TEMPFORM)
'   Create the UserForm1
    If InStr(Application.Name, "Excel") Then
       Set TEMPFORM = ActiveWorkbook.VBProject. _ 
                      VBComponents.Add(3)
    End If
    If InStr(Application.Name, "Word") Then
       Set TEMPFORM = ActiveDocument.VBProject. _ 
                      VBComponents.Add(3)
    End If
    If InStr(Application.Name, "PowerPoint") Then
       Set TEMPFORM = ActivePresentation.VBProject. _ 
                      VBComponents.Add(3)
    End If
    TEMPFORM.Properties("Caption") = "Select Directory"
    TEMPFORM.Properties("Width") = 516
    TEMPFORM.Properties("Height") = 450
    TEMPFORM.Properties("SpecialEffect") = 0
    TEMPFORM.Designer.Controls.Add ("forms.Label.1")
    Set Label = TEMPFORM.Designer.Controls.Add _ 
                ("forms.Label.1")
    With Label
        .Caption = "Current Directory"
        .Font.Size = 18
        .Height = 24
        .Left = 120
        .Top = 1
        .Width = 174
    End With
    Set ListBox = _
    TEMPFORM.Designer.Controls.Add("forms.ListBox.1")
    With ListBox
        .Height = 22.5
        .Left = 33
        .Name = "CURRENT_DIR"
        .Top = 50
        .Width = 416
    End With
    Set Label = _
    TEMPFORM.Designer.Controls.Add("forms.Label.1")
    With Label
        .Caption = "Select File"
        .Font.Size = 18
        .Height = 24
        .Left = 120
        .Top = 100
        .Width = 216
    End With
    Set ListBox = _
    TEMPFORM.Designer.Controls.Add("forms.ListBox.1")
    With ListBox
        .Height = 223.8
        .Left = 120
        .Name = "Variables"
        .Top = 132
        .Width = 138
    End With
    Set CommandButton = _
    TEMPFORM.Designer.Controls.Add("forms.CommandButton.1")
    With CommandButton
        .AutoSize = True
        .Caption = "GO"
        .Font.Size = 18
        .Height = 26
        .Left = 36
        .Name = "FORMGO"
        .Top = 156
        .Width = 66
    End With
    Set CommandButton = _
    TEMPFORM.Designer.Controls.Add("forms.CommandButton.1")
    With CommandButton
        .AutoSize = True
        .Caption = "Cancel"
        .Font.Size = 18
        .Height = 26
        .Left = 36
        .Name = "FORMCANCEL"
        .Top = 196
        .Width = 66
    End With
    With TEMPFORM.CodeModule
         x = .CountOfLines
        .InsertLines x + 1, "Private Sub FORMGO_Click()"
        .InsertLines x + 2, TEMPFORM.Name & ".Hide"
        .InsertLines x + 3, "End Sub"
        .InsertLines x + 4, "Private Sub FORMCANCEL_Click()"
        .InsertLines x + 5, TEMPFORM.Name & _
                            ".Variables.Clear"
        .InsertLines x + 6, TEMPFORM.Name & _ 
                            ".CURRENT_DIR.Clear"
        .InsertLines x + 7, "CANCEL_IT=""1"" "
        .InsertLines x + 8, TEMPFORM.Name & ".Hide"
        .InsertLines x + 9, "End Sub"
    End With
ENDIT:
End Function
Function SELECT_FILE()
    Dim LIBRARY_NAMES(500) 
    CURRENT_DIRECTORY = CurDir()
    If Right(CURRENT_DIRECTORY, 1) <> "\" _
    Then CURRENT_DIRECTORY = CURRENT_DIRECTORY & "\"
    LIBNAME = Dir(CURRENT_DIRECTORY)
    LIBRARY_SIZE = 0
    Do While LIBNAME <> ""
       If LIBNAME <> ".." _
       And LIBNAME <> "." Then
           LIBRARY_SIZE = LIBRARY_SIZE + 1
           LIBRARY_NAMES(LIBRARY_SIZE) = LIBNAME
       End If
       LIBNAME = Dir()
    Loop
    UserForm1.CURRENT_DIR.Clear
    UserForm1.CURRENT_DIR.AddItem CurDir()
    UserForm1.Variables.Clear
    For II = 1 To LIBRARY_SIZE
        UserForm1.Variables.AddItem LIBRARY_NAMES(II)
    Next
    UserForm1.Show
    SELECT_FILE = UserForm1.Variables.Text
End Function