Hi All,

I am trying to specify a chart type from a userform. The chart is
selected by the user in the active sheet and then the macro is run. It
tries to change the chart type to the one selected from the combobox.
When I hardcode the charttype in the macro it works. However when I try
to get value from the combobox text it doesnt. What is wrong? I'm
putting the code below for your reference :

Private Sub CommandButton2_Click()
On Error Resume Next
Dim ct As Integer
Dim chrttype As String
ct = ComboBox1.Value
chrttype = Trim(ListBox1.Value)
'MsgBox chrttype
'On Error Resume Next
If ct = 0 Then
MsgBox "Please select a series value from the list to continue..."
Exit Sub
Else
ActiveChart.SeriesCollection(ct).Select
ActiveChart.SeriesCollection(ct).ChartType = chrttype
ActiveChart.SeriesCollection(ct).Select
End If
CommandButton2.Visible = False
ListBox1.Visible = False
End Sub

Charttype is looking for a number, not a text string.

VBA has some predefined constants, such as xlLine, but xlLine actually holds
the value 4 so when you do

....charttype = xlLine
you are really saying
....charttype = 4

not
....charttype = "xlLine"

Adjust your code to provide the appropriate numeric value.

--
Regards,
Tom Ogilvy



"Shuvro Basu" wrote:

Hi All,

I am trying to specify a chart type from a userform. The chart is
selected by the user in the active sheet and then the macro is run. It
tries to change the chart type to the one selected from the combobox.
When I hardcode the charttype in the macro it works. However when I try
to get value from the combobox text it doesnt. What is wrong? I'm
putting the code below for your reference :

Private Sub CommandButton2_Click()
On Error Resume Next
Dim ct As Integer
Dim chrttype As String
ct = ComboBox1.Value
chrttype = Trim(ListBox1.Value)
'MsgBox chrttype
'On Error Resume Next
If ct = 0 Then
MsgBox "Please select a series value from the list to continue..."
Exit Sub
Else
ActiveChart.SeriesCollection(ct).Select
ActiveChart.SeriesCollection(ct).ChartType = chrttype
ActiveChart.SeriesCollection(ct).Select
End If
CommandButton2.Visible = False
ListBox1.Visible = False
End Sub

Hi Shuvro,

see help for charttype - you will find list of xl constants - each
specifying type of chart. Please note that these constants are numeric,
not string values.

Regards,
Ivan

Hi Tom and Ivan,

Thanks for your responses. Well then now the question is where can I
get the values for these chart type constatns. The help file gives the
names only not the enumerated or the constants associated with each
chart type.

Which way do I proceed now?

--regds

Ok guys,

Here is the list. I hope this helps others as well :

xl3DArea -4098
xl3DAreaStacked 78
xl3DAreaStacked100 79
xl3DBarClustered 60
xl3DBarStacked 61
xl3DBarStacked100 62
xl3DColumn -4100
xl3DColumnClustered 54
xl3DColumnStacked 55
xl3DColumnStacked100 56
xl3DLine -4101
xl3DPie -4102
xl3DPieExploded 70
xlArea 1
xlAreaStacked 76
xlAreaStacked100 77
xlBarClustered 57
xlBarOfPie 71
xlBarStacked 58
xlBarStacked100 59
xlBubble 15
xlBubble3DEffect 87
xlColumnClustered 51
xlColumnStacked 52
xlColumnStacked100 53
xlConeBarClustered 102
xlConeBarStacked 103
xlConeBarStacked100 104
xlConeCol 105
xlConeColClustered 95
xlConeColStacked 96
xlConeColStacked100 97
xlCylinderCol 98
xlCylinderColClustered 92
xlCylinderColStacked 93
xlCylinderColStacked100 94
xlDoughnut -4120
xlDoughnutExploded 80
xlLine 4
xlLineMarkers 65
xlLineMarkersStacked 66
xlLineMarkersStacked100 67
xlLineStacked 63
xlLineStacked100 64
xlPie 5
xlPieExploded 69
xlPieOfPie 68
xlPyramidBarClustered 109
xlPyramidBarStacked 110
xlPyramidBarStacked100 111
xlPyramidCol 112
xlPyramidColClustered 106
xlPyramidColStacked 107
xlPyramidColStacked100 108
xlRadar -4151
xlRadarFilled 82
xlRadarMarkers 81
xlStockHLC 88
xlStockOHLC 89
xlStockVHLC 90
xlStockVOHLC 91
xlSurface 83
xlSurfaceTopView 85
xlSurfaceTopViewWireframe 86
xlSurfaceWireframe 84
xlXYScatter -4169
xlXYScatterLines 74
xlXYScatterLinesNoMarkers 75
xlXYScatterSmooth 72
xlXYScatterSmoothNoMarkers 73

Cheers !!!

Hi Shuvro,

the help file gives you the constants. Click on xlcharttype and you
will see the constants that you can use in your code. For example
xlLine.

If you need to know the actual value of xlLine (which is number - type
Long), type into immediate window:
?xlline
4
You should see 4 as the value.

Using typename function you can prove that it is number (also in
immediate window):
?typename(xlline)
Long

Regards,
Ivan

Look them up in the object browser in the vbe.

Below the window/listboxes, you should see the value when you select them.

--
Regards,
Tom Ogilvy


"Shuvro Basu" wrote:

Hi Tom and Ivan,

Thanks for your responses. Well then now the question is where can I
get the values for these chart type constatns. The help file gives the
names only not the enumerated or the constants associated with each
chart type.

Which way do I proceed now?

--regds

Hi Guys,

Thanks. But then how do I use this in my code (above?) since a variable
is not working and the list in the combobox doesnt have the constants
associated with it. So does this mean that I have to create another
array with the corresponding constants and then after the user chooses
a value, seek the same in the other array and then get the long value
for the chart type? Dont tell me that there isnt a simpler way of doing
this!!

regds

Hi Shuvro,

you may have combobox with two columns, one with names you want to
display to user, the second with their meaning to Excel (I mean the
constants). The second column you can set not to be shown to the user.

Whenever user chooses from the combobox, you take value from the second
column.

This approach is similar to your thought with array, maybe a little
easier.

Regards,
Ivan