Worksheet names in ComboBox

Wilfried_1983

New Member
Joined
Jul 17, 2008
Messages
15
Hello there,

My first post on this forum! I am a VBA newbie and trying to program my first macro.

What I want to do is have a ComboBox (I think it should be a ComboBox, I want the user to be able to select multiple options at once) and display the names of the worksheets in the ComboBox. I could not find a way to do this directly with rowsource and thought about writing a loop to check the current worksheets with their names and put the values into cells, to this way have the names displayed in the ComboBox via rowsource?

Your help will be much appreciated :)
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi,

Welcome to the board!

Here is non-vba method.

Define wsArray

Refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Add.

Now say in A1 on sheet1 and copied down,

=IF(ROWS($A$1:$A1)<=COUNTA(wsArray),INDEX(wsArray,ROWS($A$1:A1)),"")

Define this range dynamically.

Define Shts

Refers to: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH("*",Sheet1!$A:$A,-1))

Add.

Type Shts in ListFillRange of the Combobox.

HTH
 
Upvote 0
If you want multiple options, you don't want a combobox, you need a listbox and you need to change its multiselect property. Is this on a userform or on a worksheet?
 
Upvote 0
Hi, Wilfried
Place a Combobox on sheet. (Combobox1)
Run this code seperately.
Combobox filled with Sheet Names.
Code:
Dim sht As Worksheet, txt As String
ComboBox1.Clear

For Each sht In ActiveWorkbook.Worksheets
      ComboBox1.AddItem sht.Name
Next sht

ComboBox1.ListIndex = 0
Regards Mick
 
Upvote 0
Then in the Userform's Initialize event you need a loop like:
Code:
For Each sht In ActiveWorkbook.Sheets
      Me.ListBox1.AddItem sht.Name
Next sht
 
Upvote 0
I wrote the following code and have used the same names, I do not see any data in my listbox.

Option Explicit
Private Sub Userform1_Initialize()
For Each sht In ActiveWorkbook.Sheets
Me.ListBox1.AddItem sht.Name
Next sht
End Sub

Am I missing something?

P.S. I typed this code in the code of the respective userform
 
Upvote 0
Change Userform1 to UserForm.
 
Upvote 0
Try:
Code:
Private Sub UserForm_Initialize()
  Dim ws As Worksheet
  For Each ws In Worksheets
    ListBox1.AddItem ws.Name
  Next ws
End Sub
 
Upvote 0
I changed the name and also declared two variables, because I received an error message.

Code:
Option Explicit
Private Sub Userform_Initialize()
Dim sht As Worksheet, txt As String
For Each sht In ActiveWorkbook.Sheets
      Me.ListBox1.AddItem sht.Name
Next sht
End Sub

Thank you guys for your help! Learned a lot today
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top