Chart Macro

[kwedde01]

When I record a chart macro and then run it, it gives an error message
on th eline Charts.Add, when the workbook is locked. How can i get
around this without manually unprotecting the workbook. Also when run
it produces a new sheet called Chart1. I do not want to create a new
sheet with the chart, but rather have it appear on the screen where the
data tables are.

Finally is there a way for me to create a user form, which has 3
different options for different charts, taken from different data, and
when each is selected the corresponding chart is diplayed on the user
form, or in the window.


This is the code for one chart so far, this generates the error
mentioned above:

Sub Chart2()
'
Range("A5:C12").Select
Charts.Add
ActiveChart.ChartType = xl3DBarClustered
ActiveChart.SetSourceData Source:=Sheets("BRANCH TOTAL CURRENT v
PREVIOUS"). _
Range("A5:C12"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
..HasTitle = True
..ChartTitle.Characters.Text = _
"Total Branch Scores-Current vs Previous (In-Branch)"
..Axes(xlCategory).HasTitle = True
..Axes(xlCategory).AxisTitle.Characters.Text = "Branch"
..Axes(xlSeries).HasTitle = False
..Axes(xlValue).HasTitle = True
..Axes(xlValue).AxisTitle.Characters.Text = "Indexed Scores"
End With
End Sub

Any help would be appreciated

 

[K Dales]

First question: You need to Unprotect the book to make a change - that is
what protection is for - but you can do it from code with
ThisWorkbook.Unprotect and then (when done creating and modifying the chart)
ThisWorkbook.Protect.

Charts.Add will create a chart sheet.
Worksheets("SheetName").ChartObjects.Add will create a chartobject, which is
a "container" for a chart within a worksheet, and this will do what you want.
The actual chart is then referenced as
Worksheets("SheetName).ChartObjects("ChartObjectName").Chart.

Finally, yes you could do as you want with a UserForm - you would need to
set up all the selection controls and code. You could then (via code) create
the chart on a worksheet and activate that sheet, or potentially put it in a
bound frame in your UserForm. I am not sure if you can get an Excel chart
onto a userform; I do not seem to have the necessary ActiveX controls on my
machine - you could use a Microsoft Chart object, which is similar but a
little more limited (6 series max, if I remember correctly...)