Listbox add-in with an overview of important workbooks and ability to open them

Question

I want to create a macro that I can share with co-workers with a UserForm Listbox containing an easy overview of all the important workbooks we have.

I'm just starting out with VBA Excel and have trouble figuring out how the program works. I've searched a lot on this issue but fail to find something that I can use. I suspect that the solution is quite straightforward.

Private Sub ListBox1_Click()

End Sub

Private Sub UserForm_Initialize()

ListBox1.AddItem "WB1"
ListBox1.AddItem "WB2"
ListBox1.AddItem "WB3"

End Sub

Essentially, I just want a Userform with a Listbox of workbooks that I define. The workbooks are in different folder structures so I would need to set the path for each individual workbook.

The UserForm should have a command click box named "Open" that the user clicks on when having selected the workbook he or she wants to open.

Answer 1

Open Workbooks via Userform Listbox

In a very basic form you can use the following code in your UserForm module; of course you can/should rename the used controls to more speaking names (e.g. "OpenFileButton" instead of CommandButton1). Added the "Open file" functionality also to the double click Event.

Option Explicit                  ' declaration head of userform code module

Private Sub doOpenSelectedWB()
' Purpose: add open file functionality (called by CommandButton1 and Listbox1's double click event)
  With Me.ListBox1
     ' [1] Escape proc if no valid item
       If .ListIndex < 0 Or .ListCount = 0 Then Exit Sub
     ' [2] Get selected workbook name via list value (index 0 equals 1st column)
       Dim myFileName As String
       myFileName = .List(.ListIndex, 0)
     ' [3] Open existing workbooks via Workbooks.Open method
       If WBExists(myFileName) Then Workbooks.Open myFileName
  End With
End Sub

Private Sub CommandButton1_Click()
   doOpenSelectedWB
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   doOpenSelectedWB
End Sub

Private Sub UserForm_Initialize()
' Purpose: prefill listbox with array values
  Dim myWBArray()
  myWBArray = Array("C:\Users\Admin\documents\test.xlsx", "C:\Users\Admin\documents\test2.xlsx", "C:\Users\Nowhere\nonsens.xlsm")
  Me.ListBox1.List = myWBArray
End Sub

Helper function WBExists() and formatting of command button

Private Function WBExists(Fullname$, Optional ByVal Infotext$ = "File not found!") As Boolean
' Purpose: Check if workbook exists
  If Dir(Fullname) = vbNullString Then
      MsgBox "File " & Fullname & " not found!", vbExclamation, Infotext
  Else
      WBExists = True
  End If
End Function

Private Sub UserForm_Layout()
' Purpose: name command button
  Me.CommandButton1.Caption = "Open"
End Sub