If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
macro to create charts in worksheets with arbitrary names
Jon, I found the source of my problem. It had nothing to do with your or Tushar's programing.
The spreadsheets in which I tried to use the macro are generated by an XLA program written by an outside firm. They chose the names of the worksheets in the form of 'Channel_I-013', and the 'minus' sign has been the source of all this trouble. Even though the worksheet name is not specifically used by the macro discussed here, somehow some piece of VBA code looks at it, 'thinks' it's a subtraction sign and screws everything up at '' Define the data and type ActiveChart.SeriesCollection(1).Values = "=" & sSheet & "!R2C8:R1000C8" If I remove the minus sign or replace it with an underscore sign, everythng runs smoothly. So, the lesson is, don't ever use hyphens in worksheet names even though the sign is not listed on the list of proscribed charatcerrs in worksheet names: \ / * ? [ ]! Maybe now somebody will suggest how to incorporate the worksheet name change into the macro to get rid of this offensive minus/hyphen sign! ;-). Great thanks again for your untiring efforts to help me; this was quite an education! z.entropic ================================== |
#22
|
|||
|
|||
macro to create charts in worksheets with arbitrary names
It's always something stupid, isn't it. But there's a fix for this.
Whenever a sheet name has a special character (i.e., one that can cause trouble), you can surround the sheet name with single quotes: ActiveChart.SeriesCollection(1).Values = _ "='" & sSheet & "'!R2C8:R1000C8" See if this helps. In fact, it never hurts to use the single quotes, because Excel ignores them if they are not needed. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ z.entropic wrote: Jon, I found the source of my problem. It had nothing to do with your or Tushar's programing. The spreadsheets in which I tried to use the macro are generated by an XLA program written by an outside firm. They chose the names of the worksheets in the form of 'Channel_I-013', and the 'minus' sign has been the source of all this trouble. Even though the worksheet name is not specifically used by the macro discussed here, somehow some piece of VBA code looks at it, 'thinks' it's a subtraction sign and screws everything up at '' Define the data and type ActiveChart.SeriesCollection(1).Values = "=" & sSheet & "!R2C8:R1000C8" If I remove the minus sign or replace it with an underscore sign, everythng runs smoothly. So, the lesson is, don't ever use hyphens in worksheet names even though the sign is not listed on the list of proscribed charatcerrs in worksheet names: \ / * ? [ ]! Maybe now somebody will suggest how to incorporate the worksheet name change into the macro to get rid of this offensive minus/hyphen sign! ;-). Great thanks again for your untiring efforts to help me; this was quite an education! z.entropic ================================== |
#24
|
|||
|
|||
macro to create charts in worksheets with arbitrary names
Yes!
z.entropic "Jon Peltier" wrote: It's always something stupid, isn't it. But there's a fix for this. Whenever a sheet name has a special character (i.e., one that can cause trouble), you can surround the sheet name with single quotes: ActiveChart.SeriesCollection(1).Values = _ "='" & sSheet & "'!R2C8:R1000C8" See if this helps. In fact, it never hurts to use the single quotes, because Excel ignores them if they are not needed. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ z.entropic wrote: Jon, I found the source of my problem. It had nothing to do with your or Tushar's programing. The spreadsheets in which I tried to use the macro are generated by an XLA program written by an outside firm. They chose the names of the worksheets in the form of 'Channel_I-013', and the 'minus' sign has been the source of all this trouble. Even though the worksheet name is not specifically used by the macro discussed here, somehow some piece of VBA code looks at it, 'thinks' it's a subtraction sign and screws everything up at '' Define the data and type ActiveChart.SeriesCollection(1).Values = "=" & sSheet & "!R2C8:R1000C8" If I remove the minus sign or replace it with an underscore sign, everythng runs smoothly. So, the lesson is, don't ever use hyphens in worksheet names even though the sign is not listed on the list of proscribed charatcerrs in worksheet names: \ / * ? [ ]! Maybe now somebody will suggest how to incorporate the worksheet name change into the macro to get rid of this offensive minus/hyphen sign! ;-). Great thanks again for your untiring efforts to help me; this was quite an education! z.entropic ================================== |
#25
|
|||
|
|||
macro to create charts in worksheets with arbitrary names
I cannot deny that you were absolutely right on three counts: the quotes (whose import I completely missed at the time), my inability then to grasp your solution, and the need to re-read the thread knowing what I know now. Actually, this is how I found the hyphen-minus problem.
Here's a bonus question ;-): by default, the created chart is small. I tried to incorporate a resizing statement by recording a little macro separately and incorporating it into the main one, but my trick didn't work because of, again, the specific and variable chart naming problem--"Chart 3" in Sub ResizeChart() ActiveSheet.ChartObjects("Chart 3").Activate ActiveChart.ChartArea.Select ActiveSheet.Shapes("Chart 3").IncrementLeft -169.5 ActiveSheet.Shapes("Chart 3").IncrementTop -108.75 ActiveSheet.Shapes("Chart 3").ScaleWidth 1.92, msoFalse, msoScaleFromTopLeft ActiveSheet.Shapes("Chart 3").ScaleHeight 1.91, msoFalse, msoScaleFromTopLeft End Sub I've tried all kinds of substitutions using sSheet for "Chart 3", but no cigar... the entire macro is listed below. Thanks for your suggestions and help. z.entropic Sub VQChart() '' declare variables for active sheet name and selected range Dim sSheet As String Dim rRange As Range Dim sAddr As String sSheet = ActiveSheet.Name '' add chart and put it where you want it Charts.Add ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheet '' Delete first series Do While ActiveChart.SeriesCollection.Count 0 ActiveChart.SeriesCollection(1).Delete Loop '' Add first series (sometimes charts.add does, sometimes not) If ActiveChart.SeriesCollection.Count = 0 Then ActiveChart.SeriesCollection.NewSeries End If '' Define the data and type ActiveChart.SeriesCollection(1).Values = _ "='" & sSheet & "'!R2C8:R1000C8" ActiveChart.SeriesCollection(1).XValues = _ "='" & sSheet & "'!R2C9:R1000C9" ActiveChart.SeriesCollection(1).Name = "='" & sSheet & "'!R1C9" ActiveChart.ChartType = xlXYScatterLinesNoMarkers '' Add second series and define data ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(2).Values = _ "='" & sSheet & "'!R2C8:R1000C8" ActiveChart.SeriesCollection(2).XValues = _ "='" & sSheet & "'!R2C10:R1000C10" ActiveChart.SeriesCollection(2).Name = "='" & sSheet & "'!R1C10" ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheet With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "capacity, Ah" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "voltage, V" End With ActiveChart.Legend.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic .Background = xlAutomatic End With Selection.Width = 135 Selection.Height = 36 Selection.Left = 186 Selection.Top = 128 ActiveChart.SeriesCollection(1).Select With Selection.Border .ColorIndex = 1 .Weight = xlHairline .LineStyle = xlContinuous End With With Selection .MarkerBackgroundColorIndex = 1 .MarkerForegroundColorIndex = 1 .MarkerStyle = xlNone .Smooth = False .MarkerSize = 5 .Shadow = False End With ActiveChart.SeriesCollection(2).Select With Selection.Border .ColorIndex = 3 .Weight = xlThin .LineStyle = xlContinuous End With With Selection .MarkerBackgroundColorIndex = xlNone .MarkerForegroundColorIndex = xlNone .MarkerStyle = xlNone .Smooth = False .MarkerSize = 3 .Shadow = False End With ActiveChart.Axes(xlValue).Select With ActiveChart.Axes(xlValue) .MinimumScale = 2 .MaximumScaleIsAuto = True .MinorUnitIsAuto = True .MajorUnitIsAuto = True .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With End Sub "Tushar Mehta" wrote: Ummm...if you go back to my June 10 post, you'll find that the proposed code includes single quotes around the worksheet name. As you noted in a subsequent post, the tips might have been too cryptic to be useful at that point. Now, given the (painful? g) experience of the past week, it might be worth your while to revisit those earlier posts ;-) -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
#26
|
|||
|
|||
macro to create charts in worksheets with arbitrary names
At some point it's your active chart, so just use ActiveChart. Actually
Activehart.Parent to adjust the ChartObject. I hate using the Shape object when editing a chart, so I use the Chart Object. With ActiveChart.Parent .Left = left coordinate in points .Top = top coordinate in points .Width = width in points .Height = height in points End With If you want to line the chart up with a range of cells, that's easy too: Set rngChart = ActiveSheet.Range("D4:K20") With ActiveChart.Parent .Left = rngChart.Left .Top = rngChart.Top .Width = rngChart.Width .Height = rngChart.Height End With - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ z.entropic wrote: I cannot deny that you were absolutely right on three counts: the quotes (whose import I completely missed at the time), my inability then to grasp your solution, and the need to re-read the thread knowing what I know now. Actually, this is how I found the hyphen-minus problem. Here's a bonus question ;-): by default, the created chart is small. I tried to incorporate a resizing statement by recording a little macro separately and incorporating it into the main one, but my trick didn't work because of, again, the specific and variable chart naming problem--"Chart 3" in Sub ResizeChart() ActiveSheet.ChartObjects("Chart 3").Activate ActiveChart.ChartArea.Select ActiveSheet.Shapes("Chart 3").IncrementLeft -169.5 ActiveSheet.Shapes("Chart 3").IncrementTop -108.75 ActiveSheet.Shapes("Chart 3").ScaleWidth 1.92, msoFalse, msoScaleFromTopLeft ActiveSheet.Shapes("Chart 3").ScaleHeight 1.91, msoFalse, msoScaleFromTopLeft End Sub I've tried all kinds of substitutions using sSheet for "Chart 3", but no cigar... the entire macro is listed below. Thanks for your suggestions and help. z.entropic Sub VQChart() '' declare variables for active sheet name and selected range Dim sSheet As String Dim rRange As Range Dim sAddr As String sSheet = ActiveSheet.Name '' add chart and put it where you want it Charts.Add ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheet '' Delete first series Do While ActiveChart.SeriesCollection.Count 0 ActiveChart.SeriesCollection(1).Delete Loop '' Add first series (sometimes charts.add does, sometimes not) If ActiveChart.SeriesCollection.Count = 0 Then ActiveChart.SeriesCollection.NewSeries End If '' Define the data and type ActiveChart.SeriesCollection(1).Values = _ "='" & sSheet & "'!R2C8:R1000C8" ActiveChart.SeriesCollection(1).XValues = _ "='" & sSheet & "'!R2C9:R1000C9" ActiveChart.SeriesCollection(1).Name = "='" & sSheet & "'!R1C9" ActiveChart.ChartType = xlXYScatterLinesNoMarkers '' Add second series and define data ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(2).Values = _ "='" & sSheet & "'!R2C8:R1000C8" ActiveChart.SeriesCollection(2).XValues = _ "='" & sSheet & "'!R2C10:R1000C10" ActiveChart.SeriesCollection(2).Name = "='" & sSheet & "'!R1C10" ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheet With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "capacity, Ah" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "voltage, V" End With ActiveChart.Legend.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic .Background = xlAutomatic End With Selection.Width = 135 Selection.Height = 36 Selection.Left = 186 Selection.Top = 128 ActiveChart.SeriesCollection(1).Select With Selection.Border .ColorIndex = 1 .Weight = xlHairline .LineStyle = xlContinuous End With With Selection .MarkerBackgroundColorIndex = 1 .MarkerForegroundColorIndex = 1 .MarkerStyle = xlNone .Smooth = False .MarkerSize = 5 .Shadow = False End With ActiveChart.SeriesCollection(2).Select With Selection.Border .ColorIndex = 3 .Weight = xlThin .LineStyle = xlContinuous End With With Selection .MarkerBackgroundColorIndex = xlNone .MarkerForegroundColorIndex = xlNone .MarkerStyle = xlNone .Smooth = False .MarkerSize = 3 .Shadow = False End With ActiveChart.Axes(xlValue).Select With ActiveChart.Axes(xlValue) .MinimumScale = 2 .MaximumScaleIsAuto = True .MinorUnitIsAuto = True .MajorUnitIsAuto = True .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With End Sub "Tushar Mehta" wrote: Ummm...if you go back to my June 10 post, you'll find that the proposed code includes single quotes around the worksheet name. As you noted in a subsequent post, the tips might have been too cryptic to be useful at that point. Now, given the (painful? g) experience of the past week, it might be worth your while to revisit those earlier posts ;-) -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
Thread Tools | |
Display Modes | |
|
|