Sheets Name into array for form dropdown
 

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.

Sheets Name into array for form dropdown
 

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
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.
.

Sheets Name into array for form dropdown
 

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" wrote in message
...
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.