Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

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.

Dropdown to add names of all the open workbooks

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.

Completed form
Names of all the opened Excel files are in dropdown list
One file selected

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

Contact Form


Email *

Message *