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

Change the Takefocusonclick property of the commandbutton to false.

--
Regards,
Tom Ogilvy

"Acheron" wrote in message
...
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 ).

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" wrote:

Change the Takefocusonclick property of the commandbutton to false.

--
Regards,
Tom Ogilvy

"Acheron" wrote in message
...
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 ).

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.

--
Regards,
Tom Ogilvy

"Acheron" wrote in message
...
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" wrote:

Change the Takefocusonclick property of the commandbutton to false.

--
Regards,
Tom Ogilvy

"Acheron" wrote in message
...
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 ).

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" wrote:

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.

--
Regards,
Tom Ogilvy

"Acheron" wrote in message
...
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" wrote:

Change the Takefocusonclick property of the commandbutton to false.

--
Regards,
Tom Ogilvy

"Acheron" wrote in message
...
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 ).

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

--
Regards,
Tom Ogilvy


"Acheron" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy

"Acheron" wrote in message
...
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" wrote:

Change the Takefocusonclick property of the commandbutton to false.

--
Regards,
Tom Ogilvy

"Acheron" wrote in message
...
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 ).