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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

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
36,714
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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

ADVERTISEMENT

Rory,

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,714
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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

ADVERTISEMENT

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
76,286
Office Version
  1. 365
Platform
  1. Windows
Change Userform1 to UserForm.
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,175
Office Version
  1. 365
Platform
  1. Windows
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,129,908
Messages
5,638,938
Members
417,060
Latest member
wcbobb

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
Top