Get Color from Color Fill on Formatting toolbar

Re: Get Active Window Title Bar Color

CommandBars("Formatting").Controls("[B][COLOR=Red]&Fill Color[/COLOR][/B]").TooltipText

doesn't work in xl97 (I confirmed the error). I think it's the part bolded in red that's the problem.

Yes Andy, I think when you read Chris' final post (which he posted while I was posting) then I think that despite what he said

Quote from chrisxs5

Correct Barbarr

to me earlier

Quote from Barbarr

I could be wrong and we'll have to get the OP to clarify but I read got the feeling that he wanted the colour to automatically match the colour of the active window title bar, based on what the user's colour scheme for Windows. This would be similar to selecting a colour on a userform based on the title bar colour.

it's whatever is pre-selected for the Color Fill icon on the Formatting toolbar.

Ain't terminology wonderful (but only if we all share the same meaning of it).

Re: Get Active Window Title Bar Color

Chris

I think this is quite an impractical idea.

The following code will return the current colour selected, surrounded by text.

Msgbox CommandBars("Formatting").Controls("&Fill Color").TooltipText

Here are the results I obtained.

Fill Color (Yellow)


Fill Color (Red)

The first thing you would need to do here is parse out the colour, then somehow, perhaps using a Select Case structure return the ColorIndex for the colour.

What is the actual purpose of what you want to do?

Re: Get Active Window Title Bar Color

Yes, as I was posing my last post and the light bulb fully lit (realized that through all the jumble it really was the Fill Color on the Formatting toolbar), the same thought crossed my mind Norie.

I mean the Fill Color could be set to No Fill so you would get no fill when you thought you were getting a color.

I guess I just fail to see the necessity of it but that's not my problem.

BTW Chris, the arrow you drew on your attached Color.xls file is only good if we also have our toolbars set up the same as you which is highly unlikely. Norie's idea of taking a picture of the icon in question was most useful. The most descriptive part of the file was describing the icon as a bucket.

Re: Get Active Window Title Bar Color

Not a problem in my xl97 version[vba]?application.CommandBars("Formatting").Controls("&Fill Color").TooltipText [/vba]
While parsing is certainly a hurdle I haven't yet found a clean way of determining to active color. Also no fill returns Fill Color (Automatic) so can be handled.

Re: Get Active Window Title Bar Color

I'm so confused with this thread :? . I know I copied and pasted the code but from which post now I'm not sure. Yes, it's working. I think I forgot to put debug.print in front of it which is probably what Chris did as well.

I notice that you have the question mark before application. So when I copy

?application.CommandBars("Formatting").Controls("&Fill Color").TooltipText

it changes to

Print Application.CommandBars("Formatting").Controls("&Fill Color").TooltipText

and I get a compile error (User-defined type not defined) but

Debug.Print Application.CommandBars("Formatting").Controls("&Fill Color").TooltipText

works just fine for me sending the results to the Intermediate Window.

Re: Get Color from Color Fill on Formatting toolbar

Sorry, I assumed the use of ? was know.

Debug.Print, used in code, will output to the immediate window. But you can also use ? directly in the immediate window.

Re: Get Color from Color Fill on Formatting toolbar

Hpefully this function will help explain what I was suggesting. It is incomplete, just some of the colours, but hey, you gotta do something yoursleves.

Function GetColourIndex()
Dim sText As String
Dim iPos As Long
Dim iColorIndex As Long


    sText = CommandBars("Formatting").Controls("&Fill Color").TooltipText
    iPos = InStr(sText, "(")
    If iPos > 0 Then
        sText = Mid(sText, iPos + 1, InStr(sText, ")") - iPos - 1)
        Select Case sText
            Case "Automatic": iColorIndex = xlColorIndexNone
            Case "Black": iColorIndex = 1
            Case "White": iColorIndex = 2
            Case "Red": iColorIndex = 3
            Case "Bright Green": iColorIndex = 4
            Case "Blue": iColorIndex = 5
            Case "Yellow": iColorIndex = 6
            Case "Pink": iColorIndex = 7
            Case "Turquoise": iColorIndex = 8
            Case "Green": iColorIndex = 10
            Case "Dark Blue": iColorIndex = 11
            Case "Dark Yellow": iColorIndex = 12
            Case "Violet": iColorIndex = 13
            Case "Teal": iColorIndex = 14
            Case "Gray - 25%": iColorIndex = 15
            Case "Gray - 50%": iColorIndex = 16
            Case "Light Turquoise": iColorIndex = 20
            Case "Dark Red": iColorIndex = 30
            Case "Light Blue": iColorIndex = 41
            Case "Sea Green": iColorIndex = 50
            Case "Brown": iColorIndex = 53
        End Select
        GetColourIndex = iColorIndex
    End If
End Function

Re: Get Color from Color Fill on Formatting toolbar

Quote from Andy Pope

Sorry, I assumed the use of ? was know.

Debug.Print, used in code, will output to the immediate window. But you can also use ? directly in the immediate window.

Okay, cool. Learning every day. :thanx:

Re: Get Color from Color Fill on Formatting toolbar

You could use a cell as the source of the colour. This method incorporates a UDF To get the ColorIndex of a cell & then colour the Activecell with the same Fill. In th example A1 is the source of the colour

'---------------------------------------------------------------------------------------
' Module    : Module2
' DateTime  : 04/11/2005 12:10
' Author    : royUK
' Website   : [url]www.excel-it.com[/url]
' Purpose   : use a cell as a colour palette
'---------------------------------------------------------------------------------------
Option Explicit


Function FindColIndex(cl)
    FindColIndex = cl.Interior.ColorIndex
End Function


Sub ColCell()
    Dim i As Integer
    i = FindColIndex(Range("A1"))
    ActiveCell.Interior.ColorIndex = i
End Sub

Re: Get Color from Color Fill on Formatting toolbar

Without the UDF you could use this code

Sub MatchCol()
  Selection.Interior.ColorIndex = Range("a1").Interior.ColorIndex
End Sub

Re: Get Color from Color Fill on Formatting toolbar

Quote from royUK

You could use a cell as the source of the colour. This method incorporates a UDF To get the ColorIndex of a cell & then colour the Activecell with the same Fill. In th example A1 is the source of the colour

Code

'---------------------------------------------------------------------------------------
' Module    : Module2
' DateTime  : 04/11/2005 12:10
' Author    : royUK
' Website   : [url]www.excel-it.com[/url]
' Purpose   : use a cell as a colour palette
'---------------------------------------------------------------------------------------
Option Explicit


Function FindColIndex(cl)
    FindColIndex = cl.Interior.ColorIndex
End Function


Sub ColCell()
    Dim i As Integer
    i = FindColIndex(Range("A1"))
    ActiveCell.Interior.ColorIndex = i
End Sub

Display More

Yeah, but absolutely nothing to do with the OPs question, which was to identify the default colour in the Fill Color button.

Re: Get Color from Color Fill on Formatting toolbar

Bob

I said earlier I was offering an alternative. These were just a few suggestions that might help!

Re: Get Color from Color Fill on Formatting toolbar

Quote from royUK

Bob

I said earlier I was offering an alternative. These were just a few suggestions that might help!

In my world, alternatives usually consitute a different way of arriving at the solution to the problem, not solving a different problem

Re: Get Color from Color Fill on Formatting toolbar

Quote from Bob Phillips

In my world, alternatives usually consitute a different way of arriving at the solution to the problem, not solving a different problem

And your point would be?????

Re: Use current Interior Color

Quote from chrisxs5

Here is the outline of what I am doing.

Select the color you want to use on the fill menu.
Check a box on the menu to turn feature on.
And now anywhere you click will be this color until the box is unchecked.

Ok. So my assumption is that you have a cell in which you tell the user to select his/her preferred interior color???

And then after setting a toggle to on that the selected cell would then be highlighted that color?

Or has this question been answered?

Re: Get Color from Color Fill on Formatting toolbar

Hi Barry

I think this one is solved. That was my interpretation, but it seems others know best!

Re: Get Color from Color Fill on Formatting toolbar

RoyUK,

Looking at thread #20 I'm not sure. I think there was a lot of misunderstanding as to what he wanted. I think that I can sum it up.

1. Using the "Fill Color" button on the Formatting Toolbar when you set a color that displayed color remains visible until the workbook is closed and reopened at which time it's reset to a default. Mine is yellow and I'm not sure how to reset it.

2. I'm thinking that the original post was this. Chris wanted to be able to when a cell was selected get this visible color and then use that to fill the selected cell. My thinking is he does presentations on say a projected screen and in order to see which cell he's selecting wants to highlight the selected cell.

3. There are 2 solutions to this problem ( I think ) 1 I know and the other I don't.

I'll attach a workbook using my method. It requires that there be a reference cell in which the desired color is set. For this use Roy's function. Though change it to a called procedure and remove the part where it resets the color ( Actually Roy I butchered you're function down to 3 lines, sorry )
Then using the selection_change event it clears all cells interior colors and then for the selected cell sets the interior color to match the targeted formatting.

Now the other method would be if someone can identify the control "Fill Color" button on the menu bar, and then extract the color that's visible. ( I hate menu bars so I leave that to the true experts )

The only difference between my workbook and the one that Chris posted is that I clear interior colors when I change selections but it would appear that he'll be able to work with that.

Re: Get Color from Color Fill on Formatting toolbar

Hi Barry - feel free to butcher my code - I cobbled it together from bits & pieces that I found.

I think this is one of those cases where the OP wasn't clear in his question. Anyway, in the tradition of Ozgrid & Excel - there's always more than one way to skin a cat (sorry Barbara) and the different solutions may prove useful to someone else, which is why I posted my suggestion originally.

Re: Get Color from Color Fill on Formatting toolbar

Quote from royUK

there's always more than one way to skin a cat (sorry Barbara) and the different solutions may prove useful to someone else, which is why I posted my suggestion originally.

First of all roy, there are some days I'd like to skin my little fur babies, so no offense (I've used the expression myself).

But I heartedly agree! Alternative solutions to solve the OPs problem and alternative ways to do something other than the way the OP is suggesting is what we're all about. Those discussion benefit not only the OP who can choose which way he/she wants to go but also others. The presentation of "alternatives" in the OzGrid world is the reason we prefer not to mark threads "SOLVED" since that tends to stifle healthy discussions.

Re: Get Color from Color Fill on Formatting toolbar

Combining Barry's sheet code with Bob's Function I think will give the solution required.