Macro won't execute from a Command Button

[Guest]

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

 

[Guest]

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]

Maybe you should post the offending code.

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?

 

[Guest]

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

 

[Norman Jones]

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


---
Regards,
Norman

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