I am having some weird issues with the code below to selectively turn markers off, filled markers (solid) or unfilled markers (hollow).
The funny structure of the code below is due to having a custom ribbon with buttons to invoke the code. The is another common sub that is called first and will find every Series in the selection and rerun the following code for each series. So I can select a series and only modify that or a selection of several series or a complete chart etc.
If I create a new chart and run the Solid code it will work. I can turn off or on with solid and it works every time. If I then run Hollow then I will get solid markers again. But if I had selected hollow the very first time then that works but not solid. So it seem the markers get stuck in whatever was done first. The markers off code always works.
Even more odd if I then save & close the the xls and reopen I get one shot at setting solid/hollow again before it gets stuck. So they could have been solid at save and after loading if I select hollow before trying solid it will work.
One more quirk. Once stuck in FILLED mode if I use the GUI and then click Marker | Marker Option | Fill | No Fill the radio button "No Fill" it will not select. The markers will not un-fill either but change to a different color. If I then reclick it again then "No Fill" selects and the fills disappear as expected. I have now unstuck the markers like close/open does.
I originally used macros to record code to go for filled then unfilled and even this didn't seem to work when replaying. Got stuck in the same way, although that code was rather different.
I would appreciate any comment or links to proper docs on using markers. I find the MS references complete useless. Every entry seem to be a tautology and only states what I can guess from the property name.
Thanks
The funny structure of the code below is due to having a custom ribbon with buttons to invoke the code. The is another common sub that is called first and will find every Series in the selection and rerun the following code for each series. So I can select a series and only modify that or a selection of several series or a complete chart etc.
If I create a new chart and run the Solid code it will work. I can turn off or on with solid and it works every time. If I then run Hollow then I will get solid markers again. But if I had selected hollow the very first time then that works but not solid. So it seem the markers get stuck in whatever was done first. The markers off code always works.
Even more odd if I then save & close the the xls and reopen I get one shot at setting solid/hollow again before it gets stuck. So they could have been solid at save and after loading if I select hollow before trying solid it will work.
One more quirk. Once stuck in FILLED mode if I use the GUI and then click Marker | Marker Option | Fill | No Fill the radio button "No Fill" it will not select. The markers will not un-fill either but change to a different color. If I then reclick it again then "No Fill" selects and the fills disappear as expected. I have now unstuck the markers like close/open does.
I originally used macros to record code to go for filled then unfilled and even this didn't seem to work when replaying. Got stuck in the same way, although that code was rather different.
I would appreciate any comment or links to proper docs on using markers. I find the MS references complete useless. Every entry seem to be a tautology and only states what I can guess from the property name.
Thanks
VBA Code:
Public Sub MarkerCommon(ser As Series, str As String)
Select Case str
Case "MarkerOff"
ser.MarkerStyle = xlMarkerStyleNone
Case "MarkerSolid"
If ser.MarkerStyle = xlMarkerStyleNone Then
ser.MarkerStyle = xlMarkerStyleCircle
ser.MarkerSize = 10
End If
ser.MarkerForegroundColor = ser.Format.Line.ForeColor 'Marker Outside line
ser.MarkerBackgroundColor = ser.Format.Line.ForeColor 'Marker Outside line
ser.Format.Fill.ForeColor.RGB = ser.Format.Line.ForeColor ' Marker Inner Color
ser.Format.Fill.Transparency = 0
ser.Format.Fill.Visible = msoTrue
ser.Format.Fill.Solid
Case "MarkerHollow"
If ser.MarkerStyle = xlMarkerStyleNone Then
ser.MarkerStyle = xlMarkerStyleCircle
ser.MarkerSize = 10
End If
ser.MarkerForegroundColor = ser.Format.Line.ForeColor 'Marker Outside line
ser.Format.Fill.ForeColor.RGB = ser.Format.Line.ForeColor
ser.Format.Fill.Transparency = 1
ser.Format.Fill.Visible = False
Case "MarkerSizeUp"
If ser.MarkerSize < 20 Then
ser.MarkerSize = ser.MarkerSize + 1
End If
Case "MarkerSize10"
ser.MarkerSize = 10
Case "MarkerSizeDown"
If ser.MarkerSize > 2 Then
ser.MarkerSize = ser.MarkerSize - 1
End If
End Select
End Sub
Last edited by a moderator: