Sheets Name into array for form dropdown

[Guest]

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.

 

[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

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?

 

[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

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?

 

[Guest]

Thanks for your reply guys.

Works a treat.

Cheers.
Al