Add names of all open workbooks to a combo box list

In this post I’m going to show you how to add names of all opened workbooks to a dropdown list. We can develop macros to automate various tasks. Sometimes you may need to let the user run a macro for a specific Excel file from the all opened Excel files. So it enables the user to do changes or formatting to that specific file. But then how can the user select the file to run the macro? To solve this we can develop a user friendly method by creating a VBA form with a dropdown like this.

Then you can add the below code to the initialize event of the userform. Name of the combobox (dropdown) is “cboFileName”.

Private Sub UserForm_Initialize()

     Dim WB As Workbook

     For Each WB In Application.Workbooks
          With cboFileName
               .AddItem WB.Name
          End With
     Next WB

End Sub

See the form in action below.

Now you can call the subroutine through the OK button. Assume you want to run the subroutine called “RemoveEmptyRows” for this file. If the name of the OK button is cmdOK we can write the code for the OK button like this.

Private Sub cmdOK_Click()

     Dim WB_Selected As Workbook

     Set WB_Selected = Workbooks(cboFileName.Value)

     Call RemoveEmptyRows(WB_Selected)

End Sub

Now you can place the RemoveEmptyRows subroutine inside the same form or inside a module.

Sub RemoveEmptyRows(WB As Workbook)

     …………………...
     More codes here
     …………………...

End Sub