Opening Workbooks and using a drop down menu

[kaileewhiting]

Hello fellow Mr. Excel users!

At my company we use Excel to keep and log a lot of our records, many of which need to be inputted into daily. Each workbook is put into one large folder and we float within that folder opening and closing the previous workbook using the command Ctrl-R. When this command is inputted a window saying "Enter the sheet to be activated" is displayed.

However it sometimes can be hard to find the correct workbook that needs to be edited due to poor handwriting. It becomes a pain to have to minimize the current Excel workbook to scroll through the folder to find the one that needs to be open. Is it possible for a scroll bar or drop down menu to be displayed that will show all the Excel files within the folder instead of having to minimize and manually search through it.

Also by the way- we're using Microsoft 2003.

 

[RedBeard]

This should work:

Private Sub Workbook_Open()

Dim fileList() As String
    Dim fName As String
    Dim fPath As String
    Dim I As Integer
    'define the directory to be searched for files
    fPath = "C:\temp\"
    
    'build a list of the files
    fName = Dir(fPath & "*.*")
    While fName <> ""
        'add fName to the list
        I = I + 1
        ReDim Preserve fileList(1 To I)
        fileList(I) = fName
        'get next filename
        fName = Dir()
    Wend
    'see if any files were found
    If I = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
    'cycle through the list and add to listbox
    For I = 1 To UBound(fileList)
    Sheet1.ComboBox1.AddItem fileList(I)
    Next

End Sub

Providing you have a combobox on sheet1.