how to assign a macro to a button in powerpoint

Add commands to your presentation with action buttons

Use an Action button to do an action in your presentation, such as going to the next or a specific slide, running an app, or playing a video clip. There are two basic steps for setting up an Action button:

First, you pick an action button from the Shapes gallery

Then you assign an action that occurs when (during Slide Show) you click the shape or point the mouse at it.

The idea is that when you deliver your presentation, you can click or point at an action button to:

Go to the next slide, the previous slide, the first slide, the last slide, the most recent slide viewed, a specific slide number, a different PowerPoint presentation, or a Web page.

Run a program

Run a macro

Play an audio clip

Put an action button on your slide

Some examples of built-in action button shapes in the Shapes gallery include right and left arrows (commonly understood symbols for going to next, previous, first, and last slides), for playing videos or sound bites, and more.

On the Insert tab, click Shapes , and then under Action Buttons at the bottom of the menu, click the button shape that you want to add.

Click a location on the slide, and then drag to draw the shape for the button.

Keep the dialog box open and go to the next procedure.

Assign an action

If you want the action to occur when the action button is clicked , do the following steps on the Mouse Click tab of the dialog box.

If you want the action to occur when the action button is simply pointed at , do the following steps on the Mouse Over tab of the dialog box.

To choose the action that takes place when you click or move the pointer over the action button, do one of the following:

Choose Hyperlink to and then select the destination (for example, the next slide, the previous slide, the last slide, or another PowerPoint presentation) that you want the hyperlink action to go to.

Note:  To link to a file created by another program, such as a Word or Excel file, in the Hyperlink to list, click Other File .

Choose None to use the shape without a corresponding action.

Choose Run program , then click Browse to locate the program that you want to run.

Choose Run macro and then select the macro that you want to run.

The Run macro settings are only available if your presentation already contains a macro, and when you save the presentation, you must save it as a PowerPoint Macro-enabled Show. (For more information about macros, see Run a macro .)

If you want the shape that you chose as an action button to perform an action, click Object action , and then select the action that you want it to perform.

The Object action settings are only available if your presentation contains an OLE object.

To play a sound, select the Play sound check box, and then select the sound that you want to play.

When you've finished choosing your action, select OK .

To test the action you've selected, choose Slide Show > From Current Slide to open the presentation in Slide Show so that you can click the action button.

Add a picture and assign it an action

On the Insert tab, in the Illustrations group, click Picture . If you want to add a piece of Smart art, click SmartArt

In the Insert Picture dialog box, locate the picture that you want to add, and then click Insert .

Note:  For SmartArt graphics, you can only assign an action to the text within the shapes, but not to the individual shapes in a SmartArt graphic.

Click the picture you added, and then on the Insert tab, in the Links group, click Action .

In the Action Settings dialog box, do one of the following:

To choose the behavior of the picture when you click it in Slide Show view, click the Mouse Click tab.

To choose the behavior of picture when you move the pointer over it in Slide Show view, click the Mouse Over tab.

To choose the action that takes place when you click or move the pointer over the picture, do one of the following:

To use the shape without a corresponding action, click None .

To create a hyperlink, click Hyperlink to , and then select the destination (for example, the next slide, the previous slide, the last slide, or another PowerPoint presentation) that you want the hyperlink action to go to.

To link to a file created by another program, such as a Microsoft Office Word or Microsoft Office Excel file, in the Hyperlink to list, click Other File .

To run a program, click Run program , click Browse , and then locate the program that you want to run.

To run a macro, click Run macro , and then select the macro that you want to run.

If you want the picture or clip art to perform an action other than what is listed, click Object action , and then select the action that you want it to perform.

Note:  The Object action settings are available only if your presentation contains an OLE object.

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

Categories:

Cloud (204)

Entertainment (11)

Facebook (43)

General (50)

Programming (64)

Technology (398)

Testing (4)

Tools (488)

Twitter (5)

Wearable (26)

Web Design (44)

Collections:

Best World Music

BitTorrent FAQ

Facebook FAQ

Firefox FAQ

Flash Player FAQ

Form Tools FAQ

FrameMaker FAQ

Internet Connection

Jupyter FAQ

LinkedIn FAQ

Office 365 FAQ

OpenOffice FAQ

Outlook FAQ

PowerPoint FAQ

Word (Microsoft) FAQ

Other Resources:

Software QA Resources

Developer Resources

DBA Resources

Windows Tutorials

Java JAR Files

File Extensions

Security Certificates

Regular Expression

Link Directories

Interview Q & A

Biotech Resources

Research Paper DOI

Cell Phone Resources

Travel Resources

Frequently Asked Questions

FYIcenter Forum

Cloud Entertainment Facebook General Life Programming Technology Testing Tools Twitter Wearable Web Design

Home Hot About Collections Index RSS Atom Ask

Tester Developer DBA Windows JAR DLL Files Certificates RegEx Links Q&A Biotech DOI Phones Travel FAQ

Home > Tools

Creating Command Buttons to Run Macros in PowerPoint

How to create a command button to run my macro in slide show in PowerPoint? I have created a macro that displays message dialog box. I want to add a command button my slide. When the button is clicked while in slide show view, I want the message box to be displayed.

✍: FYIcenter.com

1. Click the Developer tab. You see the Developer tab menu showing up.

2. Select the Command Button icon in the Controls menu group. Then press and drag the mouse on the slide to create a command button.

3. Click the right mouse button on the command button and select Properties from the context menu. You see the Properties box showing up.

4. Change the value in the (Name) field to "RunMacro". Then change the value in the Caption field to "RunMacro".

5. Click the right mouse button on the command button and select View Code from the context menu. You see the Visual Basic window showing up.

6. Enter the following code and close Visual Basic window. "HelloMacro" is the name of the macro to be executed.

7. Click the Slide Show icon and click the command button to test it.

⇒ Questions on Microsoft PowerPoint 2013

⇐ Testing Macros without Slide Show in PowerPoint

⇑ Programming Macro Code in PowerPoint

⇑⇑ MS PowerPoint - Frequently Asked Questions

2017-01-25, 23881👍, 1💬

Related Topics:

💬 2017-01-25 jose : Can I only run the macro in the button in the slide show view? Or is there a way I can press it in the normal view of the slide ...

💬 2021-11-08 Vrishin Vadakattu : Thank you it helps!

Copyright © 2023 FYIcenter.com

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.

Popular Posts:

Google Ads Not Showi...

Why Google ads are not showing on Firefox browser? I just upgraded to Firefox 43. Starting from Fire...

Microsoft Teams File...

Where are Microsoft Teams files located on my Windows 7 system? If you have Microsoft Teams desktop ...

Convert Word Documen...

How to save word documents into Unicode UTF-8 text files? When I convert Word documents into text fi...

SFTP Key Exchange Pr...

How to see the key exchange process in an SFTP connection with FileZilla FTP Client? If you are conn...

Creating a New Power...

How to create a new PowerPoint Template? I have created a slide master and some useful slide layouts...

Eileen's Lounge

Skip to content

• Portal Board index Office Applications Powerpoint

Assign the macro for shapes

Post by BOOBALAN.V » 19 Dec 2019, 06:58

Re: Assign the macro for shapes

Post by HansV » 19 Dec 2019, 08:37

Post by BOOBALAN.V » 19 Dec 2019, 08:43

Post by HansV » 19 Dec 2019, 08:45

Post by BOOBALAN.V » 19 Dec 2019, 10:25

Return to “Powerpoint”

• Site Related
• ↳   Words From On High
• ↳   Lounge Matters
• ↳   Lounge Utilities, Tips and Tricks
• Operating Systems
• ↳   Windows General
• ↳   Windows 11
• ↳   Windows 10
• ↳   Windows 8
• ↳   Windows 7
• ↳   Windows Vista
• ↳   Windows XP
• ↳   Windows Server
• ↳   Older Windows Versions
• ↳   Other Operating Systems (OS X, Linux)
• Office Applications
• ↳   Office General
• ↳   Word
• ↳   Excel
• ↳   Powerpoint
• ↳   Access/SQL
• ↳   VB/VBA/.Net
• ↳   Other MS Office Applications
• E-mail, Browsers and Social Networking
• ↳   Outlook
• ↳   Windows (Live) Mail/Outlook Express
• ↳   Other E-mail Clients
• ↳   Internet Explorer
• ↳   Mozilla Firefox
• ↳   Other Browsers
• ↳   Social Networking
• Other Serious Stuff
• ↳   Networking and Wireless
• ↳   Security and Backup
• ↳   Web Development
• ↳   Graphics and Video Software
• ↳   Beta Software
• ↳   Mobile Devices and Applications
• ↳   Other Applications
• ↳   Hardware
• Light Relief
• ↳   Puzzles
• ↳   Scuttlebutt
• ↳   Cooking
• Portal Board index
• All times are UTC

Powered by phpBB ® Forum Software © phpBB Limited

Privacy | Terms

• Skip to primary navigation
• Skip to main content
• Skip to primary sidebar

Peltier Tech

Peltier Technical Services - Excel Charts and Programming

How To: Assign a Macro to a Button or Shape

Wednesday, March 12, 2008 by Jon Peltier 124 Comments

In recent posts, I’ve discussed recording macros , fixing recorded macros , and even using someone else’s macros . To run a macro, you have a few choices.

• Tools menu > Macro > Macros, select the Macro, and click Run
• Shortcut Key combo Alt+F8, select the Macro, and click Run
• Run Macro button on the Visual Basic toolbar, select the Macro, and click Run
• Click in the macro with the mouse and press F5

None of these techniques are particularly elegant, and you wouldn’t want to email a file with a few macros to a colleague and tell them, “Okay, go to the Tools menu, select Macro, then select Macro, then click on MyFirstMacro in the list, and click Run.” It would be nice to just tell them, “Click the First Macro button.”

You can assign a macro to a few types of objects:

• Forms Menu Button or Shape
• ActiveX Button
• Menu or Toolbar Button

In this post we’ll look at using a Forms menu button to run our macros. We’ll use this simple macro for our example.

When it is run, we see a simple message.

Forms Menu Controls

First make the Forms toolbar visible. Like so many other things in Excel, there are several ways to accomplish this.

• Tools menu > Customize > Toolbars tab, check the box in front of Forms
• View menu > Toolbars, select Forms from the list
• Right click on the menu and toolbar area, choose Forms from the list

The Forms toolbar will appear.

Select the Button button, then draw a rectangle in the worksheet where you want to place the button (you can move and resize it later). The Assign Macro dialog pops up with a list of macros in the active worksheet for you to select from.

Select a macro and click OK, or Cancel (you can select a macro at a future time). You can edit the text of the button after you dismiss the dialog, or right click on the button any time.

You can assign a macro to any of the Forms toolbar controls.

You can assign a macro to any shape (including any chart) on a worksheet. Right click on the shape, choose Assign Macro, and follow the steps above.

Share this:

• Click to share on Twitter (Opens in new window)
• Click to share on Facebook (Opens in new window)
• Click to share on LinkedIn (Opens in new window)
• Click to share on Tumblr (Opens in new window)
• Click to share on Pinterest (Opens in new window)
• Click to share on Reddit (Opens in new window)
• Click to print (Opens in new window)
• Click to email a link to a friend (Opens in new window)

Posted: Wednesday, March 12th, 2008 under VBA . Tags: buttons , macro . Comments: 124

Reader Interactions

Thursday, March 13, 2008 at 7:39 am

Great post! I am a big fan of assigning macros to a button so the user can easily run ’em without having to go through the menu.

One thing that typically trips people up is the default security setting for Excel. Many times, if you send someone an .xls file with a macro, they either need to approve the sender or set their security lower. Otherwise, they won’t be able to run the macro. I’m sure most readers know this, but I think it’s worth bringing up.

Jon Peltier says

Thursday, March 13, 2008 at 8:59 am

Tony – Good point. One of these days I’ll blog about macro security.

Andras says

Tuesday, March 18, 2008 at 9:41 am

I’m very helpful for your site – it gave me many help in my job. In addition your blog is also very useful – but as you’ve wrote, it took a bit time to find it.

However, please let me ask a question regarding to this blog entry. Is there any way to assign an UDF to a button or to a menu/toolbar item in Excel?.

When I’ve tried to assign to a toolbar some of my UDFs stored in my personal.xls, but only the simple VBA routines appear, not the UDFs.

I’m currently using MS Excel 2002.

I’d be very much appreciated if you could share us your experiences in this.

Thank you for your help in advance

Kind regards, Andras Ujszaszy

Tuesday, March 18, 2008 at 1:41 pm

Hi Andras, By definition a UDF is used in a cell or in another VBA procedure and simply returns a value. When you click on a button you are not so much expecting to get a value as having some action perfomred. How would your UDF work from a button click?

Sergey says

Sunday, December 14, 2008 at 8:57 am

Hi Jon, thank you for making this great blog!

I have a question about assigning macro to control button:

“The Assign Macro dialog pops up with a list of macros in the active worksheet for you to select from.” Issue is it doesn’t pop up in my 2003 excel, moreover in right-click menu there is no command “assign macro”. If I create picture or object (not control button) – I can see this command in menu.

Could you please advise why it doesn’t work with control buttons?

Sunday, December 14, 2008 at 10:06 am

Hi Sergey –

This tip relates to the controls from the Forms toolbar, which behave much like inserted pictures or shapes.

Controls Toolbox objects are a different species altogether. For those you need to follow the procedure in How To: Assign a Macro to an ActiveX Control .

Victor says

Tuesday, December 16, 2008 at 1:03 pm

Hey there Jon, Really appreciate your site. In fact I’ve been using the Button to send selected graphs over into PowerPoint (based on an article by yourself). When one day I ran into some trouble. I thought I’d spice up the buttons by replacing them with a Shape (and some fancy coloring/shading). Trouble is, now I can’t shoot em over to PPT. Think you could tell me what I’m missing? Best regards Victor

Tuesday, December 16, 2008 at 1:54 pm

Victor –

If the button is a Forms toolbar button, it interacts with a macro the same way as a shape (right click > Assign Macro…). If it’s a controls toolbox button, the protocol is different, as described in How To: Assign a Macro to an ActiveX Control .

Or do you mean you added shapes to the chart? If you select the chart, then insert the shape, the shape is part of the chart. If you just added the shape, it’s part of the worksheet, not the chart, and grouping the chart to the shape makes another shape, which the chart objects code will not recognize.

Tuesday, December 16, 2008 at 3:48 pm

Actually, I simply inserted a shape into a worksheet, made a chart just somewhere on the worksheet, assigned a macro to the “Shape” (not button ;) that is supposed to transfer my chart but I get my error alert saying “please select a chart” when I click on the macro associated shape. If I use a button from the form control, everything is a ok . The problem I believe lies with the following ; when I select a chart to transfer, then hit the “Shape” that I’ve assigned a macro to (the macro doing the transferring) I believe excel “deselects” the prior selected chart and instead selects the shape then activates the macro causing the above mentioned error. I would use a button instead of the “shape” but the shape looks cool :).

Tuesday, December 16, 2008 at 4:54 pm

Intriguing. In Excel 2003, I created a column chart and wrote this code:

  Then I inserted a Forms button and a shape, and applied this macro to both. If I select the chart and click the button, the chart type changes. If I select the chart, change it back to a column chart, and click on the shape, again the chart type changes. The chart remains selected, though after the macro runs it is selected with white handels, meaning the shape containing the chart is actually what is selected.

Ricardo says

Thursday, January 22, 2009 at 5:48 am

I`d like to assign a macro(MouseMove) to a shape like you show above, but whan i click on the shape and chosse Assign Macro, the event assign is always the click event. So my doubt is, could i assing another event like MouseMove instead of Click ?

Tks for your help.

Thursday, January 22, 2009 at 7:02 am

Excel proposed a default macro name that mimics a click event (e.g., Rectangle1_Click), but it isn’t really an event in the sense that a command button or other ActiveX control has events it can respond to. You can only assign the name of an existing macro.

In the Excel object model, shapes expose no events that your code can respond to. The worksheet itself has a limited set of events you can code against, while charts and AcitveX controls have a rich set of events you can exploit.

Wednesday, March 18, 2009 at 11:46 pm

Hi Jon I encounter sporadic problems when assigning macros to objects – i.e. the macros fail to run when the objects are re-positioned on the same worksheet. Sometimes they work, and sometimes they don’t! There doesn’t seem to be anything wrong with the macros themselves.

Would you happen to know why or have a solution for this?

Many thanks. Mei Malaysia

Thursday, March 19, 2009 at 5:45 am

Mei –

I have never experienced this problem exactly. What version of Excel are you using? There are a couple funny things about assigning macros to shapes in Excel 2007. If that’s your version I would like to try to replicate the problem.

Thursday, March 19, 2009 at 6:59 pm

Hi Jon, Thank you for your reply. Indeed I am using Excel 2007, on a Vista OS.

It’s a simple macro to copy & paste by appending to a columnn of values. I assigned this macro to a shape that I drew somewhere on the worksheet. When I click the enabled shape, it works fine, however, the moment i move the shape to another part of the worksheet – even if it’s near by, the macros become very unstable. They work some of the time, and fail altogether at others.

It’s quite strange.

Many thanks. Mei.

Thursday, March 19, 2009 at 10:23 pm

Could you use a regular button as opposed to a shape for this? I’ve heard that using buttons (use Forms toolbar buttons, which nominally work just like shapes) is more reliable than using shapes.

Thursday, March 19, 2009 at 10:53 pm

Thanks Jon, will try it out and share any further problems if I encounter them.

Wednesday, December 9, 2009 at 9:28 am

Hi I am facing problem to assign macro in bar chart. In fact I am drawing the chart using macro but I need to make some changes such that edit grid lines, lebelling axes, figure name etc. in the bar chart. I tried using assign macro by clicking right on the chart followed by assigning a name and record. After that I did the modifications and stop it. However, it is showing error. I will be grateful if anyone helps.

Wednesday, December 9, 2009 at 9:34 am

Hi I am trying to convert the text cell to number automatically using macro. But it’s not working. In fact, I start as follows. start recording >> select the cells>> right click on error to convert to number>> stop recording. When I ask macro to run it again, it only selects the cells but not converts to number. Hence, I have to do it manually. Moreover, I checked VBA code as well; it doesn’t write anything to convert to number.

Any help will be appreciable.

Wednesday, December 9, 2009 at 9:40 am

Razib –

I don’t understand the question about the chart and macro.

To get a macro that converts text to numbers, select the range then start the macro recorder, change the number format from text to a numerical format (if necessary), then use Columns to Text to do the actual conversion.

Wednesday, December 9, 2009 at 11:22 am

Thank you for the reply. I did the same except the last part, “then use Columns to Text to do the actual conversion” as you mentioned, but it didn’t work. Macro didn’t write anything in VBA and it is not executing the operation when I run the recorded macro. However I don’t understand the last part that you mentioned. What do you mean by use columns to text to get final conversion? I don’t get anything like that.Your suggestion will be appreciated.

For the chart what I want to do is that after making the bar chart I want to lebel the chart automatically using macro e.g., change the grid lines, lebel x & y-coordinate (plant vs yr) ans so on. I tried but I am unable to do that. Can you please suggest?

Thank you again for your help.

Wednesday, December 9, 2009 at 12:17 pm

You did everything except text to columns? Text to Columns is the part that does the conversion.

About the chart, if you’re using Excel 2007 to record your macro, you won’t get much useful code. Microsoft rushed 2007 out the door before they had time to make everything work right. You can either find an earlier version of Excel for recording macros, or wait for Excel 2010 to come out in a half year.

Thursday, December 10, 2009 at 12:41 am

Thank you for your suggestion. I am able to modify the charts using macro in 2003 but not 2007. I am sorry to say that for the first problem to convert string to number, I am unable to find any command to convert “column to text”. I am explaining the fact again. I have selected the cells and click on the command “convert to number”. Then it converts the text to number. Then stop recording the macro. Now if I run the macro, it only selects those cells but does not convert the texts to numbers. However, as you mentioned that I have to select “column to text”; so far I am unable to find that command from excel. I am afraid that it might cause some errors. Can you please tell me that from where can I get that command. I am using excel 2007.

Thursday, December 10, 2009 at 8:29 am

Data tab > Data Tools group (4th from left) > Text to Columns (first item in group).

I recorded a macro, cleaned it up, and here is the function that converts a range of text values to their numeric equivalents:

  This is how the function is called:

Wednesday, January 20, 2010 at 11:54 pm

I want to generate an order using conditional randbetween function in excel 2007. For example, I want to generate 2000 orders in between 1 to 730 (randbetween(1,730)). But each number betweeen 1 to 730 can’t be repeated more than three times withinin these 2000 orders. Is it possible? How can I write this function in excel?

Thursday, January 21, 2010 at 12:11 am

There are more effective and relevant places to ask for help. I suggest you read Finding Help for Microsoft Excel .

Shubha says

Saturday, July 31, 2010 at 5:36 pm

Great post ! Thank you. Helped me a lot

Monday, September 6, 2010 at 3:36 am

G8 …. superb … Thanx a lot …..

Alan Horsfield says

Saturday, January 15, 2011 at 4:39 am

I am using Excel 2003 and only a customised toolbar I call ledger. It contains a few of the most useful standard buttons and 10 of my own for specific actions on a ledger account. The special 10 buttons are all assigned to macros which can also be operated using a control command. Everything works fine BUT, if I move the file to another folder and in particular to a CDrom, the buttons no longer work until they are reassigned.

Is there an answer to this problem?

Saturday, January 15, 2011 at 9:31 am

Alan – Do all the buttons no longer work, or just the custom ones?

Saturday, January 15, 2011 at 12:44 pm

Thank you Jon for your interest. Yes, all the standard buttons in my “ledger” toolbar work but the custom ones do not. However, the commands still work via the control keys.

Sunday, January 16, 2011 at 8:38 am

Depending on how you’ve set up those buttons, they might be linked to the whole path of the workbook that contains the macro. Move the file, the workbook doesn’t exist at that path, nothing happens.

A long time ago I stopped using attached toolbars for this reason. I build the toolbar when the workbook opens, and destroy it when the workbook closes. I have some very rudimentary code showing this technique in this text file:

Where it says for example

.OnAction = “Macro1”

I usually change to this:

.OnAction = ThisWorkbook.Name & “!Macro1”

to make sure the macro points to the correct workbook.

Monday, January 17, 2011 at 9:39 am

Thank you again, Jon. I have adapted some code similar to that in your MenuBar.txt and it works well but it deletes my carefully crafted buttons and replaces them with various buttons from the msoControlButtons set which I do not want.

I happen to have a page of instructions in my Excel file with a list of cells containing the buttons. I can assign macros to them and they remain operational if I move the file to another folder. Is there a way of giving these buttons an “id” that can be used in place of “.FaceId” and, instead of using “Type:=msoControlButton”, use some other “Type:=”? These buttons have a name like “Picture6_Click” associated with them but I do not know what it means.

Incidentally, I have an Excel2007 version of my file called ledger.xlsm with a number of subs like:

Callback for customButton1 onAction Sub Macro1(control As IRibbonControl) Run (“Check_R”) End Sub

These enable me to replace the standard ribbon with my own containing my custom buttons plus useful standard ones, and the custom ribbon transports with the file. If I unzip the .xlsm file, there is a whole lot more information there including the .png files of the buttons. So am I asking too much of Excel2003!

Monday, January 17, 2011 at 10:11 am

Alan –

Since the button images reside as pictures in your sheet, copy them, then use .PasteFace to paste the image as the button image.

Tuesday, January 18, 2011 at 12:56 pm

Jon, Thanks to your suggestion to use .PasteFace everything is now

working nicely when copied to a CDrom, so I can give my file to a

friend without having to set up the assignments for him.

When I had all the code in ThisWorkbook, it did not like:

Set myControl = CommandBars(“Ledger”).Controls.Add

So I put the code for making the toolbar in a module as a subroutine

which included the same piece of code and called it up in

ThisWorkbook. It works nicely. Many thanks again. Alan

Anonymous says

Tuesday, June 7, 2011 at 11:54 am

Great info…..saved me a lot of time trying to go through Excel’s help menu and books. Thanks!!

Friday, August 19, 2011 at 4:01 am

Hello Jon I have written a macro in the sheet module. The macro has no parameters. When I try to assign macro to my shape the macro does not appear in the macro list. If I have a macro in a general module I see it. In this particular case I want to pick up a value from the sheet that is calling the macro which is why I put it in the sheet code area. Any ideas, please?

aaron edwards says

Friday, September 2, 2011 at 9:17 am

I often create simple drawings in Excel for the machhine shop to make “quickie” fixtures and illustarte ideas. In one drawing I wanted to animate the movment of a blade shearing off the tops of the balls in Ball Grid Array device to see how many voids were inside after solder reflow. I started a macro and recorded the object as I moved it first to the left and then the right. It looked awsome. I thought I had figured out away to animate my drawing!

No such luck. Even though the VBA code seemed to recorded all by keystrokes, the macro failed saying OBJECT DOES NOT SUPPORT THIS PROPERTY OR METHOD. RUN TIME ERROR 438.

Is there a way to do simple animation of drawing objects in Excel by using a macro?

Friday, September 2, 2011 at 10:07 am

Aaron –

Each drawing object (shape or group) has a .Left and .Top property, which you can vary within a timed VBA procedure.

I’ve covered animation with VBA in these articles: Gapminder for Excel Gapminder for Excel II Gas Prices – Animated Bar Chart for Excel Gas Prices – Animated Bar Chart for Excel 2 Ballistics Animation Rolling Wheel Animation

Friday, September 2, 2011 at 1:59 pm

Thanks Ron for the Cool Chart and data related links that animate the ballistic chart. I’ll surely use these ideas in future applications. But the problem of animating an actual drawn object across the crenn is slighly different. I managed to solve that problem by alternating the rmacro ecorded keystrokes to move the object and then selecting an empty cell, back and forth repeatedly. This worked perfectly when I ran the macro afterward. Once I saw the VBA code, I simply copied the number of intrations required for the full movements I desired.

Simple but effective.

meking says

Thursday, September 15, 2011 at 12:15 pm

I want to change the button image like we could in excel 2003. Looks like we can no longer do this, so all my macro buttons on my ribbon look the same and the only way to know which one is which is to float mouse over it. Or is there a way to draw your own image as before?

Thursday, September 15, 2011 at 1:15 pm

Meking –

You can add a button to the QAT, and while in the “Customize the QAT” dialog, select the new item, then click the Modify button, and you can choose from many button images.

If you want to use your own button image, you need to use code.

paulchiu says

Tuesday, January 3, 2012 at 11:08 am

mysteriously, a button to execute a macro inside an 2010 EXCEL workbook disappeared and the “insert” feature within the “Developer” tab is disabled. Any ideas to get this back for this workbook? The “insert” works for other workbooks.

Tuesday, January 3, 2012 at 12:00 pm

Paul –

Is the workbook or worksheet protected? Are multiple sheets selected? Is the workbook shared?

Tuesday, January 3, 2012 at 12:13 pm

The worksheet is on my local drive and only the sheet where button was located before it disappeared is selected. Not sure about the worksheet being protected.

Thanks, Paul

Tuesday, January 3, 2012 at 12:27 pm

Towards the right of the Home tab, click Format, and look for Protect Sheet or Unprotect Sheet

Tuesday, January 3, 2012 at 12:40 pm

I selected all the sheets and the Protect Sheet selection is faded out.

Tuesday, January 3, 2012 at 11:56 pm

If all sheets are selected, you cannot change the protection of the sheets.

Thursday, January 5, 2012 at 12:00 pm

i gave up. i am running the macros manually.

Mark T says

Wednesday, February 1, 2012 at 5:47 pm

Hi, Jon. I haven’t had much luck finding an answer to this question; maybe you can help.

I currently have a button set up in an Excel 2007 document (let’s call this Spreadsheet A) to execute a macro on our company network (stored in Spreadsheet B). In the “Macro Name” field, the full path of Spreadsheet B is defined alongside the Subroutine. The macro works fine when I leave Spreadsheet A in its original location. The problem arises when I copy & paste Spreadsheet A elsewhere… when I do this, Microsoft automatically resets the path of Spreadsheet B to match the new location of Spreadsheet A.

I do not want to do this. I just want to leave Spreadsheet B alone, and preserve the path within Spreadsheet A. (Interestingly, this path name is preserved when I do a “Save As”, but not when I copy & paste in Windows Explorer).

Do you have any suggestions as to how I might fix this problem? Is there a way to “lock” the path name in place?

Wednesday, February 1, 2012 at 8:32 pm

Mark –

Save the path and sub name in a cell or in a constant in your module. When the button is clicked, have it run this code:

Application.Run(strMacro)

strMacro is either the constant, or a string variable defined by:

strMacro = activesheet.range(cell address containing path/sub).value

Thursday, February 2, 2012 at 3:44 pm

OK, I just tried that… unfortunately, I got an error message telling me that “the macro may not be available in this Workbook, or all macros have been disabled”.

I don’t think this is a macro security issue, as this will work when accessed directly from the “Macro Name” text in the button. Are you still supposed to use an exclamation point between the path name and the macro name?

Friday, February 3, 2012 at 1:33 pm

I think when you use Application.Run, the workbook with the procedure in it has to be open.

Vladimir Niko says

Sunday, December 30, 2012 at 11:28 pm

Dear Sir! Please I need help to resolve problem with record macro in Excel 2007. First I record a simple macro in Excel 2003 and assign it with the shape button. Then I need record a new macro with one of step is use previous recorded assign button – just to pushed it in time of record new macro. In Excell 2003 it worked perfect. I use it many time. But in Excell 2007 it not let me do that. When I need push the previous recorded button during new record macro, it cannot be pushed. How I can made this button pushable during record new macro? Please I will be very appreciate for any help. Thank you.

Monday, December 31, 2012 at 10:17 am

Vladimir –

You want to have the macro click a button? Why not have it run the code that would run if the button were clicked?

Monday, December 31, 2012 at 10:18 am

You want the macro to click a button? Isn’t it easier to have the macro run the code that the button runs when clicked?

vladimir niko says

Monday, December 31, 2012 at 1:33 pm

Thank you for answer to my question. But you don’t understand. I don’t want to macro push the button. During of the new recording macro I need to use manually push the button which already was assigned to other macros. It is more easy to use many assigned macros button and not to found their code in the list of many macros. I do not know why Excel 2007 do not support this. Because in 2003 I did that many time to save the time. Maybe I need to change something in configuration? Thank you for your help.

Monday, December 31, 2012 at 3:58 pm

Dear Sir I try to explain what I need step by step: 1. I have button (A) assign with process macro (A). 2. I need record new macro (B). During the recording process macro (B) I need run process macro (A). It is easy just to click button (A) during recording macro (B), not to go to the list of macros and manualy put code assign with button (A). In Excel 2003 it is work all the time. But in Excel 2007 in process of recording macros all previous assign button not clickable. They clickable again only if I am stop recording macro. How I can made them clickable in process of recording macro, like in Excell 2003? Thank you very much for help.

Wednesday, January 2, 2013 at 9:41 am

Vladimir – I think the answer is “you can’t.” In any case, the code you recorded in 2003 looked something like this at the point where you clicked the button: Application.Run "Book1.xls!MyMacro" where a better way to call the macro (better in most cases for a number of reasons) is simply: MyMacro You’ll have to record your macro piece-wise, and assemble the parts around calls to the various other procedures you’ve written. The VB Editor’s IntelliSense will help select the appropriate procedure as you begin typing its name.

Thursday, January 3, 2013 at 1:54 am

Dear Jon! Thank you for your answer. Probably not always the new version better than the old. It was a very useful feature in Excel 2003. Now I forced to record large macros with clickable buttons in Excell 2003, and then convert the whole file to Excel 2007. It is not comfortable, but what can I do, I need to use this buttons during the process of recording macros. But any way I really appreciate you for your help. Thank you again.

Alan Butler says

Tuesday, June 25, 2013 at 2:23 pm

Jon, I created a spreadsheet using Excel 2003 that allows users to enter the # of rows they wanted to show, tab out of that cell, and click a button to run a macro to shrink or expand the # of rows showing. The macros also unlocked and locked the spreadsheet to allow them to run, print, save, etc. Everything has been working fine for the users for the last few years. Today I was contacted because the buttons that contained the text had shrunk from about 1/2″ x 1″ to 1/8″ x 1/4″ making it impossible to read the text in each button. These buttons have also shifted out of place on their original spreadsheet. I know this user is using Excel 2010 now, (as am I.) Each month when this user entered new information, they would Save As, thus keeping all their previous versions of the tracking/reporting form. I know that Excel 2010 has added new formats that create issues with workbooks that have macros, and from what I can tell, this user is still opening and saving this workbook in .xls format.

Do you know of anything that I can do to recover the original formatting that I had for this workbook? Or, a possible cause for the corruption of this copy that workbook? (My guess is that she will have to re-enter this data on an earlier copy that wasn’t corrupted. But, I would like to help her to keep from having this occur again.)

Thank you for your assistance! Alan

Thursday, July 4, 2013 at 12:54 pm

Alan – I don’t know how new formatting would interfere with workbooks containing macros. Old macros using old formatting syntax might not have the expected results. Also, continuing to use the .xls file format may result in unexpected file formatting. The problem with the shrunken buttons could be alleviated with code that resizes and repositions buttons when a sheet is activated.

Monday, July 8, 2013 at 9:35 am

Thank you Jon! I hadn’t even considered that as a possibility.

David Long says

Wednesday, July 24, 2013 at 11:47 am

My question involves how to handle shape macros that refer to a procedure stored in an Excel Add-In. I have created an add-in that assigns macros via the Shape.OnAction property. However, this appears to create a reference to the add-in on the local machine. This means that if a file is shared via email for example, the recipient has to manually point the reference to his/her add-in in order for everything to work as expected. Is there a common workaround to this type of problem that I have not been able to find yet? Thanks for taking the time to review my question, and I look forward to hearing your thoughts.

Wednesday, July 24, 2013 at 2:36 pm

This is also a problem if a worksheet uses a user defined function written in VBA in an add-in. If the path where the add-in is located is not consistent, then the links fail.

I avoid linking shapes and buttons in a worksheet to code in a different workbook. Instead, I add a button to a custom ribbon tab for the add-in. So if the add-in is absent, there is no button not pointing to it. You may want to add intelligence to the add-in’s code that hides the button if it’s not appropriate on the active sheet, and that exits the procedure if it’s not appropriate but the button still called it.

Friday, July 26, 2013 at 10:50 am

Thanks for the clarification and advice, and sorry in advance for the lengthy post. As you suggested, most of the add-in’s functions are contained in ribbon buttons; however, in my scenario I have created a template grid composed of a series of shapes (squares and small triangles) that users can click on to create basic flowcharts. I think from a user experience perspective I would still like to have the macros execute when a user clicks on the shape. I came across the Workbook LinkSources property and ChangeLink method and have tried to update the link to the currently running add-in when a workbook is opened. Basically, I update any existing link that references the name of the add-in running the code (ThisWorkbook.Name). However, I have realized that by the time the WorkbookOpen event fires users may already be presented with a warning message stating “This workbook contains links to other data sources,” but in the end it looks like even if the user chooses “Do not Update” the code I have in the WorkbookOpen event does successfully modify the data link to the add-in running the code using the ThisWorkbook object. I think that may have to suffice for what I am looking to do unless there are valid concerns/reservations with my approach. Note: I have a custom class cExcelEvents that I found on a website by Chip Pearson that allows my add-in to gain access to the WorkbookOpen event.

Tuesday, January 28, 2014 at 9:15 pm

Congrats on your excellent blog!

I have a simple question.

Is it possible to make a button disappear after being used?

button 1: today’s date button 2: January 01

It would be perfect to have both of them disappearing but just the one that’s clicked would also be nice… :)

I’m thinking that maybe an automatic shape insertion over the buttons might work… but I don’t know how to do this either!! :)

Grateful for your help. Cheers!

Wednesday, January 29, 2014 at 8:19 am

Arg – Application.Caller will return the name of the form button that called the procedure. This will make it disappear:

Wednesday, January 29, 2014 at 7:24 pm

Thank you so much for your quick reply!

In the meantime I found this solution:

Private Sub CommandButton1_Click() DataTermo31Dez2014_01 CommandButton1.Visible = False End Sub

This allows me to use the command button, run the macro I need (DataTermo31Dez2014_01) and then hide the button.

Don’t know your opinion on this but apparently it’s working alright. I just needed to build another macro to show the button while I’m still on testings.

The big question for me now is that my worksheet has 100 buttons and so I had to repeat all the procedures 100x!!!

This resulted in a long code but… As I’m totally ignorant about VBA I decided to just pat myself in the back for even getting it to work. :)

Is there a procedure to replicate “subs”? Something like “Sub my.Macro.01 + 1”?

Sorry if this sounds to ignorant…

Thanks again for your attention!! Cheers!

Wednesday, January 29, 2014 at 8:16 pm

Arg – Oh, so you’ve used the ActiveX buttons. Well, there’s copy-paste. For Form buttons you can assign them all to a single procedure:

Wednesday, January 29, 2014 at 8:28 pm

wow! You’re fast! :)

So there’s no difference in using ActiveX or Form buttons?

And would that code hide the buttons individually? Only after they’re clicked?

By the way, sorry to bother with this too but, is there a way to disable windows privacy warnings “this document contains macros bla-bla-bla…”?

Thank you so much!

Wednesday, January 29, 2014 at 8:47 pm

The two button types are different.

An ActiveX button called Button1 has a unique event procedure called Sub Button1_Click on the code module of its parent worksheet.

A Form button called Button 1 can be assigned to any procedure in a regular module, so multiple buttons can link to the same protocol. This is the example I used, and each button is only hidden after it is clicked.

There is no way in VBA to bypass security warnings. That would defeat the purpose of the security system. On your own computer, if you’re careful, you could select settings that allow any code to be enabled, or better, define certain directories as “safe”, so any code in them would be enabled.

Wednesday, January 29, 2014 at 8:54 pm

Ok! Makes sense.

I have no training in VBA but I’ve now realized that it’s the best way to get jobs done.

Truth is I’m getting fascinated by it’s potential, so, last question (I promise), where could find documents/videos/other to learn about VBA?

And we’re talking about the basics as all that I’ve accomplished so far was by investigating forums and using logic to adapt other codes to my needs.

Again, thank you so much and congratulations on your work!

Ermencarla says

Tuesday, January 10, 2017 at 4:53 am

In MS Word 2013 I have a custom tab created by clicking New Tab in the Customize the Ribbon window. I want to programmatically hide/show this custom tab (named Personal_2) by means of a button in another custom tab, named Personal_1. I know how to use the Custom UI Editor for Office and I am familiar with Word VBA code. I would prefer to operate with Normal.dotm rather than with a specific *.dotm. Can you please help me?

Sunday, February 26, 2017 at 7:10 pm

Hi guys Have a userform excel 2007. Can add shapes and code to a worksheet but I am struggling to find an answer to adding a shape to a userform that is relatively easy. Can anyone assist?

[…] Code to Excel Workbook Learn to debug: Debugging VBA How To: Assign a Macro to a Button or Shape How To: Assign a Macro to a Button or Shape | Peltier Tech Blog | Excel Charts User Form Creation Create an Excel UserForm When To Use a UserForm & What to Use a UserForm […]

[…] Excel's recorder Learn to debug: Debugging VBA How To: Assign a Macro to a Button or Shape How To: Assign a Macro to a Button or Shape | Peltier Tech Blog | Excel Charts User Form Creation Create an Excel UserForm When To Use a UserForm & What to Use a UserForm […]

[…] MS Excel: Cells Learn to debug: Debugging VBA How To: Assign a Macro to a Button or Shape How To: Assign a Macro to a Button or Shape | Peltier Tech Blog | Excel Charts User Form Creation Create an Excel UserForm When To Use a UserForm & What to Use a UserForm […]

[…] to debug: http://www.cpearson.com/excel/debug.htm How To: Assign a Macro to a Button or Shape https://peltiertech.com/how-to-assign-a-macro-to-a-button-or-shape/ User Form Creation http://www.contextures.com/xlUserForm01.html When To Use a UserForm & What […]

[…] would assign the macros to a button or a shape on the worksheet. See How To: Assign a Macro to a Button or Shape | Peltier Tech Blog | Excel Charts for […]

[…] Learn to debug: Debugging VBA How To: Assign a Macro to a Button or Shape How To: Assign a Macro to a Button or Shape | Peltier Tech Blog | Excel Charts User Form Creation Create an Excel UserForm When To Use a UserForm & What to Use a UserForm […]

[…] MS Excel: Cells Learn to debug: Debugging VBA How To: Assign a Macro to a Button or Shape How To: Assign a Macro to a Button or Shape – Peltier Tech Blog User Form Creation Create an Excel UserForm When To Use a UserForm & What to Use a UserForm […]

[…] MS Excel: Cells Learn to debug: Debugging VBA How To: Assign a Macro to a Button or Shape https://peltiertech.com/how-to-assign-a-macro-to-a-button-or-shape/ User Form Creation Create an Excel UserForm When To Use a UserForm & What to Use a UserForm […]

[…] MS Excel: Cells Learn to debug: Debugging VBA How To: Assign a Macro to a Button or Shape How To: Assign a Macro to a Button or Shape – Peltier Tech Blog User Form Creation http://www.contextures.com/xlUserForm01.html When To Use a UserForm & What […]

[…] smuller74, Thanks for the feedback. You are very welcome. Glad I could help. Can you also help me to add a button to start the macro? See the below links: How to insert Buttons, radio buttons and check boxes in Excel How to insert Buttons, radio buttons and check boxes in Excel – Bing Videos How To: Assign a Macro to a Button or Shape How To: Assign a Macro to a Button or Shape – Peltier Tech Blog […]

[…] the latest post, I showed how to assign a macro to a Forms menu control or other shape on a sheet. Macros can also […]

[…] Or you could assign the code to a button in the worksheet. […]

Leave a Reply

Your email address will not be published. Required fields are marked *

Notify me of follow-up comments by email.

Notify me of new posts by email.

This site uses Akismet to reduce spam. Learn how your comment data is processed .