Chart formatting - CommandButton

[Acheron]

Hello,

I wrote the following code aiming to format different parameters in a chart.
The purpose is to run the macro after having selected a chart in the
worksheet (ie the one you specifically want to change) .

---------------------------------
Sub InsideChartFormat()
'
'05/10/2004 - rvg
'
ActiveChart.ChartArea.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
'Plot Area
ActiveChart.PlotArea.Select
Selection.Left = 1
Selection.Top = 10
Selection.Width = 600
Selection.Height = 650
'Legend
ActiveChart.Legend.Select
With Selection
.Top = 40
.Left = 50
.Width = 120
.Height = 50
End With
End Sub
-----------------------------------

Everything worked perfectly... but I tried to assign the macro to a
CommandButton. Then, my problem is as follow : after having selected my
chart, I click on the CommnandButton. The routine starts, then stops with an
'91' error type. Could someone help me to fix this ?!

Acheron

PS : I guess that clicking on the CommandButton has an impact such as
deselecting the chart or changing it into a Shape object instead of a
ChartObject ... but I'm stuck. Note that using a CommandButton is really,
really convenient. Putting an extra button in a toolbar is not an option,
considering that I have about 12 different types of charts in my sheet
requiring different formatting. Consequently, to be 'user friendly', I need
to put a CommandButton next to each type of chart (a type can include a few
charts, but not all of them need to be re-formatted, reason why the user must
have the choice to select a specific chart ).

 

[Tom Ogilvy]

Change the Takefocusonclick property of the commandbutton to false.

 

[Acheron]

Thanks Tom... but I am afraid that this is not the solution. The error type
changed (and is now '1004' instead of '91')... It is more frustrating knowing
that, when I run the macro from the Visual Basic toolbar... everything works
properly... aaaarghhhhh....

 

[Tom Ogilvy]

I wouldn't say that isn't the solution to the problem you described. After
all, it changed the error. That doesn't mean you don't have more problems
in you code. Try putting all the code back in a general module and then
call it from your event code.

Private Sub Commandbutton_Click()
InsideChartformat
End Sub

the InsideChartFormat should be in a general module, not in a sheet module
or the thisworkbook module or a userform module.

 

[Acheron]

Hi,

I checked my code... everything seems ok according to your remark (right
place, right module, etc). So the problem doesn't come from there.

I thought that I could, maybe try to specify the name of the chart,
rewriting the beginning of the routine as follow :

Dim chrtnme As String
chrtname = ActiveChart.Name
' display the Chart name to ensure it's the good one.
MsgBox chrtname
' Activate the selected chart
ActiveSheet.ChartObjects(chrtname).Activate

.... but once again... error (1004) (I tried even with the chrtname between
brackets). The Chart name is correctly displayed in the MsgBox. But the
routine stops at the following line.

I know that's something is going wrong with this code (and I have to
recognize that I'm quite a beginner in VBA...) so... if you can help, you're
always welcome !!!

 

[Tom Ogilvy]

The name of the chart is not the argument to ChartObjects, so no wonder you
are getting an error.

for each chtObj in Activesheet.ChartObject
msgbox chtObj.Name & " <--> " & chtObj.Chart.Name
Next

to illustrate.

But you have already said your code worked when it was in a general module.
Now you say it doesn't.

Everything worked perfectly... but I tried to assign the macro to a
CommandButton.

So saying you checked your code . . .