Unable to assign macros to a new button in the current worksheet

[JWirt]

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

 

[JLGWhiz]

In Excel, Tools>Macro>Macros>Options 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.