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

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

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