Using VBScript to change background color in Excel chart

Question

I'm using VBScript to create a line scatter plot from columns of data in Excel 2003. It comes out fine, but I want to edit some of the properties of the chart, such as background color and axis labels. I did this manually in Excel and recorded a macro, which gave me the following VBA code:

ActiveChart.PlotArea.Select
With Selection.Border
    .ColorIndex = 16
    .Weight = xlThin
    .LineStyle = xlContinuous
End With
With Selection.Interior
    .ColorIndex = 2
    .PatternColorIndex = 1
    .Pattern = xlSolid
End With
ActiveChart.Axes(xlCategory).Select
With Selection.TickLabels
    .ReadingOrder = xlContext
    .Orientation = 45
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .ReadingOrder = xlContext
    .Orientation = xlHorizontal
End With
ActiveChart.ChartArea.Select
End Sub

This looks fine for VBA, but I'm having trouble converting it to VBScript. How should I get started? This is my code at the moment:

Set objChart = objExcel.Charts.Add()
With objExcel.ActiveChart
    .ChartType = xlXYScatterLinesNoMarkers
    .SeriesCollection(1).Interior.Color = RGB(255, 0, 0)
    .HasTitle = True
    .ChartTitle.Text = "usage"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "units"
    .HasLegend = False
    .SetSourceData objWorksheet.Range("E1","F" & LastRow), xlColumns
    .SetSourceData objWorksheet.Range("E1:F200"), xlColumns
End With

The line .SeriesCollection(1).Interior.Color = RGB(255, 0, 0) causes an error: "Unable to set the color property of the Interior class". I'm guessing I shouldn't call .SeriesCollection right under .Activechart. Any suggestions? At this point I'd just be happy to be able to change the background color of the chart to white, and rotate the x-axis labels 45 degrees.

Thank you in advance.

That VBA looks pretty close to VBScript... did you try : With objExcel.ActiveChart <all your vba code here> end with? — Papasmile
– Papasmile, Commented Jul 17, 2013 at 15:12
If you're using VBScript, you can't use any constants such as xlCategory: Excel VBA knows what the values of those are, but they're not available in VBScript. You can find the numeric values in the Object Browser (F2 in the VBEditor) — Tim Williams
– Tim Williams, Commented Jul 17, 2013 at 16:29
... or just type ?xlCategory and press Enter in the Immediate Window. — Jon Peltier
– Jon Peltier, Commented Aug 20, 2015 at 15:55

Ansgar Wiechers · Accepted Answer · 2013-07-18 21:30:33Z

0

Order matters. Before you can access a series, you have to add it first. Same goes for other properties, like HasTitle. Also, the lines of an XY diagram don't have an interior color, that's only available in charts that show an interior (like pie charts or column charts). You probably mean the border color here. Change your code to this:

Set objChart = objExcel.Charts.Add
With objChart
  ' define chart type
  .ChartType = xlXYScatterLinesNoMarkers

  ' define data
  .SetSourceData objWorksheet.Range("E1:F200"), xlColumns

  ' format chart
  .SeriesCollection(1).Border.Color = RGB(255, 0, 0)
  .PlotArea.Interior.Color = RGB(255, 255, 255)
  '.PlotArea.Interior.ColorIndex = 2  'alternative
  .HasTitle = True
  .ChartTitle.Text = "usage"
  .Axes(xlCategory, xlPrimary).HasTitle = True
  .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
  .Axes(xlValue, xlPrimary).HasTitle = True
  .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "units"
  .HasLegend = False
End With

and it should work, provided you have defined the symbolic constants.

edited Jul 18, 2013 at 21:30

answered Jul 17, 2013 at 20:58

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

vicjun Over a year ago

Thanks a lot, it works now to change the color of the lines. But I would like the background color in the charts to be white, not the line. I also want to angle the x axis labels 45 degrees.

Ansgar Wiechers Over a year ago

The background color of the chart can be changed via .ChartArea.Color, the background color of the graph can be changed via .PlotArea.Color. You can also use .ColorIndex instead of .Color to assign an indexed color (e.g. 2 for white).

vicjun Over a year ago

I get an error though: "Object doesn't support this property of or method: 'PlotArea.Color' . I remember experimenting with this method before, getting similar results... Do I have to declare something else first? Thanks.

Ansgar Wiechers Over a year ago

My bad. It's .PlotArea.Interior.Color, not .PlotArea.Color.

George · Accepted Answer · 2016-03-07 08:57:52Z

There are two things:

setting the plotting area to a certain color

see example

'set plotting area to dark gray color
ActiveChart.PlotArea.Select
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorBackground1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = -0.5
    .Solid
End With

mind .ForeColor.ObjectThemeColor = msoThemeColorBackground1

Setting the general area of the chart to a certain color (I mean the entire area of the chart , except of the plotting area)

'set the color of the Chart Area to gray With ActiveSheet.Shapes("NameChart").Fill .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorBackground1 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = -0.5 .Solid End With

Where the name of the Chart has been set as "NameChart" earlier in the VBA code, when generating the Chart

  'set the name of the Chart to "Cooper" to reference it later
ActiveChart.Parent.Name = "NameChart"

mind .ForeColor.ObjectThemeColor = msoThemeColorBackground1

If my answer helped you in any way, please rate it. Thanks.

Michael Mulvey · Accepted Answer · 2019-11-26 16:44:00Z

The only ways I know are using VBScript or Python. Below is a VBScript that may help you . You would need an if statement to make the Date red or not.

I would use a formula to do this however and copy this to all the cells in the row you use for the date.

There is a link to the color index in the comments.

Save as ExcelDB.vbs

' Validate Variables.
Dim fso, strUserName , oShell

' Create an instance of the Scripting Shell
Set oShell = CreateObject( "WScript.Shell" )

' Get the Username from The Shell enviroment
strUserName = oShell.ExpandEnvironmentStrings( "%USERNAME%" )

' Create a File System Object named "fso".
Set fso = CreateObject("Scripting.FileSystemObject")

' Create Instance of Excel Object
Set objExcel = CreateObject("Excel.Application")

' Hide Alert messages
objExcel.DisplayAlerts = False

' Make Excel File Visible/Hidden (True=Visible , False = Hidden)
objExcel.Visible = True 

' Create a Workbook with 3 sheets named Retired, Styles and Patts
Set objWorkbook = objExcel.Workbooks.Add
objExcel.Activeworkbook.Sheets(1).Name = "Retired"
objExcel.Activeworkbook.Sheets(2).Name = "Styles"
objExcel.Activeworkbook.Sheets(3).Name = "Patts"

' Freeze header row (The first row will remain during sorting)

With objExcel.ActiveWindow
     .SplitColumn = 0
     .SplitRow = 1
End With
objExcel.ActiveWindow.FreezePanes = True


' Define Names for lookup tables.
objExcel.ActiveWorkbook.Names.Add "patterns", "=patts!$A$1:$b$150"
objExcel.ActiveWorkbook.Names.Add "styles", "=Styles!$A$1:$H$100"
objExcel.ActiveWorkbook.Names.Add "widths", "=Styles!$B$2:$B$101"

'Define Values for Cells

objExcel.Cells(1,1).Interior.ColorIndex = 3
objExcel.Cells(1,1).Value = "Red Background"                'Cell A1 Value
objExcel.Cells(2,1).Value = "GreenBorder"                   'Cell A2 Value
objExcel.Cells(2,2).Value = "1"
objExcel.Cells(2,3).Value = "1"
objExcel.Cells(2,4).Value = "1"
objExcel.Cells(2,5).Value = "12"
objExcel.Cells(2,6).Value = "12"
'Format Retired Cells

objExcel.Sheets("Retired").Cells(7, 2).Numberformat = "@"
objExcel.Worksheets("Retired").Cells(2,1).Borders.Color = RGB(0,255,0)
objExcel.Worksheets("Retired").Columns("A:A").Columnwidth = 20
objExcel.Worksheets("Retired").Columns("B:C").Columnwidth = 5
objExcel.Worksheets("Retired").Columns("D:D").Columnwidth = 15
objExcel.Worksheets("Retired").Columns("E:E").Columnwidth = 5
objExcel.Worksheets("Retired").Columns("F:H").columnwidth = 15
objExcel.Worksheets("Retired").Columns("I:I").Columnwidth = 5
objExcel.Worksheets("Retired").Columns("B").NumberFormat = "000"
objExcel.Worksheets("Retired").Columns("C").NumberFormat = "00"

'get a cell value and set it to a variable
row2Cell2 = objExcel.Cells(2,2).Value

'save the new excel file (make sure to change the location) 'xls for 2003 or earlier
objWorkbook.SaveAs "C:\Users\"&strUserName&"\Desktop\CreateExcel.xlsx" 

'close the workbook
'objWorkbook.Close 

'exit the excel program
'objExcel.Quit

The following line is how you change the color of an excel Cell where cell(1,1) = cell A1

objExcel.Cells(1,1).Interior.ColorIndex = 3

Collectives™ on Stack Overflow

Using VBScript to change background color in Excel chart

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related