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...
route217Asked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
Do you have a Personal.xlsb workbook?

Assuming that you do, then it is already being stored in the right place (XLSTART folder). If you don't have one, record a macro (it doesn't have to do anything) and choose to store it in Personal.xlsb in the second dropdown ("Store macro in") in the macro recording dialog. Excel will create Personal.xlsb for you and will store it in the right place when you close Excel. In my Windows 7 Pro/Office 2010 system, the right place is:
C:\Users\Admin\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB

Personal.xlsb opens automatically when you launch Excel. That's why it is the best place both for your existing macros and the suggested new code. If you close all other workbooks, you can find where Personal.xlsb is stored with the following command in the Immediate pane:
?Workbooks(1).FullName

One problem with Personal.xlsb is that it is normally hidden. You can make it visible with the following statement in the Immediate pane:
Windows("Personal.xlsb").Visible = True

You can now see the worksheet(s) in Personal.xlsb that had previously been hidden. This makes it convenient for you to paste your list of macros in Sheet1. After you have finished this process, make the window hidden once again with:
Windows("Personal.xlsb").Visible = False

You can paste my suggested code in the ThisWorkbook code pane of Personal.xlsb. You should be able to drag and drop my userform from my sample workbook into Personal.xlsb.

Note: I did find it necessary to tweak one of the subs I had previously suggested because Personal.xlsb won't be the active workbook. The revised code is:
'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 ThisWorkbook.Worksheets(1)
    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

Open in new window

0
 
byundtCommented:
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
0
 
route217Author Commented:
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...
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
route217Author Commented:
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
0
 
byundtCommented:
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
0
 
byundtCommented:
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:
ALT   F8 Macro Selector dialogNo other code or userforms are necessary.

Brad
0
 
route217Author Commented:
0
 
route217Author Commented:
hi byundt

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


http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28174602.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.