Improve company productivity with a Business Account.Sign Up

x
?
Solved

xlsm macro available in xls

Posted on 2014-04-15
2
Medium Priority
?
378 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.

Join & Write a Comment

I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

585 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