I have a line on a worksheet and its color is set with RGB settings. I
want to select the line and run a macro that will show me the RGB
settings. TIA, James

By line I can't determine if you have a drawing object or cells which
have their pattern set to the pallette.. If it is the cells color then
the VBA code to return it's pallette value is:
-----------------------------
With ActiveCell.Interior
..ColorIndex = NumColor
End With
msgbox (NumColor)
-----------------------------
I am not sure how to extract RGB values from the pallettes own settings
but if you become more clear on the requirement I am sure there is some
solution we can locate.

HTH,
Will


Zone wrote:
I have a line on a worksheet and its color is set with RGB settings. I
want to select the line and run a macro that will show me the RGB
settings. TIA, James

The Line is a drawing object. Thanks, James

wrote:
By line I can't determine if you have a drawing object or cells which
have their pattern set to the pallette.. If it is the cells color then
the VBA code to return it's pallette value is:
-----------------------------
With ActiveCell.Interior
.ColorIndex = NumColor
End With
msgbox (NumColor)
-----------------------------
I am not sure how to extract RGB values from the pallettes own settings
but if you become more clear on the requirement I am sure there is some
solution we can locate.

HTH,
Will


Zone wrote:
I have a line on a worksheet and its color is set with RGB settings. I
want to select the line and run a macro that will show me the RGB
settings. TIA, James

I can extract the pallette value using the code below.. If there is a
requirement to reuse this color elsewhere then you won't need RGB
because all other objects will work with the same pallette..however if
you still need RGB for another purpose let me know.
----------------------------
For Each Shape In ActiveSheet.Shapes
msg = msg & vbCrLf & Shape.Name & " " &
Shape.Line.ForeColor.SchemeColor
Next

MsgBox (msg)
----------------------------


Zone wrote:
The Line is a drawing object. Thanks, James

wrote:
By line I can't determine if you have a drawing object or cells which
have their pattern set to the pallette.. If it is the cells color then
the VBA code to return it's pallette value is:
-----------------------------
With ActiveCell.Interior
.ColorIndex = NumColor
End With
msgbox (NumColor)
-----------------------------
I am not sure how to extract RGB values from the pallettes own settings
but if you become more clear on the requirement I am sure there is some
solution we can locate.

HTH,
Will


Zone wrote:
I have a line on a worksheet and its color is set with RGB settings. I
want to select the line and run a macro that will show me the RGB
settings. TIA, James

Just to add, colour formats in shapes, such as fill, border, line (but not
font) can have a 'fixed-RGB' colour not linked to the palette. With a
'fixed-rgb' colour testing for its Schemecolor will throw an error, which
indeed is how to test for such a format.

Sub test()
Dim ln As Line, cx As Long, clr As Long
For Each ln In ActiveSheet.Lines
With ln.ShapeRange.Line.ForeColor
cx = -1
On Error Resume Next
cx = .SchemeColor 'error if 'fixed-RGB' colour
On Error GoTo 0
clr = .RGB
End With
Debug.Print ln.Name, cx, clr
Next
End Sub

Subtract 7 from the schemecolor to return the equivalent colorindex. But if
cx = 64 the equivalent colorindex is automatic (system black for forecolor),
or -1 in the above example indicates a fixed-rgb colour. There is also a
Backcolor property for the pattern colour (or 2nd effect colour in fill).

Regards,
Peter T

wrote in message
oups.com...
I can extract the pallette value using the code below.. If there is a
requirement to reuse this color elsewhere then you won't need RGB
because all other objects will work with the same pallette..however if
you still need RGB for another purpose let me know.
----------------------------
For Each Shape In ActiveSheet.Shapes
msg = msg & vbCrLf & Shape.Name & " " &
Shape.Line.ForeColor.SchemeColor
Next

MsgBox (msg)
----------------------------


Zone wrote:
The Line is a drawing object. Thanks, James

wrote:
By line I can't determine if you have a drawing object or cells which
have their pattern set to the pallette.. If it is the cells color then
the VBA code to return it's pallette value is:
-----------------------------
With ActiveCell.Interior
.ColorIndex = NumColor
End With
msgbox (NumColor)
-----------------------------
I am not sure how to extract RGB values from the pallettes own
settings
but if you become more clear on the requirement I am sure there is
some
solution we can locate.

HTH,
Will


Zone wrote:
I have a line on a worksheet and its color is set with RGB settings.
I
want to select the line and run a macro that will show me the RGB
settings. TIA, James

Thanks, Peter. Since my drawing object may be set with a scheme color
OR with RGB colors, my next thought was how to detect either. If I
understand your reply correctly, if I try to display the scheme color
for an object set with RGB, I'll get an error, so I could use that
error to bail from the scheme color and go to detecting RGB as Michael
explained.
Regards,
James

Peter T wrote:
Just to add, colour formats in shapes, such as fill, border, line (but not
font) can have a 'fixed-RGB' colour not linked to the palette. With a
'fixed-rgb' colour testing for its Schemecolor will throw an error, which
indeed is how to test for such a format.

Sub test()
Dim ln As Line, cx As Long, clr As Long
For Each ln In ActiveSheet.Lines
With ln.ShapeRange.Line.ForeColor
cx = -1
On Error Resume Next
cx = .SchemeColor 'error if 'fixed-RGB' colour
On Error GoTo 0
clr = .RGB
End With
Debug.Print ln.Name, cx, clr
Next
End Sub

Subtract 7 from the schemecolor to return the equivalent colorindex. But if
cx = 64 the equivalent colorindex is automatic (system black for forecolor),
or -1 in the above example indicates a fixed-rgb colour. There is also a
Backcolor property for the pattern colour (or 2nd effect colour in fill).

Regards,
Peter T

wrote in message
oups.com...
I can extract the pallette value using the code below.. If there is a
requirement to reuse this color elsewhere then you won't need RGB
because all other objects will work with the same pallette..however if
you still need RGB for another purpose let me know.
----------------------------
For Each Shape In ActiveSheet.Shapes
msg = msg & vbCrLf & Shape.Name & " " &
Shape.Line.ForeColor.SchemeColor
Next

MsgBox (msg)
----------------------------


Zone wrote:
The Line is a drawing object. Thanks, James

wrote:
By line I can't determine if you have a drawing object or cells which
have their pattern set to the pallette.. If it is the cells color then
the VBA code to return it's pallette value is:
-----------------------------
With ActiveCell.Interior
.ColorIndex = NumColor
End With
msgbox (NumColor)
-----------------------------
I am not sure how to extract RGB values from the pallettes own
settings
but if you become more clear on the requirement I am sure there is
some
solution we can locate.

HTH,
Will


Zone wrote:
I have a line on a worksheet and its color is set with RGB settings.
I
want to select the line and run a macro that will show me the RGB
settings. TIA, James

Hi Zone,
With something like that (by assigning this macro to your line):

Private Type RGB_Wrapper
Red As Byte
Green As Byte
Blue As Byte
Alpha As Byte
End Type

Private Type LNG_Wrapper
Value As Long
End Type

Sub RGB_Settings()
Dim Color As LNG_Wrapper, Colors As RGB_Wrapper
Color.Value = ActiveSheet.Shapes("Line 1").Line.ForeColor
LSet Colors = Color
MsgBox "R: " & Colors.Red & vbLf _
& "G: " & Colors.Green & vbLf _
& "B: " & Colors.Blue
End Sub


"Zone" a écrit dans le message de news:
...
I have a line on a worksheet and its color is set with RGB settings. I
want to select the line and run a macro that will show me the RGB
settings. TIA, James

wooooo-hooooo!
Michael, worked like a charm. I've never implemented a user-defined
type before, but it certainly did the trick. I want to get the back
color as well, so I just added that to your code, as below. Any
problem with this? Since the selected object might be a rectangle,
circle or freeform instead of a line, I want to be able to get the fill
RGB as well, so that will be my next effort. I found with a rectangle,
I had to select the actual outline to get the line RGB settings for the
line, which evidently means I'll need to select the fill to get the RGB
settings for the fill. Many thanks. May be posting back.
James

Private Type RGB_Wrapper
Red As Byte
Green As Byte
Blue As Byte
Alpha As Byte
End Type


Private Type LNG_Wrapper
Value As Long
End Type

Sub RGB_Settings()
Dim Color As LNG_Wrapper, Colors As RGB_Wrapper
Dim ColorB As LNG_Wrapper, ColorsB As RGB_Wrapper
Color.Value = Selection.ShapeRange.Line.ForeColor
LSet Colors = Color
ColorB.Value = Selection.ShapeRange.Line.BackColor
LSet ColorsB = ColorB
MsgBox "Line Fore Color" & vbLf & "Red: " & Colors.Red & " " _
& "Green: " & Colors.Green & " " _
& "Blue: " & Colors.Blue & vbLf & vbLf _
& "Line Pattern: " & Selection.ShapeRange.Line.Pattern & vbLf & vbLf
_
& "Line Back Color" & vbLf _
& "Red: " & ColorsB.Red & " " _
& "Green: " & ColorsB.Green & " " _
& "Blue: " & ColorsB.Blue
End Sub

Michel Pierron wrote:
Hi Zone,
With something like that (by assigning this macro to your line):

Private Type RGB_Wrapper
Red As Byte
Green As Byte
Blue As Byte
Alpha As Byte
End Type

Private Type LNG_Wrapper
Value As Long
End Type

Sub RGB_Settings()
Dim Color As LNG_Wrapper, Colors As RGB_Wrapper
Color.Value = ActiveSheet.Shapes("Line 1").Line.ForeColor
LSet Colors = Color
MsgBox "R: " & Colors.Red & vbLf _
& "G: " & Colors.Green & vbLf _
& "B: " & Colors.Blue
End Sub


"Zone" a écrit dans le message de news:
...
I have a line on a worksheet and its color is set with RGB settings. I
want to select the line and run a macro that will show me the RGB
settings. TIA, James