How to populate a Userform ComboBox with all sheet names in Excel?
This article is talking about populating all sheet names of current workbook into a userform Combo Box in Excel.
The following VBA code can help you insert all sheet names of current workbook into a userform combo box. Please do as follows.
1. Press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, please insert a Userform by clicking Insert > Userform as below screenshot shown.
3. Then draw and insert a combo box control into the Userform. See screenshot:
4. Right-click the inserted combo box, and select View Code from the context menu. See screenshot:
5. In the opening Microsoft Visual Basic for Applications window, please copy and paste below VBA code to replace the original one in the Code window.
VBA code: Populate a Userform ComboBox with all sheet names of current workbook
Private Sub UserForm_Initialize() Dim I As Long Me.ComboBox1.Clear For I = 1 To Sheets.Count Me.ComboBox1.AddItem Sheets(I).Name Next Me.ComboBox1.Value = ActiveSheet.Name End Sub
Note: In the code, ComboBox1 is the name of the inserted Combo Box. Please change it to the one of your own.
6. Press the F5 key to run the code. In the opening Userform, you can see all sheet names of the current workbook are displaying in the combo box as below screenshot:
Easily list all sheet names in Excel
The Create List of Sheet Names utility of Kutools for Excel can help to easily list all worksheet names of active workbook in a new worksheet in Excel. And you can shift to corresponding worksheet directly with clicking on the sheet name as the below screenshot shown.
Download and try it now! (30-day free trail)
- How to copy Combo Box value to active cell in Excel?
- How to display date format in combo box output in Excel?
- How to open a specific worksheet by the selected value of a Combo Box in Excel?
- How to prevent or disable typing in a combo box in Excel?
- How to tab out of combo box to select specific cell in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!