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?

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.)



Dick 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

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.)



Dick 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

Maybe you should post the offending code.


Dick 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.)



Dick 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

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.


Dick 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.)



Dick 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

Hi Dick,

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").Interio r
.ColorIndex = 41
.Pattern = xlSolid
End With
PlotSheetHome
End Sub


---
Regards,
Norman



"Dick Scheibe" wrote in message
...
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.


Dick 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.)



Dick 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

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.
Dick

"Norman Jones" wrote:

Hi Dick,

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").Interio r
.ColorIndex = 41
.Pattern = xlSolid
End With
PlotSheetHome
End Sub


---
Regards,
Norman



"Dick Scheibe" wrote in message
...
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.


Dick 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.)



Dick 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

Hi Dick,

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



"Dick Scheibe" wrote in message
...
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.
Dick

"Norman Jones" wrote:

Hi Dick,

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").Interio r
.ColorIndex = 41
.Pattern = xlSolid
End With
PlotSheetHome
End Sub


---
Regards,
Norman



"Dick Scheibe" wrote in message
...
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.


Dick 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.)



Dick 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

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.
Dick

"Dick 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.
Dick

"Norman Jones" wrote:

Hi Dick,

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").Interio r
.ColorIndex = 41
.Pattern = xlSolid
End With
PlotSheetHome
End Sub


---
Regards,
Norman



"Dick Scheibe" wrote in message
...
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.


Dick 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.)



Dick 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