Sheets Name into array for form dropdown

A

Al

Hi all.

I'm tring to make a small form with a dropdown list on it, that picks up all sheet tab names in the workbook, while excluding three sheets.

I've never done a form before, so what kind of coding would i need? I.e. a function or a macro?

And i guess the function/macro would need to put the results into a variable array, so then the dropdown box would read it. Is this correct?

Cheers.
Al.
 
P

Patrick Molloy

if your combobox is called say cbSheets then

Sub LoadCombo()
dim ws as Worksheet
for each ws in Worksheets
cbSheets.Additem ws.name
next
end sub

you could call this sub from the form's initialise event:
Private Sub UserForm_Initialize()
LoadCombo
End Sub


Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Hi all.

I'm tring to make a small form with a dropdown list on
Click to expand...
it, that picks up all sheet tab names in the workbook,
while excluding three sheets.
I've never done a form before, so what kind of coding
Click to expand...
would i need? I.e. a function or a macro?
And i guess the function/macro would need to put the
Click to expand...
results into a variable array, so then the dropdown box
would read it. Is this correct?
 
P

Peter Beach

Hi Al,

Well you start by creating a form! You invoke the form from a macro.
Something like:

Sub ShowForm()
UserForm1.Show vbModal
End Sub

To populate the dropdown list you would normally place the code in the
Initialize event of the form. Although there are many ways to populate the
list, the .AddItem method of the combobox control is probably the one that
is most suitable.

Your form code might look something like:

Private Sub ComboBox1_Change()
Debug.Print ComboBox1.Text
End Sub

Private Sub UserForm_Initialize()
Dim i As Long

For i = 1 To ThisWorkbook.Worksheets.Count
ComboBox1.AddItem ThisWorkbook.Worksheets(i).Name
Next i
End Sub

HTH

Peter Beach

Al said:
Hi all.

I'm tring to make a small form with a dropdown list on it, that picks up
Click to expand...
all sheet tab names in the workbook, while excluding three sheets.
I've never done a form before, so what kind of coding would i need? I.e. a function or a macro?

And i guess the function/macro would need to put the results into a
Click to expand...
variable array, so then the dropdown box would read it. Is this correct?
 
Top