Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allow user to choose worksheet from newly opened file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allow user to choose worksheet from newly opened file
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allow user to choose worksheet from newly opened file
"Nigel" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newly opened spreadsheet doesn't display in Excel 2007 | Excel Discussion (Misc queries) | |||
Need to Query Excel File Opened by Another User | Excel Discussion (Misc queries) | |||
Recording user and date of when a file is opened | Excel Programming | |||
Close file and run macro from newly opened file | Excel Programming | |||
can't save newly opened files | Excel Discussion (Misc queries) |