Macro won't execute from a Command Button

[Dick Scheibe]

I have macro problem where I am changing the background color on a worksheet
that contains charts. If any chart or part of a chart is selected, the macro
won't run from a button. My macro first selects a worksheet and a range in
the worksheet, thus de-selecting any other selection (chart). It then
changes the color in a specified range. The macro code associated with
button only calls another macro routine and has no real executable associated
with it other than that. The macro runs from the macro editor perfectly,
either by running the click macro associated with the button or the macro
routine called by the button. It runs perfectly from the tools macro macro
run pull down as well. It won't run from the button - it starts and then
just stops without completing the routine. Is it a bug?

[Dave Peterson]

xl97???

If yes, rightclick on the button (while in design mode) and show the properties.

Scrolldown to the .takefocusonclick property and change it to false.

(It is a bug in xl97, but fixed in xl2k.)



**** Scheibe wrote:
>
> I have macro problem where I am changing the background color on a worksheet
> that contains charts. If any chart or part of a chart is selected, the macro
> won't run from a button. My macro first selects a worksheet and a range in
> the worksheet, thus de-selecting any other selection (chart). It then
> changes the color in a specified range. The macro code associated with
> button only calls another macro routine and has no real executable associated
> with it other than that. The macro runs from the macro editor perfectly,
> either by running the click macro associated with the button or the macro
> routine called by the button. It runs perfectly from the tools macro macro
> run pull down as well. It won't run from the button - it starts and then
> just stops without completing the routine. Is it a bug?

--

Dave Peterson

[Dick Scheibe]

Thanks but no cigar. I am running both Office 2002 and 2003 but I tried your
fix anyway. Neither seem to run the macro from the button. Any other ideas?

"Dave Peterson" wrote:

> xl97???
>
> If yes, rightclick on the button (while in design mode) and show the properties.
>
> Scrolldown to the .takefocusonclick property and change it to false.
>
> (It is a bug in xl97, but fixed in xl2k.)
>
>
>
> **** Scheibe wrote:
> >
> > I have macro problem where I am changing the background color on a worksheet
> > that contains charts. If any chart or part of a chart is selected, the macro
> > won't run from a button. My macro first selects a worksheet and a range in
> > the worksheet, thus de-selecting any other selection (chart). It then
> > changes the color in a specified range. The macro code associated with
> > button only calls another macro routine and has no real executable associated
> > with it other than that. The macro runs from the macro editor perfectly,
> > either by running the click macro associated with the button or the macro
> > routine called by the button. It runs perfectly from the tools macro macro
> > run pull down as well. It won't run from the button - it starts and then
> > just stops without completing the routine. Is it a bug?
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Maybe you should post the offending code.


**** Scheibe wrote:
>
> Thanks but no cigar. I am running both Office 2002 and 2003 but I tried your
> fix anyway. Neither seem to run the macro from the button. Any other ideas?
>
> "Dave Peterson" wrote:
>
> > xl97???
> >
> > If yes, rightclick on the button (while in design mode) and show the properties.
> >
> > Scrolldown to the .takefocusonclick property and change it to false.
> >
> > (It is a bug in xl97, but fixed in xl2k.)
> >
> >
> >
> > **** Scheibe wrote:
> > >
> > > I have macro problem where I am changing the background color on a worksheet
> > > that contains charts. If any chart or part of a chart is selected, the macro
> > > won't run from a button. My macro first selects a worksheet and a range in
> > > the worksheet, thus de-selecting any other selection (chart). It then
> > > changes the color in a specified range. The macro code associated with
> > > button only calls another macro routine and has no real executable associated
> > > with it other than that. The macro runs from the macro editor perfectly,
> > > either by running the click macro associated with the button or the macro
> > > routine called by the button. It runs perfectly from the tools macro macro
> > > run pull down as well. It won't run from the button - it starts and then
> > > just stops without completing the routine. Is it a bug?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[Dick Scheibe]

The code is as follows for both the code to change the background and the
associated macro:

Sub PlotsSolidBack()
Sheets("Plot").Select
Range("a1").Select
Range("PlotBackgroundArea").Select
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
PlotSheetHome
End Sub

Sub PlotsBackgroundButton_Click()
PlotsSolidBack
End Sub

Please remember that a chart may or may not be selected when this is
executed - it does always work when the button is not used or a chart is not
selected. The purpose of the macro is to allow use by anyone. There is also
another macro that changes the background color to none that has the same
issues. PlotsSheet Home is a macro that just scrolls to the top of the sheet.
Thanks

"Dave Peterson" wrote:

> Maybe you should post the offending code.
>
>
> **** Scheibe wrote:
> >
> > Thanks but no cigar. I am running both Office 2002 and 2003 but I tried your
> > fix anyway. Neither seem to run the macro from the button. Any other ideas?
> >
> > "Dave Peterson" wrote:
> >
> > > xl97???
> > >
> > > If yes, rightclick on the button (while in design mode) and show the properties.
> > >
> > > Scrolldown to the .takefocusonclick property and change it to false.
> > >
> > > (It is a bug in xl97, but fixed in xl2k.)
> > >
> > >
> > >
> > > **** Scheibe wrote:
> > > >
> > > > I have macro problem where I am changing the background color on a worksheet
> > > > that contains charts. If any chart or part of a chart is selected, the macro
> > > > won't run from a button. My macro first selects a worksheet and a range in
> > > > the worksheet, thus de-selecting any other selection (chart). It then
> > > > changes the color in a specified range. The macro code associated with
> > > > button only calls another macro routine and has no real executable associated
> > > > with it other than that. The macro runs from the macro editor perfectly,
> > > > either by running the click macro associated with the button or the macro
> > > > routine called by the button. It runs perfectly from the tools macro macro
> > > > run pull down as well. It won't run from the button - it starts and then
> > > > just stops without completing the routine. Is it a bug?
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Norman Jones]

Hi ****,

Your code ran without problem for me, either manually or from a command
button.

I could reproduce the button inactivity, however, if I put the
PlotsBackgroundButton_Click code in a standard module instead of the
relevant sheet code module.

Incidentally, none of your selections is necessary and so your
PlotsSolidBack sub could simplify to:

Sub PlotsSolidBack()
With Sheets("Plot").Range("PlotBackgroundArea").Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
PlotSheetHome
End Sub


---
Regards,
Norman



"**** Scheibe" <[email protected]> wrote in message
news:[email protected]...
> The code is as follows for both the code to change the background and the
> associated macro:
>
> Sub PlotsSolidBack()
> Sheets("Plot").Select
> Range("a1").Select
> Range("PlotBackgroundArea").Select
> With Selection.Interior
> .ColorIndex = 41
> .Pattern = xlSolid
> End With
> PlotSheetHome
> End Sub
>
> Sub PlotsBackgroundButton_Click()
> PlotsSolidBack
> End Sub
>
> Please remember that a chart may or may not be selected when this is
> executed - it does always work when the button is not used or a chart is
> not
> selected. The purpose of the macro is to allow use by anyone. There is
> also
> another macro that changes the background color to none that has the same
> issues. PlotsSheet Home is a macro that just scrolls to the top of the
> sheet.
> Thanks
>
> "Dave Peterson" wrote:
>
>> Maybe you should post the offending code.
>>
>>
>> **** Scheibe wrote:
>> >
>> > Thanks but no cigar. I am running both Office 2002 and 2003 but I
>> > tried your
>> > fix anyway. Neither seem to run the macro from the button. Any other
>> > ideas?
>> >
>> > "Dave Peterson" wrote:
>> >
>> > > xl97???
>> > >
>> > > If yes, rightclick on the button (while in design mode) and show the
>> > > properties.
>> > >
>> > > Scrolldown to the .takefocusonclick property and change it to false.
>> > >
>> > > (It is a bug in xl97, but fixed in xl2k.)
>> > >
>> > >
>> > >
>> > > **** Scheibe wrote:
>> > > >
>> > > > I have macro problem where I am changing the background color on a
>> > > > worksheet
>> > > > that contains charts. If any chart or part of a chart is selected,
>> > > > the macro
>> > > > won't run from a button. My macro first selects a worksheet and a
>> > > > range in
>> > > > the worksheet, thus de-selecting any other selection (chart). It
>> > > > then
>> > > > changes the color in a specified range. The macro code associated
>> > > > with
>> > > > button only calls another macro routine and has no real executable
>> > > > associated
>> > > > with it other than that. The macro runs from the macro editor
>> > > > perfectly,
>> > > > either by running the click macro associated with the button or the
>> > > > macro
>> > > > routine called by the button. It runs perfectly from the tools
>> > > > macro macro
>> > > > run pull down as well. It won't run from the button - it starts
>> > > > and then
>> > > > just stops without completing the routine. Is it a bug?
>> > >
>> > > --
>> > >
>> > > Dave Peterson
>> > >
>>
>> --
>>
>> Dave Peterson
>>

[Dick Scheibe]

Thanks Norman, but it doesn't run for me if any Chart or Part of a Chart,
e.g. an axis, is already selected when the button is pushed. The deselection
of objects on the sheet seems to be the issue but the code runs fine when any
of the routines are run from the editor or pull down menu (even with a chart
selected). The command button is in the relevant sheet code module and the
called routine is in a standard module. The selection of a range was an
attempt to fix the problem in that the selection deselects any chart that may
be selected, however to no avail. Your code is more concise but the problem
remains.
****

"Norman Jones" wrote:

> Hi ****,
>
> Your code ran without problem for me, either manually or from a command
> button.
>
> I could reproduce the button inactivity, however, if I put the
> PlotsBackgroundButton_Click code in a standard module instead of the
> relevant sheet code module.
>
> Incidentally, none of your selections is necessary and so your
> PlotsSolidBack sub could simplify to:
>
> Sub PlotsSolidBack()
> With Sheets("Plot").Range("PlotBackgroundArea").Interior
> .ColorIndex = 41
> .Pattern = xlSolid
> End With
> PlotSheetHome
> End Sub
>
>
> ---
> Regards,
> Norman
>
>
>
> "**** Scheibe" <[email protected]> wrote in message
> news:[email protected]...
> > The code is as follows for both the code to change the background and the
> > associated macro:
> >
> > Sub PlotsSolidBack()
> > Sheets("Plot").Select
> > Range("a1").Select
> > Range("PlotBackgroundArea").Select
> > With Selection.Interior
> > .ColorIndex = 41
> > .Pattern = xlSolid
> > End With
> > PlotSheetHome
> > End Sub
> >
> > Sub PlotsBackgroundButton_Click()
> > PlotsSolidBack
> > End Sub
> >
> > Please remember that a chart may or may not be selected when this is
> > executed - it does always work when the button is not used or a chart is
> > not
> > selected. The purpose of the macro is to allow use by anyone. There is
> > also
> > another macro that changes the background color to none that has the same
> > issues. PlotsSheet Home is a macro that just scrolls to the top of the
> > sheet.
> > Thanks
> >
> > "Dave Peterson" wrote:
> >
> >> Maybe you should post the offending code.
> >>
> >>
> >> **** Scheibe wrote:
> >> >
> >> > Thanks but no cigar. I am running both Office 2002 and 2003 but I
> >> > tried your
> >> > fix anyway. Neither seem to run the macro from the button. Any other
> >> > ideas?
> >> >
> >> > "Dave Peterson" wrote:
> >> >
> >> > > xl97???
> >> > >
> >> > > If yes, rightclick on the button (while in design mode) and show the
> >> > > properties.
> >> > >
> >> > > Scrolldown to the .takefocusonclick property and change it to false.
> >> > >
> >> > > (It is a bug in xl97, but fixed in xl2k.)
> >> > >
> >> > >
> >> > >
> >> > > **** Scheibe wrote:
> >> > > >
> >> > > > I have macro problem where I am changing the background color on a
> >> > > > worksheet
> >> > > > that contains charts. If any chart or part of a chart is selected,
> >> > > > the macro
> >> > > > won't run from a button. My macro first selects a worksheet and a
> >> > > > range in
> >> > > > the worksheet, thus de-selecting any other selection (chart). It
> >> > > > then
> >> > > > changes the color in a specified range. The macro code associated
> >> > > > with
> >> > > > button only calls another macro routine and has no real executable
> >> > > > associated
> >> > > > with it other than that. The macro runs from the macro editor
> >> > > > perfectly,
> >> > > > either by running the click macro associated with the button or the
> >> > > > macro
> >> > > > routine called by the button. It runs perfectly from the tools
> >> > > > macro macro
> >> > > > run pull down as well. It won't run from the button - it starts
> >> > > > and then
> >> > > > just stops without completing the routine. Is it a bug?
> >> > >
> >> > > --
> >> > >
> >> > > Dave Peterson
> >> > >
> >>
> >> --
> >>
> >> Dave Peterson
> >>
>
>
>

[Norman Jones]

Hi ****,

I added some charts to the button sheet. I then repeatedly selected
different chart elements, each time attempting to run your code via the
command button. The button code only failed to run for me if I had a chart
format dialog open.

As your problem only manifests itself with charts present, if nobody else
intervenes with more constructive assistance, perhaps you could also ask in
the microsoft.public.excel.charting group.


---
Regards,
Norman



"**** Scheibe" <[email protected]> wrote in message
news:[email protected]...
> Thanks Norman, but it doesn't run for me if any Chart or Part of a Chart,
> e.g. an axis, is already selected when the button is pushed. The
> deselection
> of objects on the sheet seems to be the issue but the code runs fine when
> any
> of the routines are run from the editor or pull down menu (even with a
> chart
> selected). The command button is in the relevant sheet code module and
> the
> called routine is in a standard module. The selection of a range was an
> attempt to fix the problem in that the selection deselects any chart that
> may
> be selected, however to no avail. Your code is more concise but the
> problem
> remains.
> ****
>
> "Norman Jones" wrote:
>
>> Hi ****,
>>
>> Your code ran without problem for me, either manually or from a command
>> button.
>>
>> I could reproduce the button inactivity, however, if I put the
>> PlotsBackgroundButton_Click code in a standard module instead of the
>> relevant sheet code module.
>>
>> Incidentally, none of your selections is necessary and so your
>> PlotsSolidBack sub could simplify to:
>>
>> Sub PlotsSolidBack()
>> With Sheets("Plot").Range("PlotBackgroundArea").Interior
>> .ColorIndex = 41
>> .Pattern = xlSolid
>> End With
>> PlotSheetHome
>> End Sub
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "**** Scheibe" <[email protected]> wrote in message
>> news:[email protected]...
>> > The code is as follows for both the code to change the background and
>> > the
>> > associated macro:
>> >
>> > Sub PlotsSolidBack()
>> > Sheets("Plot").Select
>> > Range("a1").Select
>> > Range("PlotBackgroundArea").Select
>> > With Selection.Interior
>> > .ColorIndex = 41
>> > .Pattern = xlSolid
>> > End With
>> > PlotSheetHome
>> > End Sub
>> >
>> > Sub PlotsBackgroundButton_Click()
>> > PlotsSolidBack
>> > End Sub
>> >
>> > Please remember that a chart may or may not be selected when this is
>> > executed - it does always work when the button is not used or a chart
>> > is
>> > not
>> > selected. The purpose of the macro is to allow use by anyone. There
>> > is
>> > also
>> > another macro that changes the background color to none that has the
>> > same
>> > issues. PlotsSheet Home is a macro that just scrolls to the top of the
>> > sheet.
>> > Thanks
>> >
>> > "Dave Peterson" wrote:
>> >
>> >> Maybe you should post the offending code.
>> >>
>> >>
>> >> **** Scheibe wrote:
>> >> >
>> >> > Thanks but no cigar. I am running both Office 2002 and 2003 but I
>> >> > tried your
>> >> > fix anyway. Neither seem to run the macro from the button. Any
>> >> > other
>> >> > ideas?
>> >> >
>> >> > "Dave Peterson" wrote:
>> >> >
>> >> > > xl97???
>> >> > >
>> >> > > If yes, rightclick on the button (while in design mode) and show
>> >> > > the
>> >> > > properties.
>> >> > >
>> >> > > Scrolldown to the .takefocusonclick property and change it to
>> >> > > false.
>> >> > >
>> >> > > (It is a bug in xl97, but fixed in xl2k.)
>> >> > >
>> >> > >
>> >> > >
>> >> > > **** Scheibe wrote:
>> >> > > >
>> >> > > > I have macro problem where I am changing the background color on
>> >> > > > a
>> >> > > > worksheet
>> >> > > > that contains charts. If any chart or part of a chart is
>> >> > > > selected,
>> >> > > > the macro
>> >> > > > won't run from a button. My macro first selects a worksheet and
>> >> > > > a
>> >> > > > range in
>> >> > > > the worksheet, thus de-selecting any other selection (chart).
>> >> > > > It
>> >> > > > then
>> >> > > > changes the color in a specified range. The macro code
>> >> > > > associated
>> >> > > > with
>> >> > > > button only calls another macro routine and has no real
>> >> > > > executable
>> >> > > > associated
>> >> > > > with it other than that. The macro runs from the macro editor
>> >> > > > perfectly,
>> >> > > > either by running the click macro associated with the button or
>> >> > > > the
>> >> > > > macro
>> >> > > > routine called by the button. It runs perfectly from the tools
>> >> > > > macro macro
>> >> > > > run pull down as well. It won't run from the button - it starts
>> >> > > > and then
>> >> > > > just stops without completing the routine. Is it a bug?
>> >> > >
>> >> > > --
>> >> > >
>> >> > > Dave Peterson
>> >> > >
>> >>
>> >> --
>> >>
>> >> Dave Peterson
>> >>
>>
>>
>>

[Dick Scheibe]

Thank all of you who have responded and helped. I have isolated the problem
and I think it is a bug in Excel. If the buttons are located above an
horizontal freeze line (in my case the sheet was frozen below line 2) the
buttons won't work if a chart is selected. If there is no freeze or they are
below the freeze pane line they work just fine. So the problem is caused by
the location of the command button on the worksheet when the sheet has frozen
rows. This is a new anomaly for me and is likely caused by a combination of
the fact the chart is selected and the button are above the row freeze.
Hope this helps others as well.
****

"**** Scheibe" wrote:

> Thanks Norman, but it doesn't run for me if any Chart or Part of a Chart,
> e.g. an axis, is already selected when the button is pushed. The deselection
> of objects on the sheet seems to be the issue but the code runs fine when any
> of the routines are run from the editor or pull down menu (even with a chart
> selected). The command button is in the relevant sheet code module and the
> called routine is in a standard module. The selection of a range was an
> attempt to fix the problem in that the selection deselects any chart that may
> be selected, however to no avail. Your code is more concise but the problem
> remains.
> ****
>
> "Norman Jones" wrote:
>
> > Hi ****,
> >
> > Your code ran without problem for me, either manually or from a command
> > button.
> >
> > I could reproduce the button inactivity, however, if I put the
> > PlotsBackgroundButton_Click code in a standard module instead of the
> > relevant sheet code module.
> >
> > Incidentally, none of your selections is necessary and so your
> > PlotsSolidBack sub could simplify to:
> >
> > Sub PlotsSolidBack()
> > With Sheets("Plot").Range("PlotBackgroundArea").Interior
> > .ColorIndex = 41
> > .Pattern = xlSolid
> > End With
> > PlotSheetHome
> > End Sub
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "**** Scheibe" <[email protected]> wrote in message
> > news:[email protected]...
> > > The code is as follows for both the code to change the background and the
> > > associated macro:
> > >
> > > Sub PlotsSolidBack()
> > > Sheets("Plot").Select
> > > Range("a1").Select
> > > Range("PlotBackgroundArea").Select
> > > With Selection.Interior
> > > .ColorIndex = 41
> > > .Pattern = xlSolid
> > > End With
> > > PlotSheetHome
> > > End Sub
> > >
> > > Sub PlotsBackgroundButton_Click()
> > > PlotsSolidBack
> > > End Sub
> > >
> > > Please remember that a chart may or may not be selected when this is
> > > executed - it does always work when the button is not used or a chart is
> > > not
> > > selected. The purpose of the macro is to allow use by anyone. There is
> > > also
> > > another macro that changes the background color to none that has the same
> > > issues. PlotsSheet Home is a macro that just scrolls to the top of the
> > > sheet.
> > > Thanks
> > >
> > > "Dave Peterson" wrote:
> > >
> > >> Maybe you should post the offending code.
> > >>
> > >>
> > >> **** Scheibe wrote:
> > >> >
> > >> > Thanks but no cigar. I am running both Office 2002 and 2003 but I
> > >> > tried your
> > >> > fix anyway. Neither seem to run the macro from the button. Any other
> > >> > ideas?
> > >> >
> > >> > "Dave Peterson" wrote:
> > >> >
> > >> > > xl97???
> > >> > >
> > >> > > If yes, rightclick on the button (while in design mode) and show the
> > >> > > properties.
> > >> > >
> > >> > > Scrolldown to the .takefocusonclick property and change it to false.
> > >> > >
> > >> > > (It is a bug in xl97, but fixed in xl2k.)
> > >> > >
> > >> > >
> > >> > >
> > >> > > **** Scheibe wrote:
> > >> > > >
> > >> > > > I have macro problem where I am changing the background color on a
> > >> > > > worksheet
> > >> > > > that contains charts. If any chart or part of a chart is selected,
> > >> > > > the macro
> > >> > > > won't run from a button. My macro first selects a worksheet and a
> > >> > > > range in
> > >> > > > the worksheet, thus de-selecting any other selection (chart). It
> > >> > > > then
> > >> > > > changes the color in a specified range. The macro code associated
> > >> > > > with
> > >> > > > button only calls another macro routine and has no real executable
> > >> > > > associated
> > >> > > > with it other than that. The macro runs from the macro editor
> > >> > > > perfectly,
> > >> > > > either by running the click macro associated with the button or the
> > >> > > > macro
> > >> > > > routine called by the button. It runs perfectly from the tools
> > >> > > > macro macro
> > >> > > > run pull down as well. It won't run from the button - it starts
> > >> > > > and then
> > >> > > > just stops without completing the routine. Is it a bug?
> > >> > >
> > >> > > --
> > >> > >
> > >> > > Dave Peterson
> > >> > >
> > >>
> > >> --
> > >>
> > >> Dave Peterson
> > >>
> >
> >
> >