Solved

xlsm macro available in xls

Posted on 2014-04-15
2
366 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Find word and 6 digit number 22 93
vba in excel to send reply for sent email including original message 2 61
Question to Pivot table 1 33
Moving Excel to AaaS 4 36
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

947 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

22 Experts available now in Live!

Get 1:1 Help Now