Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart Macro
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 Whe=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 -- kwedde01 ------------------------------------------------------------------------ kwedde01's Profile: http://www.excelforum.com/member.php...o&userid=24156 View this thread: http://www.excelforum.com/showthread...hreadid=378714 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart Macro
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("ChartObjectNa me").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...) "kwedde01" wrote: 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 Whe=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 -- kwedde01 ------------------------------------------------------------------------ kwedde01's Profile: http://www.excelforum.com/member.php...o&userid=24156 View this thread: http://www.excelforum.com/showthread...hreadid=378714 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to change position of chart labels on line chart | Charts and Charting in Excel | |||
Chart macro | Charts and Charting in Excel | |||
chart macro | Excel Discussion (Misc queries) | |||
Chart Macro | Charts and Charting in Excel | |||
Chart Macro | Charts and Charting in Excel |