UserForm TextBox BackColor to match WorkSheet Cell

artzfx · Jun 16th 2004

I have a worksheet with cells that have a date in them and change colour ie, green, yellow, red depending on the date, ie date met, 5 days to go and date past.

I have a userForm with Textboxes representing these cells and wish for the Textboxes to also replicate this data and colour feature. I have used the follow code and the date works fine however the colours do not correspond to those in the worksheet cells they are referencing:

Code

tbE5.Text = Sheets("Tracker").Range("E5")
tbE5.BackColor = Sheets("Tracker").Range("E5")

Thanks for any help.

temposix · Jun 16th 2004

hi artzfx,

off the top of my head, try this instead:

tbE5.Text = Sheets("Tracker").Range("E5").value
tbE5.BackColor = Sheets("Tracker").Range("E5").interior.colorindex

you are better off explicity stating .value when trying to use the value of a cell.

cheers,
Paul.

artzfx · Jun 16th 2004

Thanks Paul,

I tried your code however it returns a run-time error 438 "object doesn't support this property or method". Any ideas?

Thanks
Neil

temposix · Jun 16th 2004

peculiar... i just tried it real quick and what i meant was:
Sheets("Tracker").Range("E5").Interior.Color

(ie 'Color' rather than 'ColorIndex')

anyway, which line was generating the error?

Dave Hawley · Jun 16th 2004

Hi Neil

Unfortunately the ColorIndex will not be any good for a Controls BackColor, what you need is;

Code

With Sheets("Tracker").Range("E5")
tbe5.Text = .Value
tbe5.BackColor = .Interior.Color
End With

Edit: I see Paul has beaten me to it

temposix · Jun 16th 2004

Quote from Dave Hawley

Hi Paul

Unfortunately the ColorIndex will not be any good for a Controls BackColor, what you need is;

Code

With Sheets("Tracker").Range("E5")

tbe5.Text = .Value

tbe5.BackColor .Interior.Color

End With

Quote from temposix

i just tried it real quick and what i meant was:
Sheets("Tracker").Range("E5").Interior.Color

(ie 'Color' rather than 'ColorIndex')

...indeed!

artzfx · Jun 16th 2004

Thanks for the help guys. Only problem is I now get an error saying Invalid use of Property for the BackColor. Where exactly should I be placing this code?

I removed the following code as per Daves instruction on my other thread

Code

tbE5.Text = Sheets("Tracker").Range("E5")

and have placed in the ControlSource of the Textbox properties.

Dave Hawley · Jun 16th 2004

Quote

Only problem is I now get an error saying Invalid use of Property for the BackColor

Sorry, I missed out the = sign. I have now corrected it.

Quote

Where exactly should I be placing this code?

It depends on when it should fire. How about the UserForm Initialize Event?

Code

Private Sub UserForm_Initialize()
With Sheets("Tracker").Range("E5")
tbe5.Text = .Value
tbe5.BackColor = .Interior.Color
End With
End Sub

Quote

I removed the following code as per Daves instruction on my other thread

There seems to be confusion. What I posted in your other post has nothing to do with what's been posted here.

**Andy Pope** · Jun 16th 2004

This will not work as expected if the worksheet cells are having the fill colour controlled via conditional formatting.

For that you will have to check the cells contents yourself.

artzfx · Jun 17th 2004

Thanks again Dave.

Andy you are correct. The worksheet cells colour is controlled by conditional formatting. Does that mean what I wish do do is not possible?

Dave Hawley · Jun 17th 2004

Arh, ok. Yes this will make a difference. What you will need to do is construct an If or Select Case Statement that will the same as your Conditional Formatting contruct.

Something like this;

Code

Private Sub UserForm_Initialize()
Dim vVal, vCol
Select Case Sheets("Tracker").Range("E5").Value
Case Is > 5
vCol = &HC0C0C0
Case 6 To 10
vCol = &HFF0000
Case Is > 10
vCol = &H80FFFF
Case Else
vCol = &HFFFFFF
End Select
With Sheets("Tracker").Range("E5")
tbe5.Text = .Value
tbe5.BackColor = vCol
End With
End Sub

Display More

You can copy the BackColor code direct from the Properties Window of the TextBox.

artzfx · Jun 17th 2004

Cheers again Dave, I will give it a go. Thanks.

Share