textbox color change

[Sunswan]

is there a way to change a textbox color based on a cell color? I need to have two diferent colors applied.

[mikerickson]

If you are asking "can a textbox be set to change its color whenever the user changes the color of a particular cell", the answer is no. The user changing a cell's color triggers no events. There is no way that Excel can tell when to run the (hypothetical) change-textbox-color routine.

[Sunswan]

OK, Thanks for the answer. I think I will have to try a different approach. The cells will be colored when the textbox is opened, if that would make a difference.

[mikerickson]

What kind of text box is this? On a userform?

[Sunswan]

Thanks for the reply. Glad I peeked your interest. I have made a sample book for you to look at. Maybe this is possible maybe not. I have tried to use IF with ElseIf, but no luck

[Kenneth Hobs]

Explain in words what you want to achieve.

Try limiting your test to a shorter example. Just changing the background color for a textbox control in a userform is not difficult.

[Sunswan]

That is the smaller version. I have 82 textboxes the this will operate on. The textbox control you speak of, does it function off the excel sheet or does it require VBA? I have made several different attempts at this and am beginning to think it will not work. The form textboxes should be colored as the spreadsheet. The Elseif's I put in, in my lat attempt at making this work. The rest of the code work in my project.

[Sunswan]

The result I am looking for is for the textbox to show the colors that are on the excel sheet. They will change time to time and when the form is brought up I want it to show the changes. That is why I put it under an Initialize Sub.

[Kenneth Hobs]

Since your button opened the Userform, I am guessing that you want those background colors changed for some of the TextBox controls.

The point is, what is the criterion to change them? There are too many If, ElseIF, and such for me to figure out your logic. Select Case, is usually a better choice than If, ElseIf's in general. The logic is easier to see.

[Sunswan]

The best thing is to forget the ElseIf's. The rest of the code works without it, up to fillling the textboxes with red or yellow. The criterion to change the cells is a conditional format. I have all that working, I just need to find the code to have the textboxes fill in with the color that matches the cells. I used a 12 text box but if I could find just the code for filling one textbox red or yellow, I think I can take it from there, or at least I would like to try. I seem to learn more by expanding or adapting code for my projects

[Kenneth Hobs]

T1.BackColor = Worksheets("Admin").Range("O1").Interior.Color

[snb]

Without any if's, else's, select cases'


Private Sub Userform_Initialize()
sn = Sheets("Admin").Range("O1").CurrentRegion
For J = 1 To UBound(sn) * UBound(sn, 2)
Me("T" & J).Text = sn(((J - 1) \ UBound(sn, 2)) + 1, (J - 1) Mod UBound(sn, 2) + 1)
Me("T" & J).BackColor = Sheets("Admin").Cells(((J - 1) \ UBound(sn, 2)) + 1, (J - 1) Mod UBound(sn, 2) + 15).Interior.Color
Next
End Sub

NB. You will have to remove/comment out 'Option Explicit' first

[Sunswan]

Thanks to both of you for you reply.@ Kenneth Hobs your answer was my last resort. I have 84 textboxes to fill. Thanks for the interest, I am greatful that someone was willing to help. @snb I don't have the time right now to try this, but by looking at it I think it will work. I will know later this evening as I have Grandpa duties. I will let you know I will post later and you can check in the morning. Thanks to you also.

[Kenneth Hobs]

Of course you may want something like:
Private Sub Userform_Initialize()
Dim cRange As Range, c As Range, i As Integer
Set cRange = Worksheets("Admin").Range("O1:R3")
i = 0
For Each c In cRange
i = i + 1
Controls("T" & i).BackColor = c.Interior.Color
Next c
End Sub

[snb]

see the attachment


or KH's adapted code:

Private Sub Userform_Initialize()
For Each it In Sheets("Admin").Range("O1").CurrentRegion
J = J + 1
Me("T" & J).Text = it.Value
Me("T" & J).BackColor = it.Interior.Color
Next
end sub

[Sunswan]

To all that might be interested, the two codes I have for my project works with shaded cells, however when there is a conditional format color in the cell it will not pick up the format color.

[Kenneth Hobs]

Obviously conditional formatted interior colors are not cell interior colors. We can only work with the examples that you provide or what you tell us.

Provide an example if your real question was about how to get conditional formatted colors. Normally, one just uses the condition as the criterion though Excel 2010 did make dealing conditional formats a bit easier.

[Sunswan]

Thanks for the reply, In my last book I had the cells shaded, which lead to some confusion. What i have is a calendar set upand the cell have a base color and are changes using conditioinal formatting. Green is my base color and conditional formatting will be red and yellow. I have made a new attachment that does not include the base in it. The replys I got from you and snb, were great as far as the interior color. I must have not had it worded right. Thanks

[snb]

As far as I know Excel doesn't contain any straightforward method to evaluate conditionalformatting conditions, nor the resulting formatting.
It's only with an unecessary lot of circumstantial code that you can check the conditional formatting. I don't think it's worth while the effort to create such code.
So my advice is to use the 'normal' formatting. That's easy to evaluate like KH & I showed you before.

[Sunswan]

snb Thanks for the reply,once again. I was afraid of that, I have been working on this for about 5 weeks, and have not found anything close. I just had to ask someone that might know. Thanks to all for you time and patience, I know where to come when I get bouged down. Best of luck and health to all.

[Kenneth Hobs]

I modified Chip Pearson's code to work with some Conditional Formats. That is at work so I can not post my example just yet. IF you want to try Chip's methods, see: http://www.cpearson.com/excel/CFColors.htm

As snb said, the code may seem alot. Howsoever, if code works and meets your needs, use it if it does not slow your computations too much.

[Sunswan]

Thanks Kenneth Hobs, I have looked at the code of cperson and could not make ir work. More than likely it is my lack of knowledge more than anything else. I look forward to see what you have. If its close I hope to be able to piece it together. I have had codes that were close and been able to make them work. Its all about learning.