Can't Select Macro Name for Button

Hello,

I am trying to assign a macro to a button. After clicking “Assign Macro,” the name that appears in the Macro Name field is the name of the document that I’m adding the button to. I then select the macro name I want to assign to the button. This name now appears in the Macro Name field, and I click OK. The trouble I’m running into is that the macro name does not save because when I reopen the Assign Macro box again the file name appears in the Macro Name field again, not the macro name I just selected. Does anyone know why this is happening? Thank you!

.
.
It sounds like you might not be assigning the macro properly ?

Go to the DEVELOPER TAB / INSERT ... click it.

In the upper left hand corner is the graphic for a Command Button ... click it.

Place your cursor on the sheet approximately where you want the button positioned (you can move it late if necessary).

Left click the mouse. The button is on the sheet and another window opens with macro names showing.

Left click the macro name you want .. Click OK.

Done.


When you return to the Macro Assign window, the macro name appearing in the Macro Name Field might not necessarily be the macro you expect. If your macro was assigned to the button once,
it will stay that way until you change it.

If the assigned macro is truly changing without your help, you either have a corrupted workbook or a bad install of Excel that needs repair ?

I am a beginner so it is very possible that I was not assigning the macro properly. I followed your steps (for which I thank you for being so thorough), and still the macro name did not appear in the Macro Name field. Taking your suggestion that the file may be corrupted, I created a new Excel file, followed your steps, and this time the macro name assigned did not change, so it would seem that the file is indeed corrupted. Big bummer on the one hand, but on the other hand at least I now know what the problem is. Thank you very much for your help . . . . it is much appreciated!

Before you start over ... if your Macro Name begins with Private Sub bla bla bla ()

the word PRIVATE will prevent the macro from being seen in the Macro List.

If you remove the PRIVATE from the beginning of the macro name, it will show in the list.


To keep the macro private and still run it, if your button is named CommandButton1 ... you would need to change your macro name to :

Private Sub CommandButton1_Click()
'your code
End Sub

If you've given the button a specific name ...

Private Sub YourButtonNameHere_Click ()
'your code
End Sub


Hope that makes sense.


ps: The purpose of using PRIVATE at the beginning of a Sub is to insure someone who likes to snoop around and mess things up, can't find the sub. It's hidden / Private from their view.
If they know anything about Excel and VBA, using Private won't stop them.

Thank you. Off hand I don't remember if PRIVATE is in the code, but I will check tomorrow when I return to work. Here's to keeping my fingers crossed. Your directions do make sense and, if anything, may come in handy for another time. Again, thank you for your time . . . still appreciating your thoroughness!

Ok. Here's to hoping for the best !