Solved

how to run a specific macro when excel is open

Posted on 2013-06-28
8
368 Views
Last Modified: 2013-07-03
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...
0
Comment
Question by:route217
  • 4
  • 4
8 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 39285361
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
 

Author Comment

by:route217
ID: 39285815
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
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39285848
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
 

Author Comment

by:route217
ID: 39285878
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 80

Expert Comment

by:byundt
ID: 39285906
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
 
LVL 80

Expert Comment

by:byundt
ID: 39286155
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
 

Author Comment

by:route217
ID: 39293057
0
 

Author Comment

by:route217
ID: 39296305
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now