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.

 

[alexjf]

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.

Thanks Jaafar! That worked.

I wasn't sure how I could apply the macros without making a certain report active (I'd have to select a report either way). Since some of the reports I pull are 1 worksheet and some are over 20, I need to ensure I have the formatting applied to the correct tab. Now that you helped me with this (read do all the work for me because I need to get more skill with VBA), I can run a report from the system, select a formatting macro and open the userform before the formatting macro is completed.

 

[Jaafar Tribak]

Generally speaking, an excel object need not be active in order to manipulate it via code. Without knowing the specifics and complexity of the code(s) in your project, it will be difficult for me to give further advice but, to illustrate the idea, select Sheet1 in you workbook and run the following code :

 Sheet2.Range("a1:a10").Interior.Color = vbBlue

The above code will apply the formatting to the range in Sheet2 although Sheet2 wasn't active at the time of the call.

 

[alexjf]

So I was able to figure out how to modify the code so it wouldn't pull in the file path of the workbook names (what was happening every time I ran the macro). Below is the update:

'https://www.teachexcel.com/excel-tutorial/2018/use-macros-with-userforms
' this shows one of the sources for figuring out the macro
'https://www.excel-easy.com/vba/userform.html
' second source for help
'https://www.mrexcel.com/board/threads/excel-vba-drop-down-box-which-lists-all-open-excel-workbooks.505824/
' third source for help
'https://www.google.com/search?q=excel+vba+userform+choose+macro+and+workbook&rlz=1C1GCEB_enUS1000US1000&ei=wAV0Y8SCM7Lm7_UPpYer0A8&ved=0ahUKEwjE4qbmkbH7AhUy87sIHaXDCvoQ4dUDCBA&uact=5&oq=excel+vba+userform+choose+macro+and+workbook&gs_lcp=Cgxnd3Mtd2l6LXNlcnAQAzIFCCEQoAEyBQghEKABMgUIIRCgAToKCAAQRxDWBBCwAzoHCAAQsAMQQzoNCAAQ5AIQ1gQQsAMYAToECAAQQzoFCAAQgAQ6BggAEBYQHjoFCAAQhgM6CAgAEBYQHhAKOgUIIRCrAjoICCEQFhAeEB1KBAhNGAFKBAhBGABKBAhGGAFQ5gdY-zRgyDZoAXABeACAAeYBiAGPJpIBBjAuMTkuN5gBAKABAcgBEMABAdoBBggBEAEYCQ&sclient=gws-wiz-serp
' google search I did
'https://www.mrexcel.com/board/threads/userform-for-selecting-workbook-worksheet-and-macro.1222277/#post-5977579
' source of below code
Option Explicit

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

Private Sub CommandButton1_Click()
    Dim oWb As Workbook, oSh As Worksheet
    
    Set oWb = Workbooks(ComboBox1.Value)
    'changed GetObject to Workbooks to remove fullname requirement
    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.Name
    Next oWb
    ComboBox1.ListIndex = 0
End Sub

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