Issues setting fill and no fill on markers

[David100]

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

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

 

[pbornemeier]

I think swapping order of a few lines helped. This is an historic problem in Excel VBA. Search excel Series vba color problemto see many posts

Public Sub MarkerCommon(str As String)

    Dim ser As Series
    
    Set ser = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(3)

    Select Case str
        Case "MarkerOff"
        
            ser.MarkerStyle = xlMarkerStyleNone
           
        Case "MarkerSolid"
           
           If ser.MarkerStyle = xlMarkerStyleNone Then
                ser.MarkerStyle = xlMarkerStyleCircle
                ser.MarkerSize = 10
            End If
           
            ser.Format.Fill.Solid   'This before others
            
            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
           
       
        Case "MarkerHollow"
          
            If ser.MarkerStyle = xlMarkerStyleNone Then
                ser.MarkerStyle = xlMarkerStyleCircle
                ser.MarkerSize = 10
            End If
      
            ser.Format.Fill.Visible = msoFalse  'In case of gradients or picture marker background
            
            ser.MarkerForegroundColor = ser.Format.Line.ForeColor         'Marker Outside line
            
            ser.Format.Fill.Transparency = 1 'OK
           
        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