Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
losing command button functionality
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
losing command button functionality
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
losing command button functionality
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? 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 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
losing command button functionality
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 wrote: 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? 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 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
command button add another command | Excel Discussion (Misc queries) | |||
Command Button Help | Excel Discussion (Misc queries) | |||
Losing Add-Inn Functionality | Excel Discussion (Misc queries) | |||
Command Button | New Users to Excel | |||
Losing scroll functionality - weird | Excel Discussion (Misc queries) |