Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Cannot change title of second chart
Sub Macro5()
' ' Macro5 Macro ' Macro does not change title of second chart TitleName = "Stripper Well Survey - " Windows("StripperWellsMod.xls").Activate Sheets("Charts").Select Range("A1").Select SheetColumn = 1 ChartNum = 1 ActiveSheet.ChartObjects("Chart 1").Activate Sheets("#of wells").Select RowLoc = LTrim(Str$(43 + ChartNum)) 'eg: B44 NameLoc = "B" + RowLoc Range(NameLoc).Select 'state name, eg: ALABAMA CTitle = TitleName + ActiveCell.Value Sheets("Charts").Select TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) 'eg: A1 Range(TextSheetColumn).Select ActiveSheet.ChartObjects("Chart 1").Activate With Worksheets("Charts").ChartObjects("Chart 1").Chart .HasTitle = True .ChartTitle.Text = CTitle End With ' Title is selected at this point and is changed ActiveChart.ChartArea.Copy SheetColumn = SheetColumn + 21 TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) 'eg: A22 Range(TextSheetColumn).Select ActiveSheet.Paste ' Now have two identical charts with second one selected ActiveWindow.Visible = False Selection.Name = "Chart 2" ' Now have copy with known chart name.. so try implicit loop ChartNum = 2 Sheets("#of wells").Select RowLoc = LTrim(Str$(43 + ChartNum)) 'eg: B45 NameLoc = "B" + RowLoc Range(NameLoc).Select 'state name, eg: ARIZONA CTitle = TitleName + ActiveCell.Value Sheets("Charts").Select TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) 'eg: A22 Range(TextSheetColumn).Select ChartName = "Chart" + Str$(ChartNum) ActiveSheet.ChartObjects(ChartName).Activate With Worksheets("Charts").ChartObjects(ChartName).Chart .HasTitle = True .ChartTitle.Text = CTitle End With ' ChartName and CTitle are correct ' Title is NOT selected at this point and is NOT changed End Sub |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Cannot change title of second chart
There is almost never a need to select or activate objects. Also, it is very
risky to rely on something being the active object. The code below does what you want and can be run irrespective of what workbook/sheet is the active element...as long as the target workbook is open and the appropriate worksheets exist in it. Option Explicit Sub noSelectsOrActivates() Const TitleName As String = "Stripper Well Survey - " Dim aWB As Workbook Set aWB = Workbooks("StripperWellsMod.xls") 'Set aWB = Workbooks("Book3") Dim SheetColumn As Integer, ChartNum As Integer SheetColumn = 1 ChartNum = 1 Dim CTitle As String CTitle = TitleName & aWB.Sheets("#of wells").Cells(43 + ChartNum, 2).Value With aWB.Worksheets("Charts").ChartObjects(1).Chart .HasTitle = True .ChartTitle.Text = CTitle .ChartArea.Copy End With 'The variable SheetColumn is apparently incorrectly named _ since it is used to reference a row! ChartNum = 2 With aWB.Worksheets("Charts") .Paste With .ChartObjects(.ChartObjects.Count) .Top = aWB.Worksheets("Charts").Cells(SheetColumn + 21, 1).Top .Left = aWB.Worksheets("Charts").Cells(SheetColumn + 21, 1).Left With .Chart .HasTitle = True .ChartTitle.Text = TitleName _ & aWB.Sheets("#of wells").Cells(43 + ChartNum, 2).Value End With End With End With End Sub -- Tushar Mehta http://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach In Excel 2007 double-click to format may not work; right click and select from the menu "Robert Baer" wrote: Sub Macro5() ' ' Macro5 Macro ' Macro does not change title of second chart TitleName = "Stripper Well Survey - " Windows("StripperWellsMod.xls").Activate Sheets("Charts").Select Range("A1").Select SheetColumn = 1 ChartNum = 1 ActiveSheet.ChartObjects("Chart 1").Activate Sheets("#of wells").Select RowLoc = LTrim(Str$(43 + ChartNum)) 'eg: B44 NameLoc = "B" + RowLoc Range(NameLoc).Select 'state name, eg: ALABAMA CTitle = TitleName + ActiveCell.Value Sheets("Charts").Select TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) 'eg: A1 Range(TextSheetColumn).Select ActiveSheet.ChartObjects("Chart 1").Activate With Worksheets("Charts").ChartObjects("Chart 1").Chart .HasTitle = True .ChartTitle.Text = CTitle End With ' Title is selected at this point and is changed ActiveChart.ChartArea.Copy SheetColumn = SheetColumn + 21 TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) 'eg: A22 Range(TextSheetColumn).Select ActiveSheet.Paste ' Now have two identical charts with second one selected ActiveWindow.Visible = False Selection.Name = "Chart 2" ' Now have copy with known chart name.. so try implicit loop ChartNum = 2 Sheets("#of wells").Select RowLoc = LTrim(Str$(43 + ChartNum)) 'eg: B45 NameLoc = "B" + RowLoc Range(NameLoc).Select 'state name, eg: ARIZONA CTitle = TitleName + ActiveCell.Value Sheets("Charts").Select TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) 'eg: A22 Range(TextSheetColumn).Select ChartName = "Chart" + Str$(ChartNum) ActiveSheet.ChartObjects(ChartName).Activate With Worksheets("Charts").ChartObjects(ChartName).Chart .HasTitle = True .ChartTitle.Text = CTitle End With ' ChartName and CTitle are correct ' Title is NOT selected at this point and is NOT changed End Sub |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Cannot change title of second chart
Tushar Mehta wrote:
There is almost never a need to select or activate objects. Also, it is very risky to rely on something being the active object. The code below does what you want and can be run irrespective of what workbook/sheet is the active element...as long as the target workbook is open and the appropriate worksheets exist in it. Option Explicit Sub noSelectsOrActivates() Const TitleName As String = "Stripper Well Survey - " Dim aWB As Workbook Set aWB = Workbooks("StripperWellsMod.xls") 'Set aWB = Workbooks("Book3") Dim SheetColumn As Integer, ChartNum As Integer SheetColumn = 1 ChartNum = 1 Dim CTitle As String CTitle = TitleName & aWB.Sheets("#of wells").Cells(43 + ChartNum, 2).Value With aWB.Worksheets("Charts").ChartObjects(1).Chart .HasTitle = True .ChartTitle.Text = CTitle .ChartArea.Copy End With 'The variable SheetColumn is apparently incorrectly named _ since it is used to reference a row! ChartNum = 2 With aWB.Worksheets("Charts") .Paste With .ChartObjects(.ChartObjects.Count) .Top = aWB.Worksheets("Charts").Cells(SheetColumn + 21, 1).Top .Left = aWB.Worksheets("Charts").Cells(SheetColumn + 21, 1).Left With .Chart .HasTitle = True .ChartTitle.Text = TitleName _ & aWB.Sheets("#of wells").Cells(43 + ChartNum, 2).Value End With End With End With End Sub Thanks, will give that a try. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the size of a chart title box? | Charts and Charting in Excel | |||
Dynamically change data in chart title | Charts and Charting in Excel | |||
how can i use a function to change chart title in Excel 2007 | Excel Worksheet Functions | |||
Possible to change chart title and label box widths? | Charts and Charting in Excel | |||
change chart title with auto filter | Charts and Charting in Excel |