Selecting charttype from macro is not working
 

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

Selecting charttype from macro is not working
 

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

Selecting charttype from macro is not working
 

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

Selecting charttype from macro is not working
 

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

Selecting charttype from macro is not working
 

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

Selecting charttype from macro is not working
 

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

Selecting charttype from macro is not working
 

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

Selecting charttype from macro is not working
 

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

Selecting charttype from macro is not working
 

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

Selecting charttype from macro is not working
 

Hi Ivan,

Can you give me a snippet or code for doing this. maybe just add the
appropriate lines in the code that i put in the question. That would be
of great help.

Thanks in advance.

Selecting charttype from macro is not working
 

Hi Shuvro,

post the code you use for filling listbox or combobox with values (I
mean the types of chart).

Regards,
Ivan

Selecting charttype from macro is not working
 

Hi Ivan

I have put this in the userform_activate()

ListBox1.Additem("xlLine") ' Line
ListBox1.Additem("xlLineMarkersStacked") ' Stacked Line with Markers
ListBox1.Additem("xlLineStacked") 'Stacked Line
ListBox1.Additem("xlPie") 'Pie
ListBox1.Additem("xlPieOfPie") ' Pie of Pie
ListBox1.Additem("xlPyramidBarStacked") ' Stacked Pyramid Bar
ListBox1.Additem("xlPyramidCol") ' 3D Pyramid Column
ListBox1.Additem("xlPyramidColClustered") ' Clustered Pyramid Column
ListBox1.Additem("xlPyramidColStacked") ' Stacked Pyramid Column
ListBox1.Additem("xlPyramidColStacked100") ' 100% Stacked Pyramid
Column
ListBox1.Additem("xlRadar") 'Radar
ListBox1.Additem("xlRadarFilled") 'Filled Radar
ListBox1.Additem("xlRadarMarkers") ' Radar with Data Markers
ListBox1.Additem("xlStockHLC") ' High-Low-Close
..
..
...
..
..
..
..

regds

Selecting charttype from macro is not working
 

Hi Shuvro,

the easiest way, how to fill ListBox with two columns of data is to
store them in a (hidden) worksheet.

Suppose, you have in a worksheet:
Line chart 4
Line Stacked Markers chart 66
.... and so on

then in rowsource for the listbox specify the range with the options
(see two rows above). Set column count to 2 and their width like
3cm;0cm (to hide the second column).

You can get the value from second column in selected row (I suppose
that multiselect is set to selectsingle) with this:

charttype=userform1.ListBox1.Column(1,userform1.Li stBox1.ListIndex)

In other words, you store the array in a worksheet (which is really
convenient), and then you assign this array to listbox control.

Did it help?

Regards,
Ivan

Selecting charttype from macro is not working
 

Hi Ivan,

Let me try this and get back. Thanks for the help though

Selecting charttype from macro is not working
 

Hi Ivan,

I tried as suggested. However the value is not returned. This is the
code I am using :
This is in the listbox click

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
CommandButton2.Visible = True
chrttype = 0
chrttype = chartform.ListBox1.Column(1, chartform.ListBox1.ListIndex)


This is in the process command button

Private Sub CommandButton2_Click()
On Error Resume Next
Dim ct As Integer
Dim chrttype As String
Dim chrtype As Long
Dim mypos As Long
ct = ComboBox1.Value
MsgBox str(chrttype)
'---------- I dont get a return value here.... I have put the values in
the sheet and named the range as listrange which is mapped to the
listbox1. The values are displayed without any issues. But not returned
here when the user has clicked.

'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

Hence not sure what is not working here!!

regds

Selecting charttype from macro is not working
 

Hi Shuvro,

sorry for late reply, we had public holiday yeasterday.

You have in Private Sub CommandButton2_Click() statement:
Dim chrtype As Long

This means that the scope of chrtype is this procedure only. Since you
don't assign any value to chrtype in this procedure, it is nothing. You
should Dim chrtype As Long in the top of the module to have it module
wide. Then you will be able to assign value to chrtype in
ListBox1_DblClick and read it in CommandButton2_Click.

See help for "understanding the lifetime of variables" for details.

Regards,
Ivan

Selecting charttype from macro is not working
 

Hi Ivan,

Actually I did declare that as Public and Long in General Declarations!
However it still is not working :-(

Selecting charttype from macro is not working
 

Hi Shuvro,

try this:

Private Sub CommandButton2_Click()
On Error Resume Next
Dim ct As Integer
Dim chrttype As String

Dim chrtype As Long 'dim chrtype statement is here, should be
in general declarations

Dim mypos As Long
ct = ComboBox1.Value

MsgBox chartform.ListBox1.Column(1, chartform.ListBox1.ListIndex)
'I added this

MsgBox str(chrttype)
\'---------- I dont get a return value here.... I have put the values
in
the sheet and named the range as listrange which is mapped to the
listbox1. The values are displayed without any issues. But not returned
here when the user has clicked.

'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

Regards,
Ivan