UserForm for Selecting Workbook Worksheet and Macro

[alexjf]

I'd like to put together a userform so that I can choose from dropdown menus the workbook, the worksheet and a macro. I have a number of macros that are used for modifying certain reports and would like to be able to run the macro based on selecting 1 workbook then 1 worksheet then 1 macro so that I apply the Income Statement macro to the 3rd tab of the 5 files I have open without modifying the Balance Sheet in that same workbook.

Any advice?

Below is a basic userform I was trying to get functioning but was unable to do so.

 

[Jaafar Tribak]

To flexibly run macros in different workbooks based on their name , you could use the excel Run Method.

 

[alexjf]

To flexibly run macros in different workbooks based on their name , you could use the excel Run Method.

Thanks Jaafar, but that won't be workable here. If I knew the name, I would consider that. However, every time I run a report from the system, it will generate a random name like Current.Holdings2022.11.15.183330 or something that means it is always unique. Because of that always unique naming convention, I'd need a dynamic listing of the currently open workbooks and worksheets.

 

[Jaafar Tribak]

Wouldn't those random names be known by the the time the userform is displayed ?

 

[alexjf]

Wouldn't those random names be known by the the time the userform is displayed ?

The Run method requires that I have the name of the workbook in the macro though which means I'd have to customize the macro each time. The reason I was hoping to get the userform working is so that it would display the available workbooks and worksheets and then selecting those would update the active workbook and worksheet the macro would be applied to.

I can setup the macros to be run based on whatever workbook and worksheet I set to be active but I don't know how to get the userform setup to have the selectable options.

 

[Jaafar Tribak]

You could set up the workbooks & worksheets comboboxes like this :

In UserFom Module:

Option Explicit

' ComboBox1 => Displays workbooks
' ComboBox2 => Displays worksheets

Private Sub UserForm_Initialize()
    Dim oWb As Workbook
    For Each oWb In Workbooks
        ComboBox1.AddItem oWb.FullName
    Next oWb
    ComboBox1.ListIndex = 0
End Sub

Private Sub ComboBox1_Change()
    Dim oSh As Worksheet
    For Each oSh In Worksheets
        ComboBox2.AddItem oSh.Name
    Next oSh
End Sub

 

[Jaafar Tribak]

Oops ! Ignore that code.

Here is the correct one :

Option Explicit

' ComboBox1 => Displays workbooks
' ComboBox2 => Displays worksheets

Private Sub UserForm_Initialize()
    Dim oWb As Workbook
    For Each oWb In Workbooks
        ComboBox1.AddItem oWb.FullName
    Next oWb
    ComboBox1.ListIndex = 0
End Sub

Private Sub ComboBox1_Change()
    Dim oSh As Worksheet
    
    ComboBox2.Clear
    For Each oSh In GetObject(ComboBox1.Value).Sheets
        ComboBox2.AddItem oSh.Name
    Next oSh
End Sub

 

[alexjf]

Oops ! Ignore that code.

Here is the correct one :

Option Explicit

' ComboBox1 => Displays workbooks
' ComboBox2 => Displays worksheets

Private Sub UserForm_Initialize()
    Dim oWb As Workbook
    For Each oWb In Workbooks
        ComboBox1.AddItem oWb.FullName
    Next oWb
    ComboBox1.ListIndex = 0
End Sub

Private Sub ComboBox1_Change()
    Dim oSh As Worksheet
   
    ComboBox2.Clear
    For Each oSh In GetObject(ComboBox1.Value).Sheets
        ComboBox2.AddItem oSh.Name
    Next oSh
End Sub

Jaafar, that nearly get's me to what I was hoping for. However, I still need to activate the workbook and worksheet after that is completed. I thought I could modify the code you provided to include a CommandButton1_Click but apparently I'm not doing that right. Could you take a look?

Option Explicit

Private Sub CommandButton1_Click()
    Windows(ComboBox1).Activate
    Sheets(ComboBox2).Select
End Sub

 

[alexjf]

So I almost have this working. I can see all the workbooks I have open and the sheets in those workbooks. I've added a commandbutton for navigating to the workbook and worksheet and close the userform. The only problem is that when I choose to go to a different workbook, it won't go there because (I think) it displays the full name including file path.

I've tried changing the ComboBox1.AddItem oWb.FullName to be ComboBox1.AddItem oWb.Name but that gives me an error message. Since I believe I have to use just the workbook name for this to work, I'm not sure how to address this.

 

[Jaafar Tribak]

Try this :

Option Explicit

' ComboBox1 => Displays workbooks
' ComboBox2 => Displays worksheets

Private Sub CommandButton1_Click()
    Dim oWb As Workbook, oSh As Worksheet
    
    Set oWb = GetObject(ComboBox1.Value)
    Set oSh = oWb.Sheets(ComboBox2.Value)
    oSh.Activate
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Dim oWb As Workbook
    For Each oWb In Workbooks
        ComboBox1.AddItem oWb.FullName
    Next oWb
    ComboBox1.ListIndex = 0
End Sub

Private Sub ComboBox1_Change()
    Dim oWb As Workbook, oSh As Worksheet
    
    ComboBox2.Clear
    Set oWb = GetObject(ComboBox1.Value)
    For Each oSh In oWb.Sheets
        ComboBox2.AddItem oSh.Name
    Next oSh
    ComboBox2.ListIndex = 0
End Sub

BTW, you don't necessarly need to select a worksheet in order to manipulate it via code.