How do I get VBA to copy the MajorGridlines.Border.LineStyle into a variable

revector

New Member
Joined
Jul 29, 2008
Messages
2
Hi there,

I am trying to write code that will read chart properties from one chart and apply them to others. All the code is working execpt code to try to read and apply major grid line properties. I am using excel 2003 on windows XP. When I copy the "ActiveChart.Axes(xlValue).MajorGridlines.Border.LineStyle" to a variable and use a msgbox to display that variable it displays the value as "-4118" when I read it from a chart with dashed gridlines. I'm posting the code I'm trying to use. Any thoughts on what I'm doing wrong. Thanks in advance.<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shape id=_x0000_i1025 style="WIDTH: 12pt; HEIGHT: 15.75pt" type="#_x0000_t75" alt=""><v:imagedata o:href="http://www.excelforum.com/images/smilies/confused.gif" src="file:///C:\Temp\msohtml1\01\clip_image001.gif"></v:imagedata></v:shape>

Code:
Dim GridlinesColour As Variant
Dim GridlinesWeight As String
Dim GridlinesLineStyle As String

Sub CollectChartPropertiesFromChart()
If ActiveChart.Axes(xlValue).HasMajorGridlines Then GridlinesColour = ActiveChart.Axes(xlValue).MajorGridlines.Border.ColorIndex
If ActiveChart.Axes(xlValue).HasMajorGridlines Then GridlinesWeight = ActiveChart.Axes(xlValue).MajorGridlines.Border.Weight
If ActiveChart.Axes(xlValue).HasMajorGridlines Then GridlinesLineStyle = ActiveChart.Axes(xlValue).MajorGridlines.Border.LineStyle
End Sub

Sub ApplyChartProperties()
If ActiveChart.Axes(xlValue).HasMajorGridlines Then ActiveChart.Axes(xlValue).MajorGridlines.Border.ColorIndex = GridlinesColour
If ActiveChart.Axes(xlValue).HasMajorGridlines Then ActiveChart.Axes(xlValue).MajorGridlines.Border.Weight = GridlinesWeight
If ActiveChart.Axes(xlValue).HasMajorGridlines Then ActiveChart.Axes(xlValue).MajorGridlines.Border.LineStyle = GridlinesLineStyle
End Sub<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

revector

New Member
Joined
Jul 29, 2008
Messages
2
I have found a solution with the help of Shg from excelforum. Posting the solution here in case somebody else runs into the same issue. The problem was with my variable type. I had tried String and Variant as types. He suggested using XlColorIndex, XlBorderWeight and XlLineStyle wich worked like a dream. He also rewrote by code to be a bit easier to troubleshoot

Code:
Dim GridlinesColour As XlColorIndex
Dim GridlinesWeight As XlBorderWeight
Dim GridlinesLineStyle As XlLineStyle

Sub CollectChartPropertiesFromChart()
With ActiveChart.Axes(xlValue)
If .HasMajorGridlines Then
With .MajorGridlines.Border
GridlinesColour = .ColorIndex
GridlinesWeight = .Weight
GridlinesLineStyle = .LineStyle
End With
End If
End With
End Sub

Sub ApplyChartProperties()
With ActiveChart.Axes(xlValue)
If .HasMajorGridlines Then
With .MajorGridlines.Border
.ColorIndex = GridlinesColour
.Weight = GridlinesWeight
.LineStyle = GridlinesLineStyle
End With
End If
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,132,670
Messages
5,654,653
Members
418,146
Latest member
Shnn028

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top