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 :)
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,025
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

Wilfried_1983

New Member
Joined
Jul 17, 2008
Messages
15
Rory,

I want to perform this action in a userform.
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,025
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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
 

Wilfried_1983

New Member
Joined
Jul 17, 2008
Messages
15
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,562
Office Version
365
Platform
Windows
Change Userform1 to UserForm.
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
Try:
Code:
Private Sub UserForm_Initialize()
  Dim ws As Worksheet
  For Each ws In Worksheets
    ListBox1.AddItem ws.Name
  Next ws
End Sub
 

Wilfried_1983

New Member
Joined
Jul 17, 2008
Messages
15
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,619
Messages
5,469,748
Members
406,668
Latest member
ElizaR

This Week's Hot Topics

Top