Is there a way to determine what the current / last used color index
number is from VBA? Instead of hard coding the colorindex or forcing
the user to choose a color, I would like to just pick up the color
that's currently in the toolbar? I've found the name:
Application.CommandBars("Formatting").Controls("Fi ll
Color").TooltipText
However, I'd like to get the ColorIndex number for the name. Any
ideas?

No, this is very difficult. We had a discussion on OzGrid a while back
(http://www.ozgrid.com/forum/showthread.php?t=41954) but it was hardly
satisfactory.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"JasonF" wrote in message
ups.com...
Is there a way to determine what the current / last used color index
number is from VBA? Instead of hard coding the colorindex or forcing
the user to choose a color, I would like to just pick up the color
that's currently in the toolbar? I've found the name:
Application.CommandBars("Formatting").Controls("Fi ll
Color").TooltipText
However, I'd like to get the ColorIndex number for the name. Any
ideas?

I just had a quick look and yes, it does not seem to be straight forward.

This control is of type msoControlSplitButtonPopup (= 13) and does not
support events, apparently. And calling .Execute causes the drop down,
rather than filling the .Selection.
I see no correlation between the ToolTipText and the colour shown, as it can
be completely wrong if colours are modified to custom colours, in XL2002
anyway.
e.g. I modified "Light Green" to a pinkish and the ToolTipText still shows
"Light Green" instead of maybe "Custom" or an RGB.

A quick look with Spy++ shows these messages when clicking the control to
fill the selected cell(s) with the visible colour:

<00425 00010554 P WM_LBUTTONDOWN fwKeys:MK_LBUTTON xPos:577 yPos:13
<00426 00010554 S
.................................................. ...........................
..............WM_NCHITTEST xPos:582 yPos:87
<00427 00010554 R
.................................................. ...........................
..............WM_NCHITTEST nHittest:HTCLIENT
<00428 00010554 S
.................................................. ...........................
..............WM_WINDOWPOSCHANGING lpwp:0012FA70
<00429 00010554 R
.................................................. ...........................
..............WM_WINDOWPOSCHANGING
<00430 00010554 S
.................................................. ...........................
..............WM_CAPTURECHANGED hwndNewCaptu00010554
<00431 00010554 R
.................................................. ...........................
..............WM_CAPTURECHANGED
<00432 00010554 P WM_SETCURSOR hwnd:00010554 nHittest:HTNOWHERE
wMouseMsg:0000
<00433 00010554 P WM_LBUTTONUP fwKeys:0000 xPos:577 yPos:13


So unless anyone has a better idea:
- Determine which of the above messages (if they are indeed correct) are
actually needed.
- Get the hwnd of the Formatting commandbar; FindWindow/FindWindowEx etc.
- SendMessage, using hwnd and the appropriate parameters.

Seems like a lot of work, so I would hope this is important to you <g.

NickHK

"Bob Phillips" wrote in message
...
No, this is very difficult. We had a discussion on OzGrid a while back
(http://www.ozgrid.com/forum/showthread.php?t=41954) but it was hardly
satisfactory.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"JasonF" wrote in message
ups.com...
Is there a way to determine what the current / last used color index
number is from VBA? Instead of hard coding the colorindex or forcing
the user to choose a color, I would like to just pick up the color
that's currently in the toolbar? I've found the name:
Application.CommandBars("Formatting").Controls("Fi ll
Color").TooltipText
However, I'd like to get the ColorIndex number for the name. Any
ideas?

Hi Nick,

I don't know a way to get the colour directly either. As you say with
customized colours the tooltip may be incorrect.

I've never managed to get SendMessage to work with toolbars. Another way
that should be possible is to get the colour of the pixel of the coloured
bar in the Fill icon.

Determine the toolbar that has the Fill Color icon/toolbar, typically
"Formatting"
Get the screen pixel position of the icon relative to top/left of the
container tlbr, then offset to a pixel in the 'coloured bar' in the icon,x/y
Get hwnd then the dc of the toolbar
GetPixel dc, x,y
ReleaseDC

Simple <g

Regards,
Peter T


"NickHK" wrote in message
...
I just had a quick look and yes, it does not seem to be straight forward.

This control is of type msoControlSplitButtonPopup (= 13) and does not
support events, apparently. And calling .Execute causes the drop down,
rather than filling the .Selection.
I see no correlation between the ToolTipText and the colour shown, as it
can
be completely wrong if colours are modified to custom colours, in XL2002
anyway.
e.g. I modified "Light Green" to a pinkish and the ToolTipText still shows
"Light Green" instead of maybe "Custom" or an RGB.

A quick look with Spy++ shows these messages when clicking the control to
fill the selected cell(s) with the visible colour:

<00425 00010554 P WM_LBUTTONDOWN fwKeys:MK_LBUTTON xPos:577 yPos:13
<00426 00010554 S

.................................................. ...........................
.............WM_NCHITTEST xPos:582 yPos:87
<00427 00010554 R

.................................................. ...........................
.............WM_NCHITTEST nHittest:HTCLIENT
<00428 00010554 S

.................................................. ...........................
.............WM_WINDOWPOSCHANGING lpwp:0012FA70
<00429 00010554 R

.................................................. ...........................
.............WM_WINDOWPOSCHANGING
<00430 00010554 S

.................................................. ...........................
.............WM_CAPTURECHANGED hwndNewCaptu00010554
<00431 00010554 R

.................................................. ...........................
.............WM_CAPTURECHANGED
<00432 00010554 P WM_SETCURSOR hwnd:00010554 nHittest:HTNOWHERE
wMouseMsg:0000
<00433 00010554 P WM_LBUTTONUP fwKeys:0000 xPos:577 yPos:13


So unless anyone has a better idea:
- Determine which of the above messages (if they are indeed correct) are
actually needed.
- Get the hwnd of the Formatting commandbar; FindWindow/FindWindowEx etc.
- SendMessage, using hwnd and the appropriate parameters.

Seems like a lot of work, so I would hope this is important to you <g.

NickHK

"Bob Phillips" wrote in message
...
No, this is very difficult. We had a discussion on OzGrid a while back
(http://www.ozgrid.com/forum/showthread.php?t=41954) but it was hardly
satisfactory.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"JasonF" wrote in message
ups.com...
Is there a way to determine what the current / last used color index
number is from VBA? Instead of hard coding the colorindex or forcing
the user to choose a color, I would like to just pick up the color
that's currently in the toolbar? I've found the name:
Application.CommandBars("Formatting").Controls("Fi ll
Color").TooltipText
However, I'd like to get the ColorIndex number for the name. Any
ideas?

Peter ,
I never tried the SendMessage route, so can't say really.

I thought about the GetPixel way, but considered the SendMessage less
involved - if it works.

If I have time over the weekend I may have a look at both.

NickHK

"Peter T" <peter_t@discussions wrote in message
...
Hi Nick,

I don't know a way to get the colour directly either. As you say with
customized colours the tooltip may be incorrect.

I've never managed to get SendMessage to work with toolbars. Another way
that should be possible is to get the colour of the pixel of the coloured
bar in the Fill icon.

Determine the toolbar that has the Fill Color icon/toolbar, typically
"Formatting"
Get the screen pixel position of the icon relative to top/left of the
container tlbr, then offset to a pixel in the 'coloured bar' in the
icon,x/y
Get hwnd then the dc of the toolbar
GetPixel dc, x,y
ReleaseDC

Simple <g

Regards,
Peter T


"NickHK" wrote in message
...
I just had a quick look and yes, it does not seem to be straight
forward.

This control is of type msoControlSplitButtonPopup (= 13) and does not
support events, apparently. And calling .Execute causes the drop down,
rather than filling the .Selection.
I see no correlation between the ToolTipText and the colour shown, as it
can
be completely wrong if colours are modified to custom colours, in XL2002
anyway.
e.g. I modified "Light Green" to a pinkish and the ToolTipText still
shows
"Light Green" instead of maybe "Custom" or an RGB.

A quick look with Spy++ shows these messages when clicking the control
to
fill the selected cell(s) with the visible colour:

<00425 00010554 P WM_LBUTTONDOWN fwKeys:MK_LBUTTON xPos:577
yPos:13
<00426 00010554 S


.................................................. ...........................
.............WM_NCHITTEST xPos:582 yPos:87
<00427 00010554 R


.................................................. ...........................
.............WM_NCHITTEST nHittest:HTCLIENT
<00428 00010554 S


.................................................. ...........................
.............WM_WINDOWPOSCHANGING lpwp:0012FA70
<00429 00010554 R


.................................................. ...........................
.............WM_WINDOWPOSCHANGING
<00430 00010554 S


.................................................. ...........................
.............WM_CAPTURECHANGED hwndNewCaptu00010554
<00431 00010554 R


.................................................. ...........................
.............WM_CAPTURECHANGED
<00432 00010554 P WM_SETCURSOR hwnd:00010554 nHittest:HTNOWHERE
wMouseMsg:0000
<00433 00010554 P WM_LBUTTONUP fwKeys:0000 xPos:577 yPos:13


So unless anyone has a better idea:
- Determine which of the above messages (if they are indeed correct) are
actually needed.
- Get the hwnd of the Formatting commandbar; FindWindow/FindWindowEx
etc.
- SendMessage, using hwnd and the appropriate parameters.

Seems like a lot of work, so I would hope this is important to you <g.

NickHK

"Bob Phillips" wrote in message
...
No, this is very difficult. We had a discussion on OzGrid a while back
(http://www.ozgrid.com/forum/showthread.php?t=41954) but it was hardly
satisfactory.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"JasonF" wrote in message
ups.com...
Is there a way to determine what the current / last used color index
number is from VBA? Instead of hard coding the colorindex or
forcing
the user to choose a color, I would like to just pick up the color
that's currently in the toolbar? I've found the name:
Application.CommandBars("Formatting").Controls("Fi ll
Color").TooltipText
However, I'd like to get the ColorIndex number for the name. Any
ideas?

Hi Nick,

I'll leave the SendMessage idea for you but I had a quick go with GetPixel,
with mixed results.

As toolbar controls return cooridantes in pixels relative to top-left of
screen, can get the pixel directly from the desktop. This makes it very
simple but of course will only work if the fill control is visible on the
screen -

Private Declare Function GetDC Lib "user32" ( _
ByVal hwnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" ( _
ByVal hwnd As Long, ByVal hdc As Long) As Long

'Private Declare Function GetDeviceCaps Lib "gdi32" ( _
' ByVal hdc As Long, ByVal nIndex As Long) As Long
'Private Const POINTS_PER_INCH As Long = 72

Private Declare Function FindWindowA Lib "user32" _
(ByVal lpClasssName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function GetPixel Lib "gdi32" ( _
ByVal hdc As Long, _
ByVal x As Long, ByVal y As Long) As Long


Function GetFillColorPixel() As Long
'' for this to work the Fill Color control MUST be visible on
'' the screen, not say hidden by the VBE or some dialog or window
Dim x As Long, y As Long, clr As Long
Dim dc As Long
Dim ctr As CommandBarPopup

Set ctr = Application.CommandBars.FindControl(ID:=1691)
'Debug.Print ctr.Caption ' &Fill Color

'left & right returns pixel coordinates from top left of screen
' add an offsets 8 & 16 to the coloured bar
x = ctr.Left + 8
y = ctr.Top + 16

dc = GetDC(0)
clr = GetPixel(dc, x, y)
ReleaseDC 0, dc

GetFillColorPixel = clr

End Function

Sub test3()
Dim clr As Long
clr = GetFillColorPixel
ActiveCell.Interior.Color = clr
MsgBox clr & vbCr & ActiveCell.Interior.ColorIndex
End Sub

The above seems to work fine for me, subject the control being in view.
What I originally had in mind was to get the pixel from its container
toolbar window, which if it works, does not require the control to be
visible on the screen.

Set ctr = Application.CommandBars.FindControl(ID:=1691)
sTlbrName = ctr.Parent.Name
Set cbr = Application.CommandBars(sTlbrName) ' typically "Formatting"
cbr.Visible = True ' required to get it's hwn
hwn = FindWindowA("MsoCommandbar", sTlbrName
dc = getDC(hwn)

Apart from needing the 'cbr.Visible' the toolbar needs to be not docked to
find its window handle, at least for me. Anyway did all that but GetPixel is
returning -1 for some reason.

Perhaps your SendMessage will be more reliable !

Regards,
Peter T

"NickHK" wrote in message
...
Peter ,
I never tried the SendMessage route, so can't say really.

I thought about the GetPixel way, but considered the SendMessage less
involved - if it works.

If I have time over the weekend I may have a look at both.

NickHK

"Peter T" <peter_t@discussions wrote in message
...
Hi Nick,

I don't know a way to get the colour directly either. As you say with
customized colours the tooltip may be incorrect.

I've never managed to get SendMessage to work with toolbars. Another way
that should be possible is to get the colour of the pixel of the
coloured
bar in the Fill icon.

Determine the toolbar that has the Fill Color icon/toolbar, typically
"Formatting"
Get the screen pixel position of the icon relative to top/left of the
container tlbr, then offset to a pixel in the 'coloured bar' in the
icon,x/y
Get hwnd then the dc of the toolbar
GetPixel dc, x,y
ReleaseDC

Simple <g

Regards,
Peter T


"NickHK" wrote in message
...
I just had a quick look and yes, it does not seem to be straight
forward.

This control is of type msoControlSplitButtonPopup (= 13) and does not
support events, apparently. And calling .Execute causes the drop down,
rather than filling the .Selection.
I see no correlation between the ToolTipText and the colour shown, as
it
can
be completely wrong if colours are modified to custom colours, in
XL2002
anyway.
e.g. I modified "Light Green" to a pinkish and the ToolTipText still
shows
"Light Green" instead of maybe "Custom" or an RGB.

A quick look with Spy++ shows these messages when clicking the control
to
fill the selected cell(s) with the visible colour:

<00425 00010554 P WM_LBUTTONDOWN fwKeys:MK_LBUTTON xPos:577
yPos:13
<00426 00010554 S



.................................................. ...........................
.............WM_NCHITTEST xPos:582 yPos:87
<00427 00010554 R



.................................................. ...........................
.............WM_NCHITTEST nHittest:HTCLIENT
<00428 00010554 S



.................................................. ...........................
.............WM_WINDOWPOSCHANGING lpwp:0012FA70
<00429 00010554 R



.................................................. ...........................
.............WM_WINDOWPOSCHANGING
<00430 00010554 S



.................................................. ...........................
.............WM_CAPTURECHANGED hwndNewCaptu00010554
<00431 00010554 R



.................................................. ...........................
.............WM_CAPTURECHANGED
<00432 00010554 P WM_SETCURSOR hwnd:00010554 nHittest:HTNOWHERE
wMouseMsg:0000
<00433 00010554 P WM_LBUTTONUP fwKeys:0000 xPos:577 yPos:13


So unless anyone has a better idea:
- Determine which of the above messages (if they are indeed correct)
are
actually needed.
- Get the hwnd of the Formatting commandbar; FindWindow/FindWindowEx
etc.
- SendMessage, using hwnd and the appropriate parameters.

Seems like a lot of work, so I would hope this is important to you
<g.

NickHK

"Bob Phillips" wrote in message
...
No, this is very difficult. We had a discussion on OzGrid a while
back
(http://www.ozgrid.com/forum/showthread.php?t=41954) but it was
hardly
satisfactory.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"JasonF" wrote in message
ups.com...
Is there a way to determine what the current / last used color
index
number is from VBA? Instead of hard coding the colorindex or
forcing
the user to choose a color, I would like to just pick up the color
that's currently in the toolbar? I've found the name:
Application.CommandBars("Formatting").Controls("Fi ll
Color").TooltipText
However, I'd like to get the ColorIndex number for the name. Any
ideas?

Indeed you can get the colour name from the tooltip, so can get the colour
value from a lookup table (and/or index if the tables are in correct order -
unlike below).

Following is significantly reduced from something else I have. As written
assumes a default palette (for other reasons the arrays are in colour value
order). For your purposes it would be better to arrange in color index
order, then could return the index directly with the lookup.

The reverse lookup, colour name from color value (see GetColourName), does
not require the palette colours to be in default positions.

If the colour has been customized the tooltip name doesn't always update
from a colour name to "Custom", so could return incorrectly (other ways to
verify that). Also, is say default red is in the default posistion for
default blue, the tooltip may continue to read Blue. There are other issues
too, IOW caveats!

Function CvalCNames(nClrVal As Long, sName As String) As Boolean
Dim i As Long
Dim vN, vS

' 46/56 colours, excl the 10 duplicate chart colours
vN = Array(xlAutomatic, 0, 128&, 255&, 13056&, 13107&, 13209&, 26367&, _
32768, 32896, 39423, 52377, 52479, 65280, 65535, 3355443, 6684774, 6697728,
_
6697881, 6723891, 8388608, 8388736, 8421376, 8421504, 8421631, 9868950, _
10040115, 10053222, 10079487, 10092543, 12632256, 13395456, 13408767, _
13421619, 13434828, 13434879, 16711680, 16711935, 16737843, 16751001, _
16751052, 16763904, 16764057, 16764108, 16776960, 16777164, 16777215)

vS = Array("Automatic", "Black", "Dark Red", "Red", "Dark Green", _
"Olive Green", "Brown", "Orange", "Green", "Dark Yellow", "Light Orange", _
"Lime", "Gold", "Bright Green", "Yellow", "Gray-80%", "Dark Purple", _
"Dark Teal", "Plum", "Sea Green", "Dark Blue", "Violet", "Teal", _
"Gray-50%", "Coral", "Gray-40%", "Indigo", "Blue-Gray", "Tan", _
"Light Yellow", "Gray-25%", "Ocean Blue", "Rose", "Aqua", "Light Green", _
"Ivory", "Blue", "Pink", "Light Blue", "Periwinkle", "Lavender", _
"Sky Blue", "Pale Blue", "Ice Blue", "Turqoise", "Light Turquiose", "White")

If Len(sName) Then
For i = 0 To UBound(vS)
If sName = vS(i) Then
nClrVal = vN(i)
Exit For
End If
Next
Else
For i = 0 To UBound(vN)
If nClrVal = vN(i) Then
sName = vS(i)
Exit For
End If
Next
End If

CvalCNames = i <= UBound(vN)

End Function

Sub ApplyToolBarFillColor()
Dim sName As String, nClrValue As Long

sName = Application.CommandBars("Formatting"). _
Controls("Fill Color").TooltipText

sName = Mid(sName, InStr(1, sName, "(") + 1, 30)
sName = Left(sName, Len(sName) - 1)

If CvalCNames(nClrValue, sName) Then
If nClrValue < 1 Then
ActiveCell.Interior.ColorIndex = xlAutomatic
Else
ActiveCell.Interior.Color = nClrValue
End If
Else
MsgBox "Custom or non-English colour names"
End If

GetColourName

End Sub

Sub GetColourName()
Dim idx As Long, sName As String, nClrValue As Long
With ActiveCell.Interior
nClrValue = .Color
idx = .ColorIndex
End With

If CvalCNames(nClrValue, sName) Then
MsgBox idx & " " & sName
Else
MsgBox "Custom or non-English colour names"
End If

End Sub

Regards,
Peter T


"JasonF" wrote in message
ups.com...
Is there a way to determine what the current / last used color index
number is from VBA? Instead of hard coding the colorindex or forcing
the user to choose a color, I would like to just pick up the color
that's currently in the toolbar? I've found the name:
Application.CommandBars("Formatting").Controls("Fi ll
Color").TooltipText
However, I'd like to get the ColorIndex number for the name. Any
ideas?

Peter,
If the colour has been customized the tooltip name doesn't always update
from a colour name to "Custom", so could return incorrectly
This is what I find. ToolTipsText for custom colours are never updated.

(other ways to verify that).
Care to elaborate ?

NickHK

"Peter T" <peter_t@discussions wrote in message
...
Indeed you can get the colour name from the tooltip, so can get the colour
value from a lookup table (and/or index if the tables are in correct
order -
unlike below).

Following is significantly reduced from something else I have. As written
assumes a default palette (for other reasons the arrays are in colour
value
order). For your purposes it would be better to arrange in color index
order, then could return the index directly with the lookup.

The reverse lookup, colour name from color value (see GetColourName), does
not require the palette colours to be in default positions.

If the colour has been customized the tooltip name doesn't always update
from a colour name to "Custom", so could return incorrectly (other ways to
verify that). Also, is say default red is in the default posistion for
default blue, the tooltip may continue to read Blue. There are other
issues
too, IOW caveats!

Function CvalCNames(nClrVal As Long, sName As String) As Boolean
Dim i As Long
Dim vN, vS

' 46/56 colours, excl the 10 duplicate chart colours
vN = Array(xlAutomatic, 0, 128&, 255&, 13056&, 13107&, 13209&, 26367&, _
32768, 32896, 39423, 52377, 52479, 65280, 65535, 3355443, 6684774,
6697728,
_
6697881, 6723891, 8388608, 8388736, 8421376, 8421504, 8421631, 9868950, _
10040115, 10053222, 10079487, 10092543, 12632256, 13395456, 13408767, _
13421619, 13434828, 13434879, 16711680, 16711935, 16737843, 16751001, _
16751052, 16763904, 16764057, 16764108, 16776960, 16777164, 16777215)

vS = Array("Automatic", "Black", "Dark Red", "Red", "Dark Green", _
"Olive Green", "Brown", "Orange", "Green", "Dark Yellow", "Light Orange",
_
"Lime", "Gold", "Bright Green", "Yellow", "Gray-80%", "Dark Purple", _
"Dark Teal", "Plum", "Sea Green", "Dark Blue", "Violet", "Teal", _
"Gray-50%", "Coral", "Gray-40%", "Indigo", "Blue-Gray", "Tan", _
"Light Yellow", "Gray-25%", "Ocean Blue", "Rose", "Aqua", "Light Green", _
"Ivory", "Blue", "Pink", "Light Blue", "Periwinkle", "Lavender", _
"Sky Blue", "Pale Blue", "Ice Blue", "Turqoise", "Light Turquiose",
"White")

If Len(sName) Then
For i = 0 To UBound(vS)
If sName = vS(i) Then
nClrVal = vN(i)
Exit For
End If
Next
Else
For i = 0 To UBound(vN)
If nClrVal = vN(i) Then
sName = vS(i)
Exit For
End If
Next
End If

CvalCNames = i <= UBound(vN)

End Function

Sub ApplyToolBarFillColor()
Dim sName As String, nClrValue As Long

sName = Application.CommandBars("Formatting"). _
Controls("Fill Color").TooltipText

sName = Mid(sName, InStr(1, sName, "(") + 1, 30)
sName = Left(sName, Len(sName) - 1)

If CvalCNames(nClrValue, sName) Then
If nClrValue < 1 Then
ActiveCell.Interior.ColorIndex = xlAutomatic
Else
ActiveCell.Interior.Color = nClrValue
End If
Else
MsgBox "Custom or non-English colour names"
End If

GetColourName

End Sub

Sub GetColourName()
Dim idx As Long, sName As String, nClrValue As Long
With ActiveCell.Interior
nClrValue = .Color
idx = .ColorIndex
End With

If CvalCNames(nClrValue, sName) Then
MsgBox idx & " " & sName
Else
MsgBox "Custom or non-English colour names"
End If

End Sub

Regards,
Peter T


"JasonF" wrote in message
ups.com...
Is there a way to determine what the current / last used color index
number is from VBA? Instead of hard coding the colorindex or forcing
the user to choose a color, I would like to just pick up the color
that's currently in the toolbar? I've found the name:
Application.CommandBars("Formatting").Controls("Fi ll
Color").TooltipText
However, I'd like to get the ColorIndex number for the name. Any
ideas?

Hi again,

If the colour has been customized the tooltip name doesn't always update
from a colour name to "Custom", so could return incorrectly
This is what I find. ToolTipsText for custom colours are never updated.

I find they normally update eventually and Tooltip reads "Custom", perhaps
after a save. If you drag the control off the toolbar the names immediately
update, at least for me, to read "Scheme Color".

(other ways to verify that).
Care to elaborate ?

Check if the palette is customized, and if so which colours

' print the default palette to the immediate window
Sub DefaualtPalette()
Dim i&, s$, P
Workbooks.Add ' to be sure it has a default palette
s = "arrDefPal = Array("
P = ActiveWorkbook.Colors
For i = 1 To 56
s = s & P(i)
If i = 56 Then
s = s & ")"
Else
s = s & ", "
End If
Next
Debug.Print s
End Sub

Function HasCustomPalette(wb As Workbook, bArr() As Boolean)
Dim arrDefPal, P, bFlag As Boolean

' arrDefPal = Array(0, 16777215, 255, etc.... ' copy the printout from
DefaualtPalette()
P = wb.Colors

For i = 1 To 56
If P(i) < arrDefPal(i - 1) Then
bArr(i) = True
bFlag = True
End If
Next

HasCustomPalette = bFlag

End Function

Sub test()
Dim bArrCustom(1 To 56) As Boolean
Dim bRes As Boolean

ActiveWorkbook.Colors(6) = 12345678
bRes = HasCustomPalette(ActiveWorkbook, bArrCustom)

If bRes Then
MsgBox "wb palette is customized" & vbCr & _
"ColorIndex 6 customized " & bArrCustom(6)
Else
MsgBox "Default palette"
End If

End Sub

Adapt my previous routine and place the colour-name & colorvalue lookup
tables in colorindex order, everything required for the task is available.

Regards,
Peter T


"NickHK" wrote in message
...
Peter,
If the colour has been customized the tooltip name doesn't always update
from a colour name to "Custom", so could return incorrectly
This is what I find. ToolTipsText for custom colours are never updated.

(other ways to verify that).
Care to elaborate ?

NickHK

"Peter T" <peter_t@discussions wrote in message
...
Indeed you can get the colour name from the tooltip, so can get the
colour
value from a lookup table (and/or index if the tables are in correct
order -
unlike below).

Following is significantly reduced from something else I have. As
written
assumes a default palette (for other reasons the arrays are in colour
value
order). For your purposes it would be better to arrange in color index
order, then could return the index directly with the lookup.

The reverse lookup, colour name from color value (see GetColourName),
does
not require the palette colours to be in default positions.

If the colour has been customized the tooltip name doesn't always update
from a colour name to "Custom", so could return incorrectly (other ways
to
verify that). Also, is say default red is in the default posistion for
default blue, the tooltip may continue to read Blue. There are other
issues
too, IOW caveats!

Function CvalCNames(nClrVal As Long, sName As String) As Boolean
Dim i As Long
Dim vN, vS

' 46/56 colours, excl the 10 duplicate chart colours
vN = Array(xlAutomatic, 0, 128&, 255&, 13056&, 13107&, 13209&, 26367&, _
32768, 32896, 39423, 52377, 52479, 65280, 65535, 3355443, 6684774,
6697728,
_
6697881, 6723891, 8388608, 8388736, 8421376, 8421504, 8421631, 9868950,
_
10040115, 10053222, 10079487, 10092543, 12632256, 13395456, 13408767, _
13421619, 13434828, 13434879, 16711680, 16711935, 16737843, 16751001, _
16751052, 16763904, 16764057, 16764108, 16776960, 16777164, 16777215)

vS = Array("Automatic", "Black", "Dark Red", "Red", "Dark Green", _
"Olive Green", "Brown", "Orange", "Green", "Dark Yellow", "Light
Orange",
_
"Lime", "Gold", "Bright Green", "Yellow", "Gray-80%", "Dark Purple", _
"Dark Teal", "Plum", "Sea Green", "Dark Blue", "Violet", "Teal", _
"Gray-50%", "Coral", "Gray-40%", "Indigo", "Blue-Gray", "Tan", _
"Light Yellow", "Gray-25%", "Ocean Blue", "Rose", "Aqua", "Light Green",
_
"Ivory", "Blue", "Pink", "Light Blue", "Periwinkle", "Lavender", _
"Sky Blue", "Pale Blue", "Ice Blue", "Turqoise", "Light Turquiose",
"White")

If Len(sName) Then
For i = 0 To UBound(vS)
If sName = vS(i) Then
nClrVal = vN(i)
Exit For
End If
Next
Else
For i = 0 To UBound(vN)
If nClrVal = vN(i) Then
sName = vS(i)
Exit For
End If
Next
End If

CvalCNames = i <= UBound(vN)

End Function

Sub ApplyToolBarFillColor()
Dim sName As String, nClrValue As Long

sName = Application.CommandBars("Formatting"). _
Controls("Fill Color").TooltipText

sName = Mid(sName, InStr(1, sName, "(") + 1, 30)
sName = Left(sName, Len(sName) - 1)

If CvalCNames(nClrValue, sName) Then
If nClrValue < 1 Then
ActiveCell.Interior.ColorIndex = xlAutomatic
Else
ActiveCell.Interior.Color = nClrValue
End If
Else
MsgBox "Custom or non-English colour names"
End If

GetColourName

End Sub

Sub GetColourName()
Dim idx As Long, sName As String, nClrValue As Long
With ActiveCell.Interior
nClrValue = .Color
idx = .ColorIndex
End With

If CvalCNames(nClrValue, sName) Then
MsgBox idx & " " & sName
Else
MsgBox "Custom or non-English colour names"
End If

End Sub

Regards,
Peter T


"JasonF" wrote in message
ups.com...
Is there a way to determine what the current / last used color index
number is from VBA? Instead of hard coding the colorindex or forcing
the user to choose a color, I would like to just pick up the color
that's currently in the toolbar? I've found the name:
Application.CommandBars("Formatting").Controls("Fi ll
Color").TooltipText
However, I'd like to get the ColorIndex number for the name. Any
ideas?

Peter,
OK, thanks for that.
Yes, undocking the palette, then closing it sets the ToolTipText to
"Custom", but then if you do anything and come back, it has reset to the
default. Agghhh.

All this does seem to be unnecessarily complex, especially as there is
always a set of 56 colours.

Thinking about it now, SendMessage is probably no better, as you have to
include the mouse click coords in the parameters. I'll check out that code
you posted.

NickHK

"Peter T" <peter_t@discussions wrote in message
...
Hi again,

If the colour has been customized the tooltip name doesn't always
update
from a colour name to "Custom", so could return incorrectly
This is what I find. ToolTipsText for custom colours are never updated.

I find they normally update eventually and Tooltip reads "Custom", perhaps
after a save. If you drag the control off the toolbar the names
immediately
update, at least for me, to read "Scheme Color".

(other ways to verify that).
Care to elaborate ?

Check if the palette is customized, and if so which colours

' print the default palette to the immediate window
Sub DefaualtPalette()
Dim i&, s$, P
Workbooks.Add ' to be sure it has a default palette
s = "arrDefPal = Array("
P = ActiveWorkbook.Colors
For i = 1 To 56
s = s & P(i)
If i = 56 Then
s = s & ")"
Else
s = s & ", "
End If
Next
Debug.Print s
End Sub

Function HasCustomPalette(wb As Workbook, bArr() As Boolean)
Dim arrDefPal, P, bFlag As Boolean

' arrDefPal = Array(0, 16777215, 255, etc.... ' copy the printout from
DefaualtPalette()
P = wb.Colors

For i = 1 To 56
If P(i) < arrDefPal(i - 1) Then
bArr(i) = True
bFlag = True
End If
Next

HasCustomPalette = bFlag

End Function

Sub test()
Dim bArrCustom(1 To 56) As Boolean
Dim bRes As Boolean

ActiveWorkbook.Colors(6) = 12345678
bRes = HasCustomPalette(ActiveWorkbook, bArrCustom)

If bRes Then
MsgBox "wb palette is customized" & vbCr & _
"ColorIndex 6 customized " & bArrCustom(6)
Else
MsgBox "Default palette"
End If

End Sub

Adapt my previous routine and place the colour-name & colorvalue lookup
tables in colorindex order, everything required for the task is available.

Regards,
Peter T


"NickHK" wrote in message
...
Peter,
If the colour has been customized the tooltip name doesn't always
update
from a colour name to "Custom", so could return incorrectly
This is what I find. ToolTipsText for custom colours are never updated.

(other ways to verify that).
Care to elaborate ?

NickHK

"Peter T" <peter_t@discussions wrote in message
...
Indeed you can get the colour name from the tooltip, so can get the
colour
value from a lookup table (and/or index if the tables are in correct
order -
unlike below).

Following is significantly reduced from something else I have. As
written
assumes a default palette (for other reasons the arrays are in colour
value
order). For your purposes it would be better to arrange in color index
order, then could return the index directly with the lookup.

The reverse lookup, colour name from color value (see GetColourName),
does
not require the palette colours to be in default positions.

If the colour has been customized the tooltip name doesn't always
update
from a colour name to "Custom", so could return incorrectly (other
ways
to
verify that). Also, is say default red is in the default posistion for
default blue, the tooltip may continue to read Blue. There are other
issues
too, IOW caveats!

Function CvalCNames(nClrVal As Long, sName As String) As Boolean
Dim i As Long
Dim vN, vS

' 46/56 colours, excl the 10 duplicate chart colours
vN = Array(xlAutomatic, 0, 128&, 255&, 13056&, 13107&, 13209&, 26367&,
_
32768, 32896, 39423, 52377, 52479, 65280, 65535, 3355443, 6684774,
6697728,
_
6697881, 6723891, 8388608, 8388736, 8421376, 8421504, 8421631,
9868950,
_
10040115, 10053222, 10079487, 10092543, 12632256, 13395456, 13408767,
_
13421619, 13434828, 13434879, 16711680, 16711935, 16737843, 16751001,
_
16751052, 16763904, 16764057, 16764108, 16776960, 16777164, 16777215)

vS = Array("Automatic", "Black", "Dark Red", "Red", "Dark Green", _
"Olive Green", "Brown", "Orange", "Green", "Dark Yellow", "Light
Orange",
_
"Lime", "Gold", "Bright Green", "Yellow", "Gray-80%", "Dark Purple", _
"Dark Teal", "Plum", "Sea Green", "Dark Blue", "Violet", "Teal", _
"Gray-50%", "Coral", "Gray-40%", "Indigo", "Blue-Gray", "Tan", _
"Light Yellow", "Gray-25%", "Ocean Blue", "Rose", "Aqua", "Light
Green",
_
"Ivory", "Blue", "Pink", "Light Blue", "Periwinkle", "Lavender", _
"Sky Blue", "Pale Blue", "Ice Blue", "Turqoise", "Light Turquiose",
"White")

If Len(sName) Then
For i = 0 To UBound(vS)
If sName = vS(i) Then
nClrVal = vN(i)
Exit For
End If
Next
Else
For i = 0 To UBound(vN)
If nClrVal = vN(i) Then
sName = vS(i)
Exit For
End If
Next
End If

CvalCNames = i <= UBound(vN)

End Function

Sub ApplyToolBarFillColor()
Dim sName As String, nClrValue As Long

sName = Application.CommandBars("Formatting"). _
Controls("Fill Color").TooltipText

sName = Mid(sName, InStr(1, sName, "(") + 1, 30)
sName = Left(sName, Len(sName) - 1)

If CvalCNames(nClrValue, sName) Then
If nClrValue < 1 Then
ActiveCell.Interior.ColorIndex = xlAutomatic
Else
ActiveCell.Interior.Color = nClrValue
End If
Else
MsgBox "Custom or non-English colour names"
End If

GetColourName

End Sub

Sub GetColourName()
Dim idx As Long, sName As String, nClrValue As Long
With ActiveCell.Interior
nClrValue = .Color
idx = .ColorIndex
End With

If CvalCNames(nClrValue, sName) Then
MsgBox idx & " " & sName
Else
MsgBox "Custom or non-English colour names"
End If

End Sub

Regards,
Peter T


"JasonF" wrote in message
ups.com...
Is there a way to determine what the current / last used color index
number is from VBA? Instead of hard coding the colorindex or
forcing
the user to choose a color, I would like to just pick up the color
that's currently in the toolbar? I've found the name:
Application.CommandBars("Formatting").Controls("Fi ll
Color").TooltipText
However, I'd like to get the ColorIndex number for the name. Any
ideas?

And what happens in 2007?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"NickHK" wrote in message
...
Peter,
OK, thanks for that.
Yes, undocking the palette, then closing it sets the ToolTipText to
"Custom", but then if you do anything and come back, it has reset to the
default. Agghhh.

All this does seem to be unnecessarily complex, especially as there is
always a set of 56 colours.

Thinking about it now, SendMessage is probably no better, as you have to
include the mouse click coords in the parameters. I'll check out that code
you posted.

NickHK

"Peter T" <peter_t@discussions wrote in message
...
Hi again,

If the colour has been customized the tooltip name doesn't always
update
from a colour name to "Custom", so could return incorrectly
This is what I find. ToolTipsText for custom colours are never updated.

I find they normally update eventually and Tooltip reads "Custom",
perhaps
after a save. If you drag the control off the toolbar the names
immediately
update, at least for me, to read "Scheme Color".

(other ways to verify that).
Care to elaborate ?

Check if the palette is customized, and if so which colours

' print the default palette to the immediate window
Sub DefaualtPalette()
Dim i&, s$, P
Workbooks.Add ' to be sure it has a default palette
s = "arrDefPal = Array("
P = ActiveWorkbook.Colors
For i = 1 To 56
s = s & P(i)
If i = 56 Then
s = s & ")"
Else
s = s & ", "
End If
Next
Debug.Print s
End Sub

Function HasCustomPalette(wb As Workbook, bArr() As Boolean)
Dim arrDefPal, P, bFlag As Boolean

' arrDefPal = Array(0, 16777215, 255, etc.... ' copy the printout from
DefaualtPalette()
P = wb.Colors

For i = 1 To 56
If P(i) < arrDefPal(i - 1) Then
bArr(i) = True
bFlag = True
End If
Next

HasCustomPalette = bFlag

End Function

Sub test()
Dim bArrCustom(1 To 56) As Boolean
Dim bRes As Boolean

ActiveWorkbook.Colors(6) = 12345678
bRes = HasCustomPalette(ActiveWorkbook, bArrCustom)

If bRes Then
MsgBox "wb palette is customized" & vbCr & _
"ColorIndex 6 customized " & bArrCustom(6)
Else
MsgBox "Default palette"
End If

End Sub

Adapt my previous routine and place the colour-name & colorvalue lookup
tables in colorindex order, everything required for the task is
available.

Regards,
Peter T


"NickHK" wrote in message
...
Peter,
If the colour has been customized the tooltip name doesn't always
update
from a colour name to "Custom", so could return incorrectly
This is what I find. ToolTipsText for custom colours are never updated.

(other ways to verify that).
Care to elaborate ?

NickHK

"Peter T" <peter_t@discussions wrote in message
...
Indeed you can get the colour name from the tooltip, so can get the
colour
value from a lookup table (and/or index if the tables are in correct
order -
unlike below).

Following is significantly reduced from something else I have. As
written
assumes a default palette (for other reasons the arrays are in colour
value
order). For your purposes it would be better to arrange in color
index
order, then could return the index directly with the lookup.

The reverse lookup, colour name from color value (see GetColourName),
does
not require the palette colours to be in default positions.

If the colour has been customized the tooltip name doesn't always
update
from a colour name to "Custom", so could return incorrectly (other
ways
to
verify that). Also, is say default red is in the default posistion
for
default blue, the tooltip may continue to read Blue. There are other
issues
too, IOW caveats!

Function CvalCNames(nClrVal As Long, sName As String) As Boolean
Dim i As Long
Dim vN, vS

' 46/56 colours, excl the 10 duplicate chart colours
vN = Array(xlAutomatic, 0, 128&, 255&, 13056&, 13107&, 13209&,
26367&,
_
32768, 32896, 39423, 52377, 52479, 65280, 65535, 3355443, 6684774,
6697728,
_
6697881, 6723891, 8388608, 8388736, 8421376, 8421504, 8421631,
9868950,
_
10040115, 10053222, 10079487, 10092543, 12632256, 13395456, 13408767,
_
13421619, 13434828, 13434879, 16711680, 16711935, 16737843, 16751001,
_
16751052, 16763904, 16764057, 16764108, 16776960, 16777164, 16777215)

vS = Array("Automatic", "Black", "Dark Red", "Red", "Dark Green", _
"Olive Green", "Brown", "Orange", "Green", "Dark Yellow", "Light
Orange",
_
"Lime", "Gold", "Bright Green", "Yellow", "Gray-80%", "Dark Purple",
_
"Dark Teal", "Plum", "Sea Green", "Dark Blue", "Violet", "Teal", _
"Gray-50%", "Coral", "Gray-40%", "Indigo", "Blue-Gray", "Tan", _
"Light Yellow", "Gray-25%", "Ocean Blue", "Rose", "Aqua", "Light
Green",
_
"Ivory", "Blue", "Pink", "Light Blue", "Periwinkle", "Lavender", _
"Sky Blue", "Pale Blue", "Ice Blue", "Turqoise", "Light Turquiose",
"White")

If Len(sName) Then
For i = 0 To UBound(vS)
If sName = vS(i) Then
nClrVal = vN(i)
Exit For
End If
Next
Else
For i = 0 To UBound(vN)
If nClrVal = vN(i) Then
sName = vS(i)
Exit For
End If
Next
End If

CvalCNames = i <= UBound(vN)

End Function

Sub ApplyToolBarFillColor()
Dim sName As String, nClrValue As Long

sName = Application.CommandBars("Formatting"). _
Controls("Fill Color").TooltipText

sName = Mid(sName, InStr(1, sName, "(") + 1, 30)
sName = Left(sName, Len(sName) - 1)

If CvalCNames(nClrValue, sName) Then
If nClrValue < 1 Then
ActiveCell.Interior.ColorIndex = xlAutomatic
Else
ActiveCell.Interior.Color = nClrValue
End If
Else
MsgBox "Custom or non-English colour names"
End If

GetColourName

End Sub

Sub GetColourName()
Dim idx As Long, sName As String, nClrValue As Long
With ActiveCell.Interior
nClrValue = .Color
idx = .ColorIndex
End With

If CvalCNames(nClrValue, sName) Then
MsgBox idx & " " & sName
Else
MsgBox "Custom or non-English colour names"
End If

End Sub

Regards,
Peter T


"JasonF" wrote in message
ups.com...
Is there a way to determine what the current / last used color
index
number is from VBA? Instead of hard coding the colorindex or
forcing
the user to choose a color, I would like to just pick up the color
that's currently in the toolbar? I've found the name:
Application.CommandBars("Formatting").Controls("Fi ll
Color").TooltipText
However, I'd like to get the ColorIndex number for the name. Any
ideas?