Sheet names in a form dropdown
 

Hi I have created a form and I would like to have a drop down on the form
populated with the names of each sheet in my workbook (Other than the sheet
named "Menu")

Can someone tell me how to do this.

Thanks

Rob

Sheet names in a form dropdown
 

"Rob Hargreaves" schrieb im Newsbeitrag
...
Hi I have created a form and I would like to have a drop down on the form
populated with the names of each sheet in my workbook (Other than the
sheet named "Menu")

Can someone tell me how to do this.

Thanks

Rob

Hi rob,

try the following in the Initialize event of your form

Private Sub UserForm_Initialize()
Dim mySheet As Worksheet
For Each mySheet In Worksheets
If mySheet.Name < "Menu" Then

myDropDownCtrlName.AddItem mySheet.Name

End If
Next
End Sub


Stephan

Sheet names in a form dropdown
 

Here's something I use with the worksheet activate...

Private Sub Worksheet_Activate()
Dim cell As Range, ws As Worksheet
Application.ScreenUpdating = False
Dim x As Integer
With ActiveSheet
.ListBox1.Clear
x = 1
For Each ws In ActiveWorkbook.Worksheets

If ws.Name < "Index" Then
.ListBox1.AddItem ws.Name
Cells(x, 1) = ws.Name
x = x + 1
End If
Next
End With
Range("a1").Select
Application.ScreenUpdating = True
End Sub

--
steveB

Remove "AYN" from email to respond

Remove "AYN" from email to respond
"Rob Hargreaves" wrote in message
...
Hi I have created a form and I would like to have a drop down on the form
populated with the names of each sheet in my workbook (Other than the
sheet named "Menu")

Can someone tell me how to do this.

Thanks

Rob

Sheet names in a form dropdown
 

Rob,

If you are talking about a userform try adding this to the Initialize event.
Change Combobox1 to your Combobox name.

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

If this is a worksheet form you will need to create a range containing a
list of all the worksheet names and set the ListFillRange property of the
Dropdown box to that range.

HTH,
Matt









"Rob Hargreaves" wrote:

Hi I have created a form and I would like to have a drop down on the form
populated with the names of each sheet in my workbook (Other than the sheet
named "Menu")

Can someone tell me how to do this.

Thanks

Rob