Conditional Formatting of Text Boxes using VBA

[Denny57]

I have applied conditional formatting to data stored in a range cells in a Worksheet when the value of cells match the value of a cells within a separate range.

(eg Apply CF to all cells in Range C2:M20 whose value matches any value of a cell in Range B2:B8). Using a unique value (in Cell B1 in this example), the values of the rangeare called into corresponding TextBoxes in a User Form.
Note: There are a number of sets of such data in the Worksheet which will be called separately using the unique value (e.g. Week 1, Week 2 etc)

I am wondering if it possible to apply the same conditional formatting to corresponding textboxes in a user Form when the data is called from the Worksheet either by
a) Maintaining the existing CF and passing this directly to the Text Box or
b) to apply VBA code to each TextBox to match the CF rules in the Worksheet.

This is not essential but if it is possible I would be grateful if someone could point me in the right direction so I can make some investigations to possible solutions (Sample code is always welcome!)

Many thanks

 

[Micron]

For each tb you want to do this for you should be able to get the Range.DisplayFormat property from the cell it is bound to (assuming it is bound). That property gets what is currently showing in the sheet. So you'd set the interior/font/border etc. of the tb to whatever is currently showing in the sheet. Perhaps something like
Me.myTextbox.Interior.Color = Sheets("mySheet").Range("myRange").DisplayFormat.Interior.Color

 

[Denny57]

For each tb you want to do this for you should be able to get the Range.DisplayFormat property from the cell it is bound to (assuming it is bound). That property gets what is currently showing in the sheet. So you'd set the interior/font/border etc. of the tb to whatever is currently showing in the sheet. Perhaps something like
Me.myTextbox.Interior.Color = Sheets("mySheet").Range("myRange").DisplayFormat.Interior.Color

Thank you for providing this as a possible solution.. Unfortunately, the cells are not bound but vary depending on the value in a ComboBox and each tb can have up to 8 possble source cells.

This was only a "nice to have" so I think I will apply a simple code to just a small range of textboxes where the value is either zero or another value. I can therefore apply fomatting where the value is not zero.

 

[Denny57]

This is the generic code I am using to format those text Boxes where the value is greater that £0.00.

Private Sub txtBox1_Change()

If txtBox1.Text > "£0.00" Then
txtBox1.BackColor = vbGreen
End If
End Sub

I have a significant number of TextBoxes to which I wish to apply this formatting, however their naming is not seqential. Is it possible to add a line into a module that would select only textboxes perhaps from an array or list of TextBox sequence Numbers

For example I want to apply the same formatting to each of the following textboxes where the value is NOT "£0.00"
TextBox1, TextBox2, TextBox20, TextBox24.

I would use a separate module such as

Private Sub Format_Change_Click()
Dim n As Variable)
If txtBox.Text > "£0.00" Then
txtBox.BackColor = vbGreen
End If
Loop for each value of n - where n would be the numbers in the array

Many thanks

 

[Denny57]

Should have displayed as txtBoxn where n is the seqence number of the textBox

 

[Micron]

Use code tags and that won't happen.
The way I do this in Access is to use the code tag, which is also available in Excel. In Access, it is often done like this:

Dim ctl As Control

For Each ctl in Me.Controls
   If ctl.Type = acTextBox and ctl.Tag = "something" Then
      If ctl > 0 Then ctl.BackColor = vbGreen
  End If
Next

Then you don't need to worry about the control names. Doubtful that exact code rendition would loop over userform controls - you'd have to check. I would not use comparison operators with text, except for equals. I would also use either BeforeUpdate or AfterUpdate for textboxes most of the time. What you're using fires upon every keystroke. The tag value can be any text that you want.

 

[Denny57]

Use code tags and that won't happen.
The way I do this in Access is to use the code tag, which is also available in Excel. In Access, it is often done like this:

Dim ctl As Control

For Each ctl in Me.Controls
   If ctl.Type = acTextBox and ctl.Tag = "something" Then
      If ctl > 0 Then ctl.BackColor = vbGreen
  End If
Next

Then you don't need to worry about the control names. Doubtful that exact code rendition would loop over userform controls - you'd have to check. I would not use comparison operators with text, except for equals. I would also use either BeforeUpdate or AfterUpdate for textboxes most of the time. What you're using fires upon every keystroke. The tag value can be any text that you want.

Thank you for taking the time to reply. I think I have understood the code but I would appreciate some clarification of a couple of items.
1) Is there any relevance to the ac prefix to TextBox?
2) I assume the The ctl.Tag would be used to apply the suffix to the control name. I can have up to 40 TextBoxes that require formatting if their value is > 0 so how might I add the individual suffixes as these are not sequential. .

Meanwhile I have added individual code for each Textbox although I will alter the "Change" instruction

Private Sub txtFormattb1_Change()

    If ttxtFormattb1.Text > "£0.00" Then
        txtFormattb1.BackColor = vbGreen
    End If
End Sub

 

[Micron]

1) in Access the control type name for textbox is acTextbox. It's not the name of the control. I would have to research but I presume it's the same for Excel. So I'm saying "If the type of control is a textbox, then..." However, you'll see below that I did not use the right property name.

2)No. The tag property is a way of associating controls with one another, although it could have other purposes as well, I suppose. If code loops through controls and finds that the control being looked at has the specified tag, it does what the code tells it to do. If not, it moves on to the next one. Thus the control name doesn't matter. You'd have to add a tag value to every control you want to be in a group. As for acTextbox, that's how it is in Access but I didn't remember that it is ControlType, not just Type. I just researched this for Excel and find that there is no such property & I see that TypeName is what is typically used. Once again, I wonder why the Access and Excel development teams didn't attend the same meetings and raise the level of consistency between the two apps.
This works on a simple test userform I have. I put it in a standard module so that I could just run it without having to use some other event. I put "test" without quotes in tag property of 2 textboxes. Note that my suggestion is just one way you might achieve what you want. IMO it's simpler coding than worrying about what the control names are, and having to name them in some fashion that you can use - especially if you're beyond that point.

Function loopControls()
Dim ctl As control

For Each ctl In UserForm.Controls
     If TypeName(ctl) = "TextBox" And ctl.Tag = "test" Then
          Debug.Print ctl.Name
     End If
Next

End Function

 

[Denny57]

1) in Access the control type name for textbox is acTextbox. It's not the name of the control. I would have to research but I presume it's the same for Excel. So I'm saying "If the type of control is a textbox, then..." However, you'll see below that I did not use the right property name.

2)No. The tag property is a way of associating controls with one another, although it could have other purposes as well, I suppose. If code loops through controls and finds that the control being looked at has the specified tag, it does what the code tells it to do. If not, it moves on to the next one. Thus the control name doesn't matter. You'd have to add a tag value to every control you want to be in a group. As for acTextbox, that's how it is in Access but I didn't remember that it is ControlType, not just Type. I just researched this for Excel and find that there is no such property & I see that TypeName is what is typically used. Once again, I wonder why the Access and Excel development teams didn't attend the same meetings and raise the level of consistency between the two apps.
This works on a simple test userform I have. I put it in a standard module so that I could just run it without having to use some other event. I put "test" without quotes in tag property of 2 textboxes. Note that my suggestion is just one way you might achieve what you want. IMO it's simpler coding than worrying about what the control names are, and having to name them in some fashion that you can use - especially if you're beyond that point.

Function loopControls()
Dim ctl As control

For Each ctl In UserForm.Controls
     If TypeName(ctl) = "TextBox" And ctl.Tag = "test" Then
          Debug.Print ctl.Name
     End If
Next

End Function

Sorry for the delay in responding. I have been reading up on Tags and now have learned about another of the wonders of VBA. Thank you for introducing this element to me and for your invaluable help once again

 

[Micron]

Forgot to say that in my attempts to call a sheet sub from the immediate window, I ended up with a Function. You could just as well stick with a sub but I don't see that it will make much difference in your case. Glad to be of help & you're welcome. Hope you end up with something that works for you.