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

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

--
Cheers
Nigel



"Chris Lewis" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>
> --
> Chris Lewis
>

"Nigel" <nigel-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>
> --
> Cheers
> Nigel
>
>

It werks!! Ta muchly!

--
Chris Lewis