Link to home
Start Free TrialLog in
Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland

asked on

how to run a specific macro when excel is open

Hi Experts

I want to press alt and f8, irrespective of what version of excel I am in 2003 or 7...and I want to have I list of specific macro available to run...

how would I do this....I have all the macros...

so if file a.xls comes in via email (open file) I want to press alt and f8 and select macro sub abc() and run it...and if file b.xlsx arrives via email I want to open the file select alt and f8 and run macro sub cde()

And so on...
Avatar of byundt
byundt
Flag of United States of America image

I suggest integrating a list of macros and userform in your Personal.xlsb workbook. You'll need to store it in the XLSTART folder for each of the versions of Excel that you use.

Code to trap ALT + F8 and display a userform listing the macros
'Goes in ThisWorkbook code pane
Private Sub Workbook_Open()
Application.OnKey "%{F8}", "ThisWorkbook.MacroList"
End Sub

Private Sub MacroList()
frmMacroSelector.Show
End Sub

Open in new window


Code behind the userform:
'Goes in code pane behind userform
Private Sub UserForm_Initialize()
Dim i As Long, n As Long
Dim cel As Range, rgMacros As Range
With Worksheets("Sheet1")
    Set rgMacros = .Range("A2")   'Name of first macro
    Set rgMacros = Range(rgMacros, .Cells(.Rows.Count, rgMacros.Column).End(xlUp))
End With
For Each cel In rgMacros.Cells
    lbMacros.AddItem rgMacros.Cells(i + 1).Value, i
    i = i + 1
Next
End Sub

Private Sub cbCancel_Click()
Me.Hide
Unload Me
End Sub

Private Sub cbRun_Click()
Dim v As Variant
v = lbMacros.Value
If IsNull(v) Then
Else
    Application.OnTime Now, v
End If
Me.Hide
Unload Me
End Sub

Open in new window

MacroSelectorQ28170750.xlsm
Avatar of route217

ASKER

hi byundt

firstly, thanks for the excellent feedback. just one question how do I do"I suggest integrating a list of macros and userform in your Personal.xlsb workbook. You'll need to store it in the XLSTART folder for each of the versions of Excel that you use"

the attached workbook is great...
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Do you have a Personal.xlsb workbook? no

I need to repeat your steps for 2003 and 2007...

its my first time at trying to do this...

thanks expert's
Excel 2003 will use Personal.xls
Excel 2003 and later will use Personal.xlsb

You will likely have different XLSTART folders for each of your versions of Excel. The best way to find them is to record a macro and use the Immediate pane statement as previously described.

The Immediate pane is found in the VBA Editor on the right at the bottom of the window. If you don't see it, then use the View...Immediate menu item to display it.

If you type a statement (or paste it) in the Immediate pane, it will be executed immediately when you hit the Enter key. Make sure that the cursor is at the end of the statement before you hit Enter.

A question mark before a statement in the Immediate pane means to display the value returned on the next line. To display the filename and path for Personal.xlsb, use the following statement:
?Workbooks("Personal.xlsb").FullName

Assuming that your macros are already in another workbook, if you drag and drop a Module containing those macros from their current home to Personal.xlsb, then you will have them available in Personal.xlsb. This process is easier than copying and pasting the code.

Brad
If you just store your existing macros in Personal.xls and Personal.xlsb workbooks, they will always be available to you. And you can launch the ALT + F8 macro selector and see them in a dialog like this:
User generated imageNo other code or userforms are necessary.

Brad
hi byundt

totally stuck on this do nor have a clue. ...can you kindly assist. .


https://www.experts-exchange.com/questions/28174602/compile-error-variable-not-defined.html