|
ColorIndex of the current application
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? |
ColorIndex of the current application
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? |
ColorIndex of the current application
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? |
ColorIndex of the current application
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? |
ColorIndex of the current application
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? |
ColorIndex of the current application
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? |
ColorIndex of the current application
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? |
ColorIndex of the current application
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? |
ColorIndex of the current application
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? |
ColorIndex of the current application
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? |
ColorIndex of the current application
Peter,
I tried with: dc = GetDC(WindowFromPoint(x, y)) clr = GetPixel(dc, x, y) but I too always get a return value of -1. I need to read up more on this GDI stuff... Actually shouldn't the coordinates be relative to the top-left of the containing Window, rather than the screen ? This seems to work, so it maybe a case of correcting the x,y value to pass to GetPixel. using FindWindow/FindWindowEx instead of WindowFromPoint will then mean the command bar can be hidden behind anything. 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 GetPixel Lib "gdi32" ( _ ByVal hdc As Long, _ ByVal x As Long, ByVal y As Long) As Long Private Declare Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As Long Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long Private Type RECT Left As Long Top As Long Right As Long Bottom As Long End Type Private Sub CommandButton1_Click() Dim ctr As CommandBarPopup Dim x As Long, y As Long Dim RetVal As Long Dim WndRect As RECT Dim i As Long, j As Long Dim dc As Long Dim Wid As Long With Cells .ClearFormats .ColumnWidth = 2.1 .RowHeight = 12 End With Set ctr = Application.CommandBars.FindControl(ID:=1691) x = ctr.Left y = ctr.Top RetVal = WindowFromPoint(x, y) GetWindowRect RetVal, WndRect dc = GetDC(RetVal) With WndRect Wid = .Bottom - .Top 'x,y values are a little, but for demonstration.... For i = 1 To .Right - .Left For j = 1 To Wid Worksheets(3).Range("A1").Offset(i - 1, Wid - j).Interior.Color = GetPixel(dc, i, j) Next Next End With ReleaseDC 0, dc End Sub NickHK "Peter T" <peter_t@discussions wrote in message ... 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 -------------------- CUT -------------------- |
ColorIndex of the current application
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? |
ColorIndex of the current application
Hi Nick,
That was a nice giant reflection of my toolbar, cool ! Actually shouldn't the coordinates be relative to the top-left of the containing Window, rather than the screen ? Indeed. But which window! When I tried WindowFromPoint it returned either the XLMAIN window or the MsoCommandBar Window depending on whether or not the commandbar is currently docked/not docked, and inconsistently depending on what it had been, just for added confusion. With a bit of fiddling around I managed to verify the window in question and then the relevant offsets from top-left corner of the appropriate window. Having done all that and working with the correct window and coordinates, to my surprise I still find the fill control needs to be 'in view' to get its pixels. I don't understand that at all! So unless I'm missing something obvious it seems one might just as well work with the desktop window and the coordinates returned by the control's position (absolute desktop x/y pixels). And not much use unless can guarantee the fill control is not covered! Just a small thing - ReleaseDC 0, dc I guess a typo but the 'O' coerces to the desktop window (which is what I was using), but should be the dc returned with GetDC, so in your example ReleaseDC RetVal, dc Regards, Peter T "NickHK" wrote in message ... Peter, I tried with: dc = GetDC(WindowFromPoint(x, y)) clr = GetPixel(dc, x, y) but I too always get a return value of -1. I need to read up more on this GDI stuff... Actually shouldn't the coordinates be relative to the top-left of the containing Window, rather than the screen ? This seems to work, so it maybe a case of correcting the x,y value to pass to GetPixel. using FindWindow/FindWindowEx instead of WindowFromPoint will then mean the command bar can be hidden behind anything. 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 GetPixel Lib "gdi32" ( _ ByVal hdc As Long, _ ByVal x As Long, ByVal y As Long) As Long Private Declare Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As Long Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long Private Type RECT Left As Long Top As Long Right As Long Bottom As Long End Type Private Sub CommandButton1_Click() Dim ctr As CommandBarPopup Dim x As Long, y As Long Dim RetVal As Long Dim WndRect As RECT Dim i As Long, j As Long Dim dc As Long Dim Wid As Long With Cells .ClearFormats .ColumnWidth = 2.1 .RowHeight = 12 End With Set ctr = Application.CommandBars.FindControl(ID:=1691) x = ctr.Left y = ctr.Top RetVal = WindowFromPoint(x, y) GetWindowRect RetVal, WndRect dc = GetDC(RetVal) With WndRect Wid = .Bottom - .Top 'x,y values are a little, but for demonstration.... For i = 1 To .Right - .Left For j = 1 To Wid Worksheets(3).Range("A1").Offset(i - 1, Wid - j).Interior.Color = GetPixel(dc, i, j) Next Next End With ReleaseDC 0, dc End Sub NickHK "Peter T" <peter_t@discussions wrote in message ... 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 -------------------- CUT -------------------- |
ColorIndex of the current application
It would need to be adapted <g
Rregards, Peter T "Bob Phillips" wrote in message ... 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 |
ColorIndex of the current application
Peter,
Yes, Office apps do seem to have strange implementations with some of these Windows calls. I'll play around a bit more this week and see what I can do. ReleaseDC 0, dc Yes, I didn't update that line to use RetVal. NickHK "Peter T" <peter_t@discussions wrote in message ... Hi Nick, That was a nice giant reflection of my toolbar, cool ! Actually shouldn't the coordinates be relative to the top-left of the containing Window, rather than the screen ? Indeed. But which window! When I tried WindowFromPoint it returned either the XLMAIN window or the MsoCommandBar Window depending on whether or not the commandbar is currently docked/not docked, and inconsistently depending on what it had been, just for added confusion. With a bit of fiddling around I managed to verify the window in question and then the relevant offsets from top-left corner of the appropriate window. Having done all that and working with the correct window and coordinates, to my surprise I still find the fill control needs to be 'in view' to get its pixels. I don't understand that at all! So unless I'm missing something obvious it seems one might just as well work with the desktop window and the coordinates returned by the control's position (absolute desktop x/y pixels). And not much use unless can guarantee the fill control is not covered! Just a small thing - ReleaseDC 0, dc I guess a typo but the 'O' coerces to the desktop window (which is what I was using), but should be the dc returned with GetDC, so in your example ReleaseDC RetVal, dc Regards, Peter T "NickHK" wrote in message ... Peter, I tried with: dc = GetDC(WindowFromPoint(x, y)) clr = GetPixel(dc, x, y) but I too always get a return value of -1. I need to read up more on this GDI stuff... Actually shouldn't the coordinates be relative to the top-left of the containing Window, rather than the screen ? This seems to work, so it maybe a case of correcting the x,y value to pass to GetPixel. using FindWindow/FindWindowEx instead of WindowFromPoint will then mean the command bar can be hidden behind anything. 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 GetPixel Lib "gdi32" ( _ ByVal hdc As Long, _ ByVal x As Long, ByVal y As Long) As Long Private Declare Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As Long Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long Private Type RECT Left As Long Top As Long Right As Long Bottom As Long End Type Private Sub CommandButton1_Click() Dim ctr As CommandBarPopup Dim x As Long, y As Long Dim RetVal As Long Dim WndRect As RECT Dim i As Long, j As Long Dim dc As Long Dim Wid As Long With Cells .ClearFormats .ColumnWidth = 2.1 .RowHeight = 12 End With Set ctr = Application.CommandBars.FindControl(ID:=1691) x = ctr.Left y = ctr.Top RetVal = WindowFromPoint(x, y) GetWindowRect RetVal, WndRect dc = GetDC(RetVal) With WndRect Wid = .Bottom - .Top 'x,y values are a little, but for demonstration.... For i = 1 To .Right - .Left For j = 1 To Wid Worksheets(3).Range("A1").Offset(i - 1, Wid - j).Interior.Color = GetPixel(dc, i, j) Next Next End With ReleaseDC 0, dc End Sub NickHK "Peter T" <peter_t@discussions wrote in message ... 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 -------------------- CUT -------------------- |
ColorIndex of the current application
Peter,
After playing unsuccessfully with the SendMessage approach (seem to be getting different return value compared to Spy++ on the initial call), decided on the cheat method. Whilst still limited to being "in view", it more straight forward that messing with DCs etc. Quick enough so it's not noticable. Private Type POINTAPI X As Long Y As Long End Type Private Declare Function GetCursorPos Lib "user32" ( _ lpPoint As POINTAPI) _ As Long Private Declare Function SetCursorPos Lib "user32" ( _ ByVal X As Long, _ ByVal Y As Long) _ As Long Private Declare Sub mouse_event Lib "user32" ( _ ByVal dwFlags As Long, _ ByVal dx As Long, _ ByVal dy As Long, _ ByVal cButtons As Long, _ ByVal dwExtraInfo As Long) Private Const MOUSEEVENTF_ABSOLUTE As Long = &H8000 Private Const MOUSEEVENTF_LEFTDOWN As Long = &H2 Private Const MOUSEEVENTF_LEFTUP As Long = &H4 Private Const CTR_OFFSET_X As Long = 5 Private Const CTR_OFFSET_Y As Long = 5 Private Sub CommandButton1_Click() Dim ctr As CommandBarPopup Dim X As Long, Y As Long Dim OldCursorPos As POINTAPI Dim RetVal As Long Set ctr = Application.CommandBars.FindControl(ID:=1691) X = ctr.Left + CTR_OFFSET_X Y = ctr.Top + CTR_OFFSET_Y RetVal = GetCursorPos(OldCursorPos) SetCursorPos X, Y mouse_event MOUSEEVENTF_LEFTDOWN Or MOUSEEVENTF_ABSOLUTE, 0, 0, 0, 0 mouse_event MOUSEEVENTF_LEFTUP Or MOUSEEVENTF_ABSOLUTE, 0, 0, 0, 0 SetCursorPos OldCursorPos.X, OldCursorPos.Y End Sub NickHK "Peter T" <peter_t@discussions wrote in message ... Hi Nick, That was a nice giant reflection of my toolbar, cool ! Actually shouldn't the coordinates be relative to the top-left of the containing Window, rather than the screen ? Indeed. But which window! When I tried WindowFromPoint it returned either the XLMAIN window or the MsoCommandBar Window depending on whether or not the commandbar is currently docked/not docked, and inconsistently depending on what it had been, just for added confusion. With a bit of fiddling around I managed to verify the window in question and then the relevant offsets from top-left corner of the appropriate window. Having done all that and working with the correct window and coordinates, to my surprise I still find the fill control needs to be 'in view' to get its pixels. I don't understand that at all! So unless I'm missing something obvious it seems one might just as well work with the desktop window and the coordinates returned by the control's position (absolute desktop x/y pixels). And not much use unless can guarantee the fill control is not covered! Just a small thing - ReleaseDC 0, dc I guess a typo but the 'O' coerces to the desktop window (which is what I was using), but should be the dc returned with GetDC, so in your example ReleaseDC RetVal, dc Regards, Peter T "NickHK" wrote in message ... Peter, I tried with: dc = GetDC(WindowFromPoint(x, y)) clr = GetPixel(dc, x, y) but I too always get a return value of -1. I need to read up more on this GDI stuff... Actually shouldn't the coordinates be relative to the top-left of the containing Window, rather than the screen ? This seems to work, so it maybe a case of correcting the x,y value to pass to GetPixel. using FindWindow/FindWindowEx instead of WindowFromPoint will then mean the command bar can be hidden behind anything. 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 GetPixel Lib "gdi32" ( _ ByVal hdc As Long, _ ByVal x As Long, ByVal y As Long) As Long Private Declare Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As Long Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long Private Type RECT Left As Long Top As Long Right As Long Bottom As Long End Type Private Sub CommandButton1_Click() Dim ctr As CommandBarPopup Dim x As Long, y As Long Dim RetVal As Long Dim WndRect As RECT Dim i As Long, j As Long Dim dc As Long Dim Wid As Long With Cells .ClearFormats .ColumnWidth = 2.1 .RowHeight = 12 End With Set ctr = Application.CommandBars.FindControl(ID:=1691) x = ctr.Left y = ctr.Top RetVal = WindowFromPoint(x, y) GetWindowRect RetVal, WndRect dc = GetDC(RetVal) With WndRect Wid = .Bottom - .Top 'x,y values are a little, but for demonstration.... For i = 1 To .Right - .Left For j = 1 To Wid Worksheets(3).Range("A1").Offset(i - 1, Wid - j).Interior.Color = GetPixel(dc, i, j) Next Next End With ReleaseDC 0, dc End Sub NickHK |
ColorIndex of the current application
Actually, just realised that this not really solve the OP request. And may
not always work as expected. I test some more, but maybe back to the drawing board... NickHK "NickHK" wrote in message ... Peter, After playing unsuccessfully with the SendMessage approach (seem to be getting different return value compared to Spy++ on the initial call), decided on the cheat method. Whilst still limited to being "in view", it more straight forward that messing with DCs etc. Quick enough so it's not noticable. Private Type POINTAPI X As Long Y As Long End Type Private Declare Function GetCursorPos Lib "user32" ( _ lpPoint As POINTAPI) _ As Long Private Declare Function SetCursorPos Lib "user32" ( _ ByVal X As Long, _ ByVal Y As Long) _ As Long Private Declare Sub mouse_event Lib "user32" ( _ ByVal dwFlags As Long, _ ByVal dx As Long, _ ByVal dy As Long, _ ByVal cButtons As Long, _ ByVal dwExtraInfo As Long) Private Const MOUSEEVENTF_ABSOLUTE As Long = &H8000 Private Const MOUSEEVENTF_LEFTDOWN As Long = &H2 Private Const MOUSEEVENTF_LEFTUP As Long = &H4 Private Const CTR_OFFSET_X As Long = 5 Private Const CTR_OFFSET_Y As Long = 5 Private Sub CommandButton1_Click() Dim ctr As CommandBarPopup Dim X As Long, Y As Long Dim OldCursorPos As POINTAPI Dim RetVal As Long Set ctr = Application.CommandBars.FindControl(ID:=1691) X = ctr.Left + CTR_OFFSET_X Y = ctr.Top + CTR_OFFSET_Y RetVal = GetCursorPos(OldCursorPos) SetCursorPos X, Y mouse_event MOUSEEVENTF_LEFTDOWN Or MOUSEEVENTF_ABSOLUTE, 0, 0, 0, 0 mouse_event MOUSEEVENTF_LEFTUP Or MOUSEEVENTF_ABSOLUTE, 0, 0, 0, 0 SetCursorPos OldCursorPos.X, OldCursorPos.Y End Sub NickHK "Peter T" <peter_t@discussions wrote in message ... Hi Nick, That was a nice giant reflection of my toolbar, cool ! Actually shouldn't the coordinates be relative to the top-left of the containing Window, rather than the screen ? Indeed. But which window! When I tried WindowFromPoint it returned either the XLMAIN window or the MsoCommandBar Window depending on whether or not the commandbar is currently docked/not docked, and inconsistently depending on what it had been, just for added confusion. With a bit of fiddling around I managed to verify the window in question and then the relevant offsets from top-left corner of the appropriate window. Having done all that and working with the correct window and coordinates, to my surprise I still find the fill control needs to be 'in view' to get its pixels. I don't understand that at all! So unless I'm missing something obvious it seems one might just as well work with the desktop window and the coordinates returned by the control's position (absolute desktop x/y pixels). And not much use unless can guarantee the fill control is not covered! Just a small thing - ReleaseDC 0, dc I guess a typo but the 'O' coerces to the desktop window (which is what I was using), but should be the dc returned with GetDC, so in your example ReleaseDC RetVal, dc Regards, Peter T "NickHK" wrote in message ... Peter, I tried with: dc = GetDC(WindowFromPoint(x, y)) clr = GetPixel(dc, x, y) but I too always get a return value of -1. I need to read up more on this GDI stuff... Actually shouldn't the coordinates be relative to the top-left of the containing Window, rather than the screen ? This seems to work, so it maybe a case of correcting the x,y value to pass to GetPixel. using FindWindow/FindWindowEx instead of WindowFromPoint will then mean the command bar can be hidden behind anything. 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 GetPixel Lib "gdi32" ( _ ByVal hdc As Long, _ ByVal x As Long, ByVal y As Long) As Long Private Declare Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As Long Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long Private Type RECT Left As Long Top As Long Right As Long Bottom As Long End Type Private Sub CommandButton1_Click() Dim ctr As CommandBarPopup Dim x As Long, y As Long Dim RetVal As Long Dim WndRect As RECT Dim i As Long, j As Long Dim dc As Long Dim Wid As Long With Cells .ClearFormats .ColumnWidth = 2.1 .RowHeight = 12 End With Set ctr = Application.CommandBars.FindControl(ID:=1691) x = ctr.Left y = ctr.Top RetVal = WindowFromPoint(x, y) GetWindowRect RetVal, WndRect dc = GetDC(RetVal) With WndRect Wid = .Bottom - .Top 'x,y values are a little, but for demonstration.... For i = 1 To .Right - .Left For j = 1 To Wid Worksheets(3).Range("A1").Offset(i - 1, Wid - j).Interior.Color = GetPixel(dc, i, j) Next Next End With ReleaseDC 0, dc End Sub NickHK |
ColorIndex of the current application
Hi Nick,
Yes the Mouse event API works fine (but see below). I still wonder if there's not a SendMessage approach, but doubtful. As you say the control also needs to be 'in view' with this method. However if it isn't the mouse event might hit on some other button that hides the fill format control. I tried that with the VBE sitting over the fill control and the API hit a VBE button while running the code. Currently I can't close the VBE window even with Ctrl-Alt-Delete, will have to reboot in a moment! The worst the GetPixel method would do is apply the wrong colour. With both methods it should be possible to get the window under X/Y with your WindowFromPoint, then check to see if it's either the XLMAIN window or the ("MsoCommandBar", ctr.parent.name) window depending if the toolbar is docked/not docked. Anyway providing the window under X/Y is an anticipated window, should then be safe to use either the mouse event API or GetPixel. it more straight forward that messing with DCs etc. Not sure I see anything complicated about getting desktop DC, GetPixel, then releasing the DC. Regards, Peter T "NickHK" wrote in message ... Peter, After playing unsuccessfully with the SendMessage approach (seem to be getting different return value compared to Spy++ on the initial call), decided on the cheat method. Whilst still limited to being "in view", it more straight forward that messing with DCs etc. Quick enough so it's not noticable. Private Type POINTAPI X As Long Y As Long End Type Private Declare Function GetCursorPos Lib "user32" ( _ lpPoint As POINTAPI) _ As Long Private Declare Function SetCursorPos Lib "user32" ( _ ByVal X As Long, _ ByVal Y As Long) _ As Long Private Declare Sub mouse_event Lib "user32" ( _ ByVal dwFlags As Long, _ ByVal dx As Long, _ ByVal dy As Long, _ ByVal cButtons As Long, _ ByVal dwExtraInfo As Long) Private Const MOUSEEVENTF_ABSOLUTE As Long = &H8000 Private Const MOUSEEVENTF_LEFTDOWN As Long = &H2 Private Const MOUSEEVENTF_LEFTUP As Long = &H4 Private Const CTR_OFFSET_X As Long = 5 Private Const CTR_OFFSET_Y As Long = 5 Private Sub CommandButton1_Click() Dim ctr As CommandBarPopup Dim X As Long, Y As Long Dim OldCursorPos As POINTAPI Dim RetVal As Long Set ctr = Application.CommandBars.FindControl(ID:=1691) X = ctr.Left + CTR_OFFSET_X Y = ctr.Top + CTR_OFFSET_Y RetVal = GetCursorPos(OldCursorPos) SetCursorPos X, Y mouse_event MOUSEEVENTF_LEFTDOWN Or MOUSEEVENTF_ABSOLUTE, 0, 0, 0, 0 mouse_event MOUSEEVENTF_LEFTUP Or MOUSEEVENTF_ABSOLUTE, 0, 0, 0, 0 SetCursorPos OldCursorPos.X, OldCursorPos.Y End Sub NickHK "Peter T" <peter_t@discussions wrote in message ... Hi Nick, That was a nice giant reflection of my toolbar, cool ! Actually shouldn't the coordinates be relative to the top-left of the containing Window, rather than the screen ? Indeed. But which window! When I tried WindowFromPoint it returned either the XLMAIN window or the MsoCommandBar Window depending on whether or not the commandbar is currently docked/not docked, and inconsistently depending on what it had been, just for added confusion. With a bit of fiddling around I managed to verify the window in question and then the relevant offsets from top-left corner of the appropriate window. Having done all that and working with the correct window and coordinates, to my surprise I still find the fill control needs to be 'in view' to get its pixels. I don't understand that at all! So unless I'm missing something obvious it seems one might just as well work with the desktop window and the coordinates returned by the control's position (absolute desktop x/y pixels). And not much use unless can guarantee the fill control is not covered! Just a small thing - ReleaseDC 0, dc I guess a typo but the 'O' coerces to the desktop window (which is what I was using), but should be the dc returned with GetDC, so in your example ReleaseDC RetVal, dc Regards, Peter T "NickHK" wrote in message ... Peter, I tried with: dc = GetDC(WindowFromPoint(x, y)) clr = GetPixel(dc, x, y) but I too always get a return value of -1. I need to read up more on this GDI stuff... Actually shouldn't the coordinates be relative to the top-left of the containing Window, rather than the screen ? This seems to work, so it maybe a case of correcting the x,y value to pass to GetPixel. using FindWindow/FindWindowEx instead of WindowFromPoint will then mean the command bar can be hidden behind anything. 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 GetPixel Lib "gdi32" ( _ ByVal hdc As Long, _ ByVal x As Long, ByVal y As Long) As Long Private Declare Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As Long Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long Private Type RECT Left As Long Top As Long Right As Long Bottom As Long End Type Private Sub CommandButton1_Click() Dim ctr As CommandBarPopup Dim x As Long, y As Long Dim RetVal As Long Dim WndRect As RECT Dim i As Long, j As Long Dim dc As Long Dim Wid As Long With Cells .ClearFormats .ColumnWidth = 2.1 .RowHeight = 12 End With Set ctr = Application.CommandBars.FindControl(ID:=1691) x = ctr.Left y = ctr.Top RetVal = WindowFromPoint(x, y) GetWindowRect RetVal, WndRect dc = GetDC(RetVal) With WndRect Wid = .Bottom - .Top 'x,y values are a little, but for demonstration.... For i = 1 To .Right - .Left For j = 1 To Wid Worksheets(3).Range("A1").Offset(i - 1, Wid - j).Interior.Color = GetPixel(dc, i, j) Next Next End With ReleaseDC 0, dc End Sub NickHK |
ColorIndex of the current application
"Peter T" <peter_t@discussions wrote in message
Currently I can't close the VBE window even with Ctrl-Alt-Delete, will have to reboot in a moment! - not so serious, I just had to close Excel, no work lost :-) Peter T |
| All times are GMT +1. The time now is 02:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com