Allow user to choose worksheet from newly opened file

[Chris Lewis]

I have written a macro that performs the same set of commands to a worksheet
for a set of data of a given format.

this data is supplied to me from various sources but is always of the same
format. I want the macro in one excel file to prompt for which file to open
(which I have done using Application.GetOpenFilename) and then prompt the
user for which worksheet should be made active. The names of the sheets are
indicative of what they contain.

So I guess after saying open test.xls I want the user prompted with a list
of the worksheets available in test.xls and allow them to select which
should be made active for the macro to perform its duties.

Any ideas?

Thanks

 

[Nigel]

I created a userform, with a combobox and commandbutton, the form is opened
after the GetOpenFileName dialog ends with

UserForm1.Show

In the userform code, the combobox is filled with the names of all sheets in
the activeworkbook, if a valid name is chosen by the user the selected sheet
is activated,. The command closes the userform. Your code could run after
this.

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex >= 0 Then
Sheets(ComboBox1.Value).Activate
End If
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
ComboBox1.AddItem Ws.Name
Next
End Sub

 

[Chris Lewis]

I created a userform, with a combobox and commandbutton, the form is
opened after the GetOpenFileName dialog ends with

UserForm1.Show

In the userform code, the combobox is filled with the names of all sheets
in the activeworkbook, if a valid name is chosen by the user the selected
sheet is activated,. The command closes the userform. Your code could
run after this.

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex >= 0 Then
Sheets(ComboBox1.Value).Activate
End If
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
ComboBox1.AddItem Ws.Name
Next
End Sub

It werks!! Ta muchly!