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.
Sub HelloWorld() MsgBox "Hello, World! ", vbExclamation End Sub
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.
Shapes
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.
Tony says
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
Tony – Good point. One of these days I’ll blog about macro security.
Andras says
John,
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
Jon Peltier says
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
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?
Sergey
Jon Peltier says
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
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
Jon Peltier says
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.
Victor says
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 :).
Jon Peltier says
Intriguing. In Excel 2003, I created a column chart and wrote this code:
Sub Sample() If ActiveChart Is Nothing Then MsgBox "Select a chart" Else ActiveChart.ChartType = xlLine End If End SubThen 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
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.
Jon Peltier says
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.
Mei says
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
Jon Peltier says
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.
Mei says
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.
Jon Peltier says
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.
Mei says
Thanks Jon, will try it out and share any further problems if I encounter them.
Cheers.
Razib says
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.
Razib says
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.
Jon Peltier says
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.
razib says
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.
Jon Peltier says
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.
Razib says
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.
Jon Peltier says
Razib –
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:
Function ConvertTextToNumbers(rng As Range) Dim col As Range For Each col In rng.Columns col.TextToColumns Destination:=col, DataType:=xlDelimited Next End FunctionThis is how the function is called:
Razib says
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?
Jon Peltier says
Razib –
There are more effective and relevant places to ask for help. I suggest you read Finding Help for Microsoft Excel.
Shubha says
Great post ! Thank you. Helped me a lot
Anup says
G8 …. superb … Thanx a lot …..
Alan Horsfield says
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?
Jon Peltier says
Alan – Do all the buttons no longer work, or just the custom ones?
Alan Horsfield says
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.
Jon Peltier says
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.
Alan Horsfield says
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!
Jon Peltier says
Alan –
Since the button images reside as pictures in your sheet, copy them, then use .PasteFace to paste the image as the button image.
Alan Horsfield says
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
Great info…..saved me a lot of time trying to go through Excel’s help menu and books. Thanks!!
Chris says
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
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?
Jon Peltier says
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
aaron edwards says
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
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?
Jon Peltier says
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
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.
Thanks!
Paul
Jon Peltier says
Paul –
Is the workbook or worksheet protected? Are multiple sheets selected? Is the workbook shared?
paulchiu says
Jon,
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
Jon Peltier says
Towards the right of the Home tab, click Format, and look for Protect Sheet or Unprotect Sheet
paulchiu says
I selected all the sheets and the Protect Sheet selection is faded out.
Jon Peltier says
If all sheets are selected, you cannot change the protection of the sheets.
paulchiu says
i gave up.
i am running the macros manually.
Mark T says
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?
Jon Peltier says
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
Mark T says
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?
Jon Peltier says
Mark –
I think when you use Application.Run, the workbook with the procedure in it has to be open.
Vladimir Niko says
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.
Jon Peltier says
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?
Jon Peltier says
Vladimir –
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
Dear Sir!
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.
Vladimir Niko says
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.
Jon Peltier says
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:
MyMacroYou’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.
Vladimir Niko says
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
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
Jon Peltier says
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.
Alan Butler says
Thank you Jon! I hadn’t even considered that as a possibility.
David Long says
Jon,
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.
Jon Peltier says
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.
David Long says
Jon,
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.
Arg says
Hello Jon!
Congrats on your excellent blog!
I have a simple question.
Is it possible to make a button disappear after being used?
Example:
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!
Jon Peltier says
Arg –
Application.Caller will return the name of the form button that called the procedure. This will make it disappear:
Arg says
Hi Jon!
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!
Jon Peltier says
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:
Arg says
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!
Jon Peltier says
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.
Arg says
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
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?
Greg says
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?