I like what you are trying to do but I am having another issue where trying to set the chart type doesn't always work.
First of all, when I try to set a chart value, it doesn't adhere to the values I have found online. The values appear to be correct--I know this because I used a msgbox to tell me the object type and it matched what I've found online.
Here is my code:
sub SetChartType
set chart = ActiveDocument.GetSheetObject("adhoc")
intObjectType = chart.GetObjectType
Dim ChartValue
ChartValue = ActiveDocument.Variables("vSetChartType").GetContent().String
intObjectType = chart.GetObjectType
msgbox (intObjectType)
intTest = 12
if ChartValue = "1" then 'Straight Table
chart.SetChartType 5
elseif ChartValue = "2" then 'pivot table
chart.SetChartType 2
elseif ChartValue = "3" then 'bar chart --- DOES NOT WORK, set's it to a mekko chart...
chart.SetObjectType 12
elseif ChartValue = "4" then 'line chart
chart.SetChartType 3
elseif ChartValue = "5" then 'bar/line chart
chart.SetChartType 2
end if
end sub
Troubleshooting:
Set chart to bar chart, added msgbox to tell me the object value. The msgbox reads the bar chart as 12. As soon as it executes "chart.SetObjectType 12", it switches to a mekko chart. When I use msgbox to read the chart type selected, it tells me 37--what the heck?
This is the list I have:
0=Unknown
1=List Box
2=Multi Box
3=Statistics Box
4=Table Box
5=Button
6=Text Object
7=Current Selections Box
8=Input Box
9=Line/Arrow Object
10=Pivot Table
11=Straight Table
12=Bar Chart
13=Pie Chart
14=Scatter Chart
15=Line Chart
16=Combo Chart
17=Custom Object
18=Bookmark Object
19=Slider/Calendar Object
20=Grid Chart
21=Radar Chart
22=Gauge Chart
