Re: Identifying the Active Fill Color

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: John Mansfield (JohnMansfield_at_discussions.microsoft.com)
Date: 12/08/04 

Date: Wed, 8 Dec 2004 14:23:02 -0800

I don't know if this is quite what you're looking for, but i'll try anyway.
One way to get the last selected fill color would be to use a work*** cell
as a placeholder for the color index number. For example, the procedure
below extracts the color of the shape and adds the index number to cell A1.
Anytime the shape color is changed, the new color index is recorded in cell
A1. In short, cell A1 acts as a memory location for the active shape color.
When the workbook is saved and exited, the index number is also saved. When
the workbook is activated again, the procedure picks up the last color by
referencing cell A1 for the index. Of course, you will need to expand on the
idea below by writing your own procedure to fit your needs.

Sub RecordShapeColor()
    Dim Shp As Shape
    Dim Clr As Integer
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = Clr
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 3
    Selection.ShapeRange.Fill.Solid
    Clr = Selection.ShapeRange.Fill.ForeColor.SchemeColor
    Sheets("Sheet1").Range("A1").Value = Clr
End Sub

"P Daulton" wrote:

> No one having answered this so far I thought I'd have a go..
> I couldn't find anything in the object model to look at for this, but that's
> because I don't have an extensive excel object model to look at.
> I did however find that
> CommandBars("Formatting").Controls(24)
> was the fill colour button showing on the menu bar, but the only property
> that I could find which changed was its ToolTipText, the text that shows up
> after hovering over the button. This text remains the same even if the
> palette colours are changed!
> So the following macro looks at that text, converts it into the index value,
> converts it into rgb and uses that to colour the fill of the first shape
> object on the first work***. It's done in steps below, it could be
> condensed.
> I think Steve's right, there MUST be a better way.
> Pascal
> ps one small snag, if the user alters the palette later, the filled shapes
> won't change whereas cell fills will.
>
>
>
> Sub ToolTipTextColour()
> Select Case CommandBars("Formatting").Controls(24).TooltipText
> Case "Fill Color (Automatic)"
> mycolorindex = xlNone
> Case "Fill Color (Black)"
> mycolorindex = 1
> Case "Fill Color (Brown)"
> mycolorindex = 53
> Case "Fill Color (Olive Green)"
> mycolorindex = 52
> Case "Fill Color (Dark Green)"
> mycolorindex = 51
> Case "Fill Color (Dark Teal)"
> mycolorindex = 49
> Case "Fill Color (Dark Blue)"
> mycolorindex = 11
> Case "Fill Color (Indigo)"
> mycolorindex = 55
> Case "Fill Color (Gray-80%)"
> mycolorindex = 56
> Case "Fill Color (Dark Red)"
> mycolorindex = 9
> Case "Fill Color (Orange)"
> mycolorindex = 46
> Case "Fill Color (Dark Yellow)"
> mycolorindex = 12
> Case "Fill Color (Green)"
> mycolorindex = 10
> Case "Fill Color (Teal)"
> mycolorindex = 14
> Case "Fill Color (Blue)"
> mycolorindex = 5
> Case "Fill Color (Blue-Gray)"
> mycolorindex = 47
> Case "Fill Color (Gray-50%)"
> mycolorindex = 16
> Case "Fill Color (Red)"
> mycolorindex = 3
> Case "Fill Color (Light Orange)"
> mycolorindex = 45
> Case "Fill Color (Lime)"
> mycolorindex = 43
> Case "Fill Color (Sea Green)"
> mycolorindex = 50
> Case "Fill Color (Aqua)"
> mycolorindex = 42
> Case "Fill Color (Light Blue)"
> mycolorindex = 41
> Case "Fill Color (Violet)"
> mycolorindex = 13
> Case "Fill Color (Gray-40%)"
> mycolorindex = 48
> Case "Fill Color (Pink)"
> mycolorindex = 7
> Case "Fill Color (Gold)"
> mycolorindex = 44
> Case "Fill Color (Yellow)"
> mycolorindex = 6
> Case "Fill Color (Bright Green)"
> mycolorindex = 4
> Case "Fill Color (Turquoise)"
> mycolorindex = 8
> Case "Fill Color (Sky Blue)"
> mycolorindex = 33
> Case "Fill Color (Plum)"
> mycolorindex = 54
> Case "Fill Color (Gray-25%)"
> mycolorindex = 15
> Case "Fill Color (Rose)"
> mycolorindex = 38
> Case "Fill Color (Tan)"
> mycolorindex = 40
> Case "Fill Color (Light Yellow)"
> mycolorindex = 36
> Case "Fill Color (Light Green)"
> mycolorindex = 35
> Case "Fill Color (Light Turquoise)"
> mycolorindex = 34
> Case "Fill Color (Pale Blue)"
> mycolorindex = 37
> Case "Fill Color (Lavender)"
> mycolorindex = 39
> Case "Fill Color (White)"
> mycolorindex = 2
> Case Else
> mycolorindex = xlNone
> End Select
> If mycolorindex <> xlNone Then
> myrgb = ActiveWorkbook.Colors(mycolorindex)
> With Worksheets(1).Shapes(1).Fill
> .ForeColor.RGB = myrgb
> .Visible = True
> End With
> Else: Worksheets(1).Shapes(1).Fill.Visible = msoFalse
> End If
> End Sub
>
>
>
> "Steve Conary" <conarysr@comcast.net> wrote in message
> news:1102477773.393731.208600@c13g2000cwb.googlegroups.com...
> > I have a routine that adds a shape to a selected cell and I would like
> > to have the color of that shape be the last selected "fill" color (or
> > the default fill color if no change made since Excel was started). How
> > can I determine the active fill color from the "Fill Color" toolbar
> > using VBA? Seems like there should be a way to get that color value.
> > Thanks...
> > Steve
> >
>
>
>

Quantcast