Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Browse Forms Controls and change TextBox color based on cell color
Hi!
I would like to dynamically change the background color in an array of TextBoxes in a Form, based on background colors in corresponding cells on the sheet. The textboxes are named so that they can be adressed but how do i typecast a recieved Control from the Controls collection to a TextBox control (I get type mismatch errors) and how do I set the BackColor-property ? I would be greatful for advices on this matter! // Sample pseudo-code below Best regards, StefanW Private Sub SetBackgnd() Dim tb As TextBox Dim x, y As Integer For y = 1 To 13 For x = 1 To 3 Set tb = MyForm.Controls("row" & y & "col" & x) <- I get error here 'Change background color of TextBox named "row'y'col'x'" 'based on backgroundcolor in Range("A1").Offset(row,col) Next res Next mt End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Browse Forms Controls and change TextBox color based on cell color
If all your indexes are correct, it could be as simple as your dim statement:
Dim tb As msforms.TextBox There are two Textboxes in excel--one from the Drawing toolbar and one from the control toolbox toolbar (used in Userforms). If you don't qualify which one you want, you'll get the drawing toolbar version. === There are other objects that are like this, too. You may want to start fully qualifying those controls in your userform--just in case. StefanW wrote: Hi! I would like to dynamically change the background color in an array of TextBoxes in a Form, based on background colors in corresponding cells on the sheet. The textboxes are named so that they can be adressed but how do i typecast a recieved Control from the Controls collection to a TextBox control (I get type mismatch errors) and how do I set the BackColor-property ? I would be greatful for advices on this matter! // Sample pseudo-code below Best regards, StefanW Private Sub SetBackgnd() Dim tb As TextBox Dim x, y As Integer For y = 1 To 13 For x = 1 To 3 Set tb = MyForm.Controls("row" & y & "col" & x) <- I get error here 'Change background color of TextBox named "row'y'col'x'" 'based on backgroundcolor in Range("A1").Offset(row,col) Next res Next mt End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Browse Forms Controls and change TextBox color based on cell c
Thanx alot Dave!
Your solution was like magic to my application! Best regards, StefanW "Dave Peterson" wrote: If all your indexes are correct, it could be as simple as your dim statement: Dim tb As msforms.TextBox There are two Textboxes in excel--one from the Drawing toolbar and one from the control toolbox toolbar (used in Userforms). If you don't qualify which one you want, you'll get the drawing toolbar version. === There are other objects that are like this, too. You may want to start fully qualifying those controls in your userform--just in case. StefanW wrote: Hi! I would like to dynamically change the background color in an array of TextBoxes in a Form, based on background colors in corresponding cells on the sheet. The textboxes are named so that they can be adressed but how do i typecast a recieved Control from the Controls collection to a TextBox control (I get type mismatch errors) and how do I set the BackColor-property ? I would be greatful for advices on this matter! // Sample pseudo-code below Best regards, StefanW Private Sub SetBackgnd() Dim tb As TextBox Dim x, y As Integer For y = 1 To 13 For x = 1 To 3 Set tb = MyForm.Controls("row" & y & "col" & x) <- I get error here 'Change background color of TextBox named "row'y'col'x'" 'based on backgroundcolor in Range("A1").Offset(row,col) Next res Next mt End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change tab color based on current color of a cell | Excel Discussion (Misc queries) | |||
Can you change the color of one cell based on the color of another | Excel Discussion (Misc queries) | |||
Excel: Syntax to change cell color based on color of another cell | Excel Worksheet Functions | |||
Change tab color based on a cell value | Excel Discussion (Misc queries) | |||
Q: How to change tab color based on cell value | Excel Programming |