Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Steve Conary
 
Posts: n/a
Default Identifying the Active Fill Color

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

  #2   Report Post  
P Daulton
 
Posts: n/a
Default

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 worksheet. 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" wrote in message
oups.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



  #3   Report Post  
John Mansfield
 
Posts: n/a
Default

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 worksheet 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 worksheet. 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" wrote in message
oups.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




  #4   Report Post  
Steve Conary
 
Posts: n/a
Default

Thanks for the suggestions - 56 cases to find the right color index....
John's suggestion is also a possibility...I'll give them a try.

Still seems that there should be a more direct way to query the "Fill
Color" button.

Many thanks for your help!
Steve



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 worksheet. 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" wrote in message
oups.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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to change the color of all series in an excel chart in one go. Marielle Charts and Charting in Excel 2 May 3rd 23 07:45 PM
Fill screen with chartobject by zooming? Gunnar Johansson Charts and Charting in Excel 1 December 15th 04 04:41 AM
Fill area beneath a scatter plot JZip Charts and Charting in Excel 4 December 8th 04 02:59 PM
Is there a way to change the default shading color ? greg Excel Discussion (Misc queries) 5 December 1st 04 12:40 AM


All times are GMT +1. The time now is 12:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"