losing command button functionality

[smokeyspal]

Hi, my question is this:

I recorded a few macros for clearing the contents in ranges of cells.
I gave the ranges names, ie. "cellstoclearplow" These macros perform
fine.

Next, I placed Command Buttons from the Controls toolbox (not Forms
toolbox) on the sheet. I followed the "view code" link on their right-
click menus, and inserted the macro names into their respective VBA
scripts. The macros fired flawlessly now when clicking on the command
buttons.

The problem occurs when I close the worksheet, then re-open: Most,
but not all, of the Command buttons:

1. No longer fire their assigned macros, EVEN THOUGH all the code is
visible and unchanged in the VBA editor

2. No longer behave like Controls, but instead behave like 'pictures'
- the right-click context menu is the one I would get if I had used
Command Buttons from the Forms toolbox instead of from Controls
Toolbox - (and, by the way, this same loss of function occurs even if
I start out with Forms Command Buttons; that is, they will lose their
association with the macros I assigned once I close and reopen the
sheet; and yes, I'm careful to save the file before closing)

I know about being in and out of Design Mode, so that is not
interfering
I know about unprotecting the sheet, so that is not an issue

My hunch is that in the VBA code I need to associate the Command
Buttons and/or macros with active sheet. Does this sound plausible?
Here is an example of what I've got:

First the 3 declared macros:

Sub ClearPlowCells()
' ClearPlowCells Macro
' Macro recorded 10/21/2009 by James J

Application.Goto Reference:="cellstoclearplow"
Selection.ClearContents
End Sub

Sub clearshovelcells()
' clearshovelcells Macro
' Macro recorded 10/21/2009 by James J

Application.Goto Reference:="cellstoclearshovel"
Selection.ClearContents
End Sub

Sub ClearMeltCells()
Application.Goto Reference:="cellstoclearmelt"
Selection.ClearContents
End Sub

Now here are the associated Command Buttons:

Private Sub CommandButton2_Click()
ClearPlowCells
End Sub

Private Sub CommandButton3_Click()
clearshovelcells
End Sub

Private Sub CommandButton4_Click()
ClearMeltCells
End Sub

 

[Dave Peterson]

What version of excel are you using?

How are you saving the file (what format--a normal workbook)????

Are you enabling macros when you open the workbook?

 

[smokeyspal]

What version of excel are you using?

How are you saving the file (what format--a normal workbook)????

Are you enabling macros when you open the workbook?

Thank you dave,
The basic workbook was created in 2007 and the cells laid out;
however it is running now on 2003. The macros and controls have been
created in 2003.

I saved it as "macro-enabled", though I just see now in the Save As
options it is calling it an "excel 2007 macro-enabled file", even
though this is a 2003 version. Perhaps that is an issue.

I had set security setting to lowest so as to enable macros at
opening, but I will hunt for other settings that may be preventing
macro enablement upon opening.

Ultimately I hope to save this as a template with protections so that
others can simply enter time and quantity data.

 

[Dave Peterson]

Do you still have a working copy of the .xlsm file (macro enabled in xl2007)?

If yes, try saving a copy as a xl97-xl2003 version.

Then you can test that copy using xl2003.

 

[smokeyspal]

Do you still have a working copy of the .xlsm file (macro enabled in xl2007)?

If yes, try saving a copy as a xl97-xl2003 version.

Then you can test that copy using xl2003.



smokeyspalwrote:

Thank you Dave, I'll give it a try.