losing command button functionality
On Oct 22, 8:27*pm, Dave Peterson wrote:
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:
On Oct 22, 12:57 pm, Dave Peterson wrote:
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?
smokeyspalwrote:
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
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
Thank you Dave, I'll give it a try.
|