How do I offer ChartType options?

[davidm]

I have a chart loaded onto a Userform and am looking for a way to fit multiple chart options via option buttons. To illustrate, catering for 2 options, I attempted something like the codes below which failed with Type mismatch error. (The variable CharttNme is declared global at module level).
What am I doing wrong?

David.

The codes:

Dim CharttNme

Private Sub OptionButton1_Click()
Call CharttType
End Sub

Private Sub OptionButton2_Click()
Call CharttType
End Sub

Private Sub CharttType()
If OptionButton1.Value = True Then
chartNme = xlScatterSmooth
ElseIf OptionButton2.Value = True Then
chartNme = xlColumnClustered
End
End If
Call GraphTables
End Sub

Private Sub GraphTables()
Charts.Add
ActiveChart.ChartType = chartNme 'Type mismatch Error on this line
ActiveChart.SetSourceData Source:=Sheets("Data").Range("A1:C8"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Data"
With ActiveChart
.HasTitle = True
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).HasTitle = True
End With

Set ObChart = Sheets("Data").ChartObjects(1).Chart
CurrentChart.ChartType = chartNme

' Save chart as GIF
fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
ObChart.Export FileName:=fname, FilterName:="GIF"

' Show the chart
Image1.Picture = LoadPicture(fname)
Kill fname

End Sub


Private Sub UserForm_Initialize()
OptionButton1_Click
End Sub

[Greg Wilson]

This is based solely on a visual (no testing), but you apparently have a
spelling error. Should be "xlXYScatterSmooth". Also, although I don't think
it has any effect, there is an "End" statement I believe you didn't intend
right after "chartNme = xlColumnClustered".

I suggest you forget the module level var chtNme and the ChartType procedure
altogether and pass the constants xlXYScatterSmooth and xlColumnClustered
directly to GraphTables as follows:

Private Sub OptionButton1_Click()
Call GraphTables(xlXYScatterSmooth)
End Sub

Private Sub OptionButton2_Click()
Call GraphTables(xlColumnClustered)
End Sub

Private Sub GraphTables(ChartNme As Long)
Charts.Add
ActiveChart.ChartType = ChartNme
'Remaining code...
End Sub

I also suggest you check out Stephen Bullen's PastPicture.zip at:
http://www.bmsltd.ie/Excel/Default.htm
IMO, it's far superior to the technique you're using to update a chart in a
UF.

Regards,
Greg


"davidm" wrote:

>
> I have a chart loaded onto a Userform and am looking for a way to fit
> multiple chart options via option buttons. To illustrate, catering for
> 2 options, I attempted something like the codes below which failed with
> Type mismatch error. (The variable CharttNme is declared global at
> module level).
> What am I doing wrong?
>
> David.
>
> The codes:
>
> Dim CharttNme
>
> Private Sub OptionButton1_Click()
> Call CharttType
> End Sub
>
> Private Sub OptionButton2_Click()
> Call CharttType
> End Sub
>
> Private Sub CharttType()
> If OptionButton1.Value = True Then
> chartNme = xlScatterSmooth
> ElseIf OptionButton2.Value = True Then
> chartNme = xlColumnClustered
> End
> End If
> Call GraphTables
> End Sub
>
> Private Sub GraphTables()
> Charts.Add
> ActiveChart.ChartType = chartNme *'Type mismatch Error on this line
> *ActiveChart.SetSourceData Source:=Sheets("Data").Range("A1:C8"),
> PlotBy:=xlColumns
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Data"
> With ActiveChart
> .HasTitle = True
> .Axes(xlCategory, xlPrimary).HasTitle = True
> .Axes(xlValue, xlPrimary).HasTitle = True
> End With
>
> Set ObChart = Sheets("Data").ChartObjects(1).Chart
> CurrentChart.ChartType = chartNme
>
> ' Save chart as GIF
> fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
> ObChart.Export FileName:=fname, FilterName:="GIF"
>
> ' Show the chart
> Image1.Picture = LoadPicture(fname)
> Kill fname
>
> End Sub
>
>
> Private Sub UserForm_Initialize()
> OptionButton1_Click
> End Sub
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=496334
>
>

[davidm]

Greg,

Many thanks. The flaws you picked were inadvertent and resulted from hurried (over)editing. But even working with the original codes which had none of the blemishes, the Type Mismatch error was generated.

Your approach of passing the ChartType as arguments from subordinate subs worked well. But I am still a bit mystifed by the failure of the GraphTables code to handle the ChartNme (globally-declared) variable. arguments.

While you have solved my primary problem, any thoughts on why my method failed would be appreciated.

David

[Greg Wilson]

Hi David,

All of the following comments are based on minimal testing and several
assumptions.

I assume that the spelling error in "Dim CharttNme" (note double t) only
exists in your post. Your post executes the OB1 click event on UF
initialization:

< Private Sub UserForm_Initialize()
< OptionButton1_Click
< End Sub

This doesn't change OB1's value to True (contrary to logic) and therefore
the CharttType routine, assuming neither OB1 nor OB2 are defaulted to True
through Properties, fails to set a value for ChartNme and its value is
"Empty" when GraphTables is called:

< Private Sub CharttType()
< If OptionButton1.Value = True Then
< chartNme = xlXYScatterSmooth 'Spelling corrected
< ElseIf OptionButton2.Value = True Then
< chartNme = xlColumnClustered
< End If
< Call GraphTables
< End Sub

I assume, by executing the Click event for OB1 on initialization, you want
OB1 to default to True and to import its associated graph. I would instead
set its value to True through Properties (right-click OB1 > select Properties
> set Value to True) and directly execute the CharttType routine on UF
initialization. However, setting OB1 to True directly on UF initialization
instead of executing its click event will also achieve the desired objective:

Private Sub UserForm_Initialize()
OptionButton1 = True 'This works
End Sub

I reiterate my recommendation for Stephen Bullen's PastePicture.zip. You're
driving a Hyundai Pony and a Lexus is free. Also be carefull using Kill.
Files deleted this way don't go to the Recycle Bin, they're just history. Not
to imply I would do it any different (get rid of fname).

Regards,
Greg




"davidm" wrote:

>
> Greg,
>
> Many thanks. The flaws you picked were inadvertent and resulted from
> hurried (over)editing. But even working with the original codes which
> had none of the blemishes, the Type Mismatch error was generated.
>
> Your approach of passing the ChartType as arguments from subordinate
> subs worked well. But I am still a bit mystifed by the failure of the
> GraphTables code to handle the ChartNme (globally-declared) variable.
> arguments.
>
> While you have solved my primary problem, any thoughts on why my method
> failed would be appreciated.
>
> David
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=496334
>
>

[Jon Peltier]

Here is another point, which may not be the issue here, but would definitely
cause problems in bubble or stock charts. It is more reliable in VBA to set
a chart's source data range first, then assign a chart type. If the chart
does not have the correct data to support the chart type, you get an error.
Even if you're using a recorded macro, it will fail for this reason.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


"Greg Wilson" <[email protected]> wrote in message
news:[email protected]...
> Hi David,
>
> All of the following comments are based on minimal testing and several
> assumptions.
>
> I assume that the spelling error in "Dim CharttNme" (note double t) only
> exists in your post. Your post executes the OB1 click event on UF
> initialization:
>
> < Private Sub UserForm_Initialize()
> < OptionButton1_Click
> < End Sub
>
> This doesn't change OB1's value to True (contrary to logic) and therefore
> the CharttType routine, assuming neither OB1 nor OB2 are defaulted to True
> through Properties, fails to set a value for ChartNme and its value is
> "Empty" when GraphTables is called:
>
> < Private Sub CharttType()
> < If OptionButton1.Value = True Then
> < chartNme = xlXYScatterSmooth 'Spelling corrected
> < ElseIf OptionButton2.Value = True Then
> < chartNme = xlColumnClustered
> < End If
> < Call GraphTables
> < End Sub
>
> I assume, by executing the Click event for OB1 on initialization, you want
> OB1 to default to True and to import its associated graph. I would instead
> set its value to True through Properties (right-click OB1 > select
> Properties
>> set Value to True) and directly execute the CharttType routine on UF
> initialization. However, setting OB1 to True directly on UF initialization
> instead of executing its click event will also achieve the desired
> objective:
>
> Private Sub UserForm_Initialize()
> OptionButton1 = True 'This works
> End Sub
>
> I reiterate my recommendation for Stephen Bullen's PastePicture.zip.
> You're
> driving a Hyundai Pony and a Lexus is free. Also be carefull using Kill.
> Files deleted this way don't go to the Recycle Bin, they're just history.
> Not
> to imply I would do it any different (get rid of fname).
>
> Regards,
> Greg
>
>
>
>
> "davidm" wrote:
>
>>
>> Greg,
>>
>> Many thanks. The flaws you picked were inadvertent and resulted from
>> hurried (over)editing. But even working with the original codes which
>> had none of the blemishes, the Type Mismatch error was generated.
>>
>> Your approach of passing the ChartType as arguments from subordinate
>> subs worked well. But I am still a bit mystifed by the failure of the
>> GraphTables code to handle the ChartNme (globally-declared) variable.
>> arguments.
>>
>> While you have solved my primary problem, any thoughts on why my method
>> failed would be appreciated.
>>
>> David
>>
>>
>> --
>> davidm
>> ------------------------------------------------------------------------
>> davidm's Profile:
>> http://www.excelforum.com/member.php...o&userid=20645
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=496334
>>
>>