Can anybody help me out.

I have a sheet filled with colors. Some of these colors are not preset color
palette colors, but user defined. Now I want to change certain buttons on a
form I have created to have the background color of a cell. I tried the
colorindex and color property, however none of these give the desired result
for the user defined colors.

Therefore I would like to have the RGB code for these colors.
Is this possible or is there any workaround?

Thanks in advance

s/b simple assuming you are
using an embedded forms commandbutton:

Private Sub CommandButton1_Click()
With CommandButton1
.BackColor = .TopLeftCell.Interior.Color
End With
End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Brotha lee wrote :

Can anybody help me out.

I have a sheet filled with colors. Some of these colors are not
preset color palette colors, but user defined. Now I want to change
certain buttons on a form I have created to have the background color
of a cell. I tried the colorindex and color property, however none of
these give the desired result for the user defined colors.

Therefore I would like to have the RGB code for these colors.
Is this possible or is there any workaround?

Thanks in advance

Thanks this works!

However I use a userform and not an embedded form. I have copied the code in
the initilize event, however then it does not work. Only on a click event it
works.

Do you know why?

"keepITcool" schreef:


s/b simple assuming you are
using an embedded forms commandbutton:

Private Sub CommandButton1_Click()
With CommandButton1
.BackColor = .TopLeftCell.Interior.Color
End With
End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Brotha lee wrote :

Can anybody help me out.

I have a sheet filled with colors. Some of these colors are not
preset color palette colors, but user defined. Now I want to change
certain buttons on a form I have created to have the background color
of a cell. I tried the colorindex and color property, however none of
these give the desired result for the user defined colors.

Therefore I would like to have the RGB code for these colors.
Is this possible or is there any workaround?

Thanks in advance

show me your code




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Brotha lee wrote :

Thanks this works!

However I use a userform and not an embedded form. I have copied the
code in the initilize event, however then it does not work. Only on a
click event it works.

Do you know why?

"keepITcool" schreef:


s/b simple assuming you are
using an embedded forms commandbutton:

Private Sub CommandButton1_Click()
With CommandButton1
.BackColor = .TopLeftCell.Interior.Color
End With
End Sub




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Brotha lee wrote :

Can anybody help me out.

I have a sheet filled with colors. Some of these colors are not
preset color palette colors, but user defined. Now I want to
change certain buttons on a form I have created to have the
background color of a cell. I tried the colorindex and color
property, however none of these give the desired result for the
user defined colors.

Therefore I would like to have the RGB code for these colors.
Is this possible or is there any workaround?

Thanks in advance

Private Sub UserForm_Initialize()
myheight = 12
mytop = 70
mywidth = 12
myleft = 12
Set Mycmd = Controls.Add("Forms.CommandButton.1", "Test")
Mycmd.Left = myleft
Mycmd.Top = mytop
Mycmd.Width = mywidth
Mycmd.Height = myheight
Range("a2").Select
Mycmd.BackColor = Range("a2").Interior.Color

End Sub


"keepITcool" schreef:

show me your code




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Brotha lee wrote :

Thanks this works!

However I use a userform and not an embedded form. I have copied the
code in the initilize event, however then it does not work. Only on a
click event it works.

Do you know why?

"keepITcool" schreef:


s/b simple assuming you are
using an embedded forms commandbutton:

Private Sub CommandButton1_Click()
With CommandButton1
.BackColor = .TopLeftCell.Interior.Color
End With
End Sub




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Brotha lee wrote :

Can anybody help me out.

I have a sheet filled with colors. Some of these colors are not
preset color palette colors, but user defined. Now I want to
change certain buttons on a form I have created to have the
background color of a cell. I tried the colorindex and color
property, however none of these give the desired result for the
user defined colors.

Therefore I would like to have the RGB code for these colors.
Is this possible or is there any workaround?

Thanks in advance

yes...
strange and unexpected :(
but easily solved with :)

Mycmd.BackColor = ActiveWorkbook.Colors(Range("a2").Interior.ColorIn dex)





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Brotha lee wrote :

Private Sub UserForm_Initialize()
myheight = 12
mytop = 70
mywidth = 12
myleft = 12
Set Mycmd = Controls.Add("Forms.CommandButton.1", "Test")
Mycmd.Left = myleft
Mycmd.Top = mytop
Mycmd.Width = mywidth
Mycmd.Height = myheight
Range("a2").Select
Mycmd.BackColor = Range("a2").Interior.Color

End Sub


"keepITcool" schreef:

show me your code




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Brotha lee wrote :

Thanks this works!

However I use a userform and not an embedded form. I have copied
the code in the initilize event, however then it does not work.
Only on a click event it works.

Do you know why?

"keepITcool" schreef:


s/b simple assuming you are
using an embedded forms commandbutton:

Private Sub CommandButton1_Click()
With CommandButton1
.BackColor = .TopLeftCell.Interior.Color
End With
End Sub




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Brotha lee wrote :

Can anybody help me out.

I have a sheet filled with colors. Some of these colors are
not preset color palette colors, but user defined. Now I want
to change certain buttons on a form I have created to have the
background color of a cell. I tried the colorindex and color
property, however none of these give the desired result for
the user defined colors.

Therefore I would like to have the RGB code for these colors.
Is this possible or is there any workaround?

Thanks in advance

Hi KeepITcool

What's strange and unexpected, Brotha lee's code works fine for me in XL2K

However your workaround fails if the cell is not colour formatted -

Range("a2").Interior.ColorIndex = xlNone
x = ActiveWorkbook.Colors(Range("a2").Interior.ColorIn dex) ' fails
x = Range("B2").Interior.Color ' works

Regards,
Peter T

"keepITcool" wrote in message
ft.com...

yes...
strange and unexpected :(
but easily solved with :)

Mycmd.BackColor = ActiveWorkbook.Colors(Range("a2").Interior.ColorIn dex)





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Brotha lee wrote :

Private Sub UserForm_Initialize()
myheight = 12
mytop = 70
mywidth = 12
myleft = 12
Set Mycmd = Controls.Add("Forms.CommandButton.1", "Test")
Mycmd.Left = myleft
Mycmd.Top = mytop
Mycmd.Width = mywidth
Mycmd.Height = myheight
Range("a2").Select
Mycmd.BackColor = Range("a2").Interior.Color

End Sub


"keepITcool" schreef:

show me your code




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Brotha lee wrote :

Thanks this works!

However I use a userform and not an embedded form. I have copied
the code in the initilize event, however then it does not work.
Only on a click event it works.

Do you know why?

"keepITcool" schreef:


s/b simple assuming you are
using an embedded forms commandbutton:

Private Sub CommandButton1_Click()
With CommandButton1
.BackColor = .TopLeftCell.Interior.Color
End With
End Sub




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Brotha lee wrote :

Can anybody help me out.

I have a sheet filled with colors. Some of these colors are
not preset color palette colors, but user defined. Now I want
to change certain buttons on a form I have created to have the
background color of a cell. I tried the colorindex and color
property, however none of these give the desired result for
the user defined colors.

Therefore I would like to have the RGB code for these colors.
Is this possible or is there any workaround?

Thanks in advance

Peter,

what Brother Lee and I find unexpected....

is that it will give the standard (uncustomized)
colorRGB (at least it does in xl2002 & xl2003)
see original question.

You are correct it should handle 'uncolored' cells:

Select Case ActiveCell.Interior.ColorIndex
Case 1 To 56
Mycmd.BackColor = _
ActiveWorkbook.Colors(ActiveCell.Interior.ColorInd ex)
Case Else
Mycmd.BackColor = vbWindowBackground
End Select


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Peter T wrote :

Hi KeepITcool

What's strange and unexpected, Brotha lee's code works fine for me in
XL2K

However your workaround fails if the cell is not colour formatted -

Range("a2").Interior.ColorIndex = xlNone
x = ActiveWorkbook.Colors(Range("a2").Interior.ColorIn dex) ' fails
x = Range("B2").Interior.Color ' works

Regards,
Peter T

"keepITcool" wrote in message
ft.com...

yes...
strange and unexpected :(
but easily solved with :)

Mycmd.BackColor =
ActiveWorkbook.Colors(Range("a2").Interior.ColorIn dex)





--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Brotha lee wrote :

Private Sub UserForm_Initialize()
myheight = 12
mytop = 70
mywidth = 12
myleft = 12
Set Mycmd = Controls.Add("Forms.CommandButton.1", "Test")
Mycmd.Left = myleft
Mycmd.Top = mytop
Mycmd.Width = mywidth
Mycmd.Height = myheight
Range("a2").Select
Mycmd.BackColor = Range("a2").Interior.Color

End Sub


"keepITcool" schreef:

show me your code




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Brotha lee wrote :

Thanks this works!

However I use a userform and not an embedded form. I have
copied the code in the initilize event, however then it does
not work. Only on a click event it works.

Do you know why?

"keepITcool" schreef:


s/b simple assuming you are
using an embedded forms commandbutton:

Private Sub CommandButton1_Click()
With CommandButton1
.BackColor = .TopLeftCell.Interior.Color
End With
End Sub




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Brotha lee wrote :

Can anybody help me out.

I have a sheet filled with colors. Some of these colors
are not preset color palette colors, but user defined.
Now I want to change certain buttons on a form I have
created to have the background color of a cell. I tried
the colorindex and color property, however none of these
give the desired result for the user defined colors.

Therefore I would like to have the RGB code for these
colors. Is this possible or is there any workaround?

Thanks in advance

What's strange and unexpected, Brotha lee's code works fine for me in
XL2K

what Brother Lee and I find unexpected....

is that it will give the standard (uncustomized)
colorRGB (at least it does in xl2002 & xl2003)
see original question.

Apologies, I didn't read the original question and have come accros this
before. A lot of what I do in Excel concerns colour and it's still a mystery
to me as to where/how the current customized and default palettes are stored
and managed.

Regards,
Peter T