UserForm for Selecting Workbook Worksheet and Macro

alexjf

New Member
Joined
Oct 28, 2013
Messages
11
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.

1668550686848.png
 
Try this :
VBA Code:
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.
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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 :
VBA 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.
 
Upvote 0
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:

VBA Code:
'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
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top