Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to assign macros to a new button in the current worksheet
How can I get the vba macros I've written to show up in the "Assign Macro"
windows when I open it to assign a maro to a button on the worksheet I am building? This never used to be a problem. It is probably related to all the security crap one has to deal with now. Macro "Security" in OFFICE has been set to Low. The steps are this: 1. I have about 7 macros in "Sheet 1" of the spreadsheet I currently have open in Excel 2003. I can see all 15 macros in the "VBA Project" window of the VBA Editor, including one new one I have just written. I can also see 6 other macros I've previously written in VBA code and which work fine. 2. Sometime ago, I created six buttons on the spreadsheet and attached these macros to them. These six buttons work fine (the macros do what they are supposed to do). 3. Now I wnat to add one more macro. It is a carbon copy of the others except I want to attach it to a new button, so new code needs to be written for this button. I simply copied the code for one of the existing buttons (e.g., HideF1_Click) within the code window and renamed it (e.g. to HideBY_Click). 4. Then, I created a new button on the spreadsheet using the Forms Toolbar. I clicked on the "button" image on the Toolbar and then moved the cursor to where I wanted the button and drew it. 5. Fine, the button was created and the Assign Macro window opened but NO MACROS show up in it. 6. Why not? 7. "Sheet1" in the VBA Project window where all the macros are (including the new one) is seelected. I can see all seven macros in the VBA Project window but none show up in the "Assign Macro" window. So the problem is that the code is written and available in "Sheet1" of the current workbook and the button where the code should be assigned is there, but the code is not connected to the button. John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to assign macros to a new button in the current worksheet
In Excel, ToolsMacroMacrosOptions will bring up a window where you can
assign keyboard shorycut keys to macros. To assign them to a button: 1- Buttons from the Forms Toolbar - Right Click and from the pop up menu, select "Assign Macro". A dialog box will open to accomplish the assignment. The macro will run when the button is clicked. The code for this type button resides in the public module and uses standard macro code. 2- Buttons from the Control Toolbox - You must be in design mode. To enter design mode, press Alt + F11, click on the design Icon (Ruler, Triangle, Pencil). When the icon is highlighted it is in design mode. Right click the button and select "View Code" from the pop up menu. Paste your macro between the title line and End Sub line that VBA alutomatically provides for the CommandButton1_Click event. Remove your old title line if one exists and make sure you have only one End Sub line. The macro will now run when the button is clicked. The code for this type button resides in the object that contains the button (i.e. Sheet or Form) and must use the prescribed event title line or it will not execute. "JWirt" wrote in message ... How can I get the vba macros I've written to show up in the "Assign Macro" windows when I open it to assign a maro to a button on the worksheet I am building? This never used to be a problem. It is probably related to all the security crap one has to deal with now. Macro "Security" in OFFICE has been set to Low. The steps are this: 1. I have about 7 macros in "Sheet 1" of the spreadsheet I currently have open in Excel 2003. I can see all 15 macros in the "VBA Project" window of the VBA Editor, including one new one I have just written. I can also see 6 other macros I've previously written in VBA code and which work fine. 2. Sometime ago, I created six buttons on the spreadsheet and attached these macros to them. These six buttons work fine (the macros do what they are supposed to do). 3. Now I wnat to add one more macro. It is a carbon copy of the others except I want to attach it to a new button, so new code needs to be written for this button. I simply copied the code for one of the existing buttons (e.g., HideF1_Click) within the code window and renamed it (e.g. to HideBY_Click). 4. Then, I created a new button on the spreadsheet using the Forms Toolbar. I clicked on the "button" image on the Toolbar and then moved the cursor to where I wanted the button and drew it. 5. Fine, the button was created and the Assign Macro window opened but NO MACROS show up in it. 6. Why not? 7. "Sheet1" in the VBA Project window where all the macros are (including the new one) is seelected. I can see all seven macros in the VBA Project window but none show up in the "Assign Macro" window. So the problem is that the code is written and available in "Sheet1" of the current workbook and the button where the code should be assigned is there, but the code is not connected to the button. John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assign different macros for True/False button | Excel Discussion (Misc queries) | |||
Place a button on a worksheet and assign a macro to it? | New Users to Excel | |||
Protecting a worksheet which has button controlled macros | Excel Programming | |||
Protecting a worksheet which has button controlled macros | Excel Discussion (Misc queries) | |||
Unable to Assign Value To Cell | Excel Programming |