RGB value of cell interior

[Guest]

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

 

[keepITcool]

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 :

 

[Guest]

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:

 

[keepITcool]

show me your code




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


Brotha lee wrote :

 

[Guest]

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:

 

[keepITcool]

yes...
strange and unexpected
but easily solved with

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





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


Brotha lee wrote :

 

[Peter T]

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.ColorIndex) ' fails
x = Range("B2").Interior.Color ' works

Regards,
Peter T

 

[keepITcool]

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.ColorIndex)
Case Else
Mycmd.BackColor = vbWindowBackground
End Select


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


Peter T wrote :

 

[Peter T]

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

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