Displaying all sheets in a workbook using a combo box

Posted by Bill Muirhead on June 27, 2001 1:24 PM

Hi all,

I've written a user form that allows expense sheets to be filled and calculated using a combination of text boxes and option buttons. The sheets are then copied and a new sheet created so that over a period of time a collection of sheets builds up. I would like to add the facility to print a sheet by choosing it from a combo box on the form. Is there a way to populate a combo box with the names of the sheets that exist so far and have this update as new sheets are added?

Am I going about this in the wrong way?

Regards

Bill M

Posted by Damon Ostrander on June 27, 2001 2:12 PM

Hi Bill,

Yes, it is quite easy. I recommend just adding a few lines of code to your userforms Initialze event so that when it loads, the combobox is populated. For example

Private Sub UserForm_Initialize()

For Each w In Worksheets
ComboBox1.AddItem w.Name
Next w

End Sub

will populate ComboBox1 on the userform with the names of all the worksheets in your workbook in index number order. So, if for example you want to activate the sheet when a user selects it in the dropdown, you would add to your userform the code

Private Sub ComboBox1_Change()
Worksheets(ComboBox1.Value).Activate
End Sub

Pretty simple, huh?

Happy computing.

Damon

Posted by Bill Muirhead on June 27, 2001 3:45 PM

Hi Damon,

Thanks for your help, the code works a treat. It may look simple but I searched the help files for ages without success, thanks again.

Regards

Bill