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("Fill
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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("Fill
> 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 hwndNewCapture:00010554
<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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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("Fill
> > 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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("Fill
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 hwndNewCapture:00010554
> <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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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("Fill
> > > 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
news:eErfk$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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("Fill
> > 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
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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 hwndNewCapture:00010554
> > <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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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("Fill
> > > > 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" <(E-Mail Removed)> wrote in message
news:O#JC#(E-Mail Removed)...
> 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
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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 hwndNewCapture:00010554
> > > <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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > 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("Fill
> > > > > 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> news:eErfk$(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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("Fill
> > > 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
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> > news:eErfk$(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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("Fill
> > > > Color").TooltipText
> > > > However, I'd like to get the ColorIndex number for the name. Any
> > > > ideas?
> > > >
> > >
> > >
> > >
> > >
> >
> >
>
>