Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

xlsm macro available in xls

Posted on 2014-04-15
2
Medium Priority
?
376 Views
Last Modified: 2014-04-16
Hi,
i am overtaking a project from someone else who left the company. i have a xlsm file with 2 macros in it.
2 other users do have 2 lil buttons/icons in their ribbon to execute that macro.
now i have a new hire and i dont know how to bring that macro or that xlsm file into their excel so that i can customize the ribbon to.
how do i link excel to that maco in that xlsm file?

thank you
0
Comment
Question by:TLANGI
  • 2
2 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 40003052
If the buttons on the ribbon are part of the Custom UI part of the file, then any Excel 2007 user should see those buttons when they open the file. Should that be the case, store the Excel file in the XLSTART folder in the new user's computer. The workbook will then be opened automatically whenever Excel launches, and the macros will always be available.

It is also possible to store the macros in a Personal.xlsb macro workbook, once again in the new user's XLSTART folder. If the user doesn't have one, the easy way to get one is:
1.  Record a new macro, making sure to choose to store the macro in "Personal macro workbook" in the first step of the macro recording wizard
2.  The macro doesn't need to do anything, so you can quit recording as soon as you start
3.  Open the .xlsm file that contains the macros
4.  ALT +F11 to open the VBA Editor
5.  I assume the macros are in Module1 in the .xlsm file. If so, drag Module1 from the .xlsm file into Personal.xlsb
6.  Quit Excel, making sure to save Personal.xlsb when prompted

To add an icon to the ribbon:
1.  Rightclick the ribbon and choose "Customize the ribbon..."
2.  In the "Choose commands from" field on the left, select Macros
3.  In the resulting pane on the right, choose the menu you want to add the icons to, then click "New Group"
4.  Choose a macro, then click the Add>> button between the two panes
5.  Repeat for the other macro
0
 
LVL 81

Expert Comment

by:byundt
ID: 40003055
Storing a custom icon in the ribbon by means of Custom UI additions to the .xlsm file is fairly fiddly. Microsoft Excel MVP Ron de Bruin has a series of webpages that discuss the topic in detail. The index page for those is at http://www.rondebruin.nl/win/section2.htm
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

877 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