How do I offer ChartType options?

[davidm]

I have a chart loaded onto a Userform and am looking for a way to fi
multiple chart options via option buttons. To illustrate, catering fo
2 options, I attempted something like the codes below which failed wit
Type mismatch error. (The variable CharttNme is declared global a
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 Su

 

[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]

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

 

[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/
_______