Issues setting fill and no fill on markers

David100

New Member
Joined
Jul 20, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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




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:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,889
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

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,870
Messages
5,627,364
Members
416,245
Latest member
Xterminat

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