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 wrote:

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