Conditional Format TextBox on UserForm to turn red after date is overdue

bclark3001

New Member
Joined
Jul 4, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Please someone help me solve this.....
I am trying to format my text boxes to turn red when a date is past due. For example: my employees have an array of training that needs to be completed. Some of the training has to be completed every year, 2 years, and 3 years. I have my cells conditionally formatted to turn red on these intervals. The problem is that if I do not have two computer screens when utilizing the userform it makes it difficult to tell which dates are past due because I can't view the spreadsheet and the userform at the same time. Any assistance would be greatly appreciated.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Not sure I understand. You can use CF on userform controls (going by your post title there)? Would have thought you could only use code, such as
If Me.txtDateDue < Date Then Me.txtDateDue.BackColor = vbRed
 
Upvote 0
Not sure I understand. You can use CF on userform controls (going by your post title there)? Would have thought you could only use code, such as
If Me.txtDateDue < Date Then Me.txtDateDue.BackColor = vbRed
So lets say for example: one of my textboxes is labeled "txtcyber" and it shows a date. This training is due every year. Can I use the Now() >= -365 function somewhere? Do I have to define this in the code somewhere?
 
Upvote 0
When you initialize the userform, check if your textbox values are < Date I(Date is a function and you should never use that word for any object name if you're going to use code). You could use Now function, but you don't care about the time portion I suspect. Either should work but as I posted, not what you posted as the test:
If Me.txtDateDue < Date Then Me.txtDateDue.BackColor = vbRed

Substitute your control name(s) for txtDateDue and/or substitute Date with Now. I'm assuming that if training was due yesterday, you want the control to show red but not if it was due today.
 
Upvote 0
When you initialize the userform, check if your textbox values are < Date I(Date is a function and you should never use that word for any object name if you're going to use code). You could use Now function, but you don't care about the time portion I suspect. Either should work but as I posted, not what you posted as the test:
If Me.txtDateDue < Date Then Me.txtDateDue.BackColor = vbRed

Substitute your control name(s) for txtDateDue and/or substitute Date with Now. I'm assuming that if training was due yesterday, you want the control to show red but not if it was due today.
When I use the < sign it does nothing. If I use the > sign it turns every person's date red not matter what it is.
 
Upvote 0
When I use the < sign it does nothing. If I use the > sign it turns every person's date red not matter what it is.
Where exactly am I supposed to be inputting this formula? I have put it into Userform AddControls, Userform Activate, and also tried putting in in the textbox_change as well.
 
Upvote 0
That piece of code is supposed to be going in your userform module. It's not a formula, so are you putting it in a sheet cell somewhere?
Can you post a file copy somewhere? If your data is private, just copy/paste a several bogus records that can be used to solve the issue and remove everything else that you don't want anyone to see? Or you can just try putting that in the userform initialize event.
 
Upvote 0
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
''''''''''Adding the Double Click Display Information Function to the ListBox''''''''''''
txtSearch.Text = ListBox1.Column(0)
If txtSearch.Text = ListBox1.Column(0) Then
txtID.Text = ListBox1.Column(0)
cmbPAFSC.Text = ListBox1.Column(1)
cmbRANK.Text = ListBox1.Column(2)
txtLname.Text = ListBox1.Column(3)
txtFname.Text = ListBox1.Column(4)
txtMI.Text = ListBox1.Column(5)
txtdate.Value = Format(ListBox1.Column(6), "dd-mmm-yy")
txtFP.Value = Format(ListBox1.Column(7), "dd-mmm-yy")
txtCTIP.Value = Format(ListBox1.Column(8), "dd-mmm-yy")
txtCUI.Value = Format(ListBox1.Column(9), "dd-mmm-yy")
txtOPSEC.Value = Format(ListBox1.Column(10), "dd-mmm-yy")
txtRELIG.Value = Format(ListBox1.Column(11), "dd-mmm-yy")
txtEID.Value = Format(ListBox1.Column(12), "dd-mmm-yy")
txtCULT.Value = Format(ListBox1.Column(13), "dd-mmm-yy")
txtCOLREP.Value = Format(ListBox1.Column(14), "dd-mmm-yy")
txtUOF.Value = Format(ListBox1.Column(15), "dd-mmm-yy")
txtPDFR.Value = Format(ListBox1.Column(16), "dd-mmm-yy")
txtLOWB.Value = Format(ListBox1.Column(17), "dd-mmm-yy")
txtLOWA.Value = Format(ListBox1.Column(18), "dd-mmm-yy")
txtMH.Value = Format(ListBox1.Column(19), "dd-mmm-yy")
txtICE.Value = Format(ListBox1.Column(20), "dd-mmm-yy")
txtTCCC.Value = Format(ListBox1.Column(21), "dd-mmm-yy")
txtEAST.Value = Format(ListBox1.Column(22), "dd-mmm-yy")
txtSAPR.Value = Format(ListBox1.Column(23), "dd-mmm-yy")
txtVRED.Value = Format(ListBox1.Column(24), "dd-mmm-yy")
txtSERE.Value = Format(ListBox1.Column(25), "dd-mmm-yy")
txtCBRNE.Value = Format(ListBox1.Column(26), "dd-mmm-yy")
txtCATM.Value = Format(ListBox1.Column(27), "dd-mmm-yy")
txtGAS.Value = Format(ListBox1.Column(28), "dd-mmm-yy")
txtISOP.Value = Format(ListBox1.Column(29), "dd-mmm-yy")
txtAPGF.Value = Format(ListBox1.Column(30), "dd-mmm-yy")
txtAMXS.Value = Format(ListBox1.Column(31), "dd-mmm-yy")
txtMED.Value = Format(ListBox1.Column(32), "dd-mmm-yy")
txtAEF.Value = Format(ListBox1.Column(33), "dd-mmm-yy")
End If

This is how my dates get into my textboxes. Each employee has different dates because they complete the training at different times which means their due dates are not all the same. Is there something I'm missing and this is the reason it won't work?
 
Upvote 0
'''''''''''Setting up the Personnel Listbox'''''''''''''''''
With ListBox1
.ColumnCount = 34
.ColumnHeads = True
.TextAlign = fmTextAlignCenter
.MultiSelect = fmMultiSelectSingle
.RowSource = "MOB_FOLDER!A2:AH10000"
.ColumnWidths = "35,40,50,100,100,50,150,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200"
End With


This code above is the only code in the UserForm Initialize besides drop down menu's.
 
Upvote 0
That code all about the listbox. You want to be dealing with textbox events & code. It seems your listbox double click event populates the userform textboxes in one shot, so the change event might be the simplest/best. TBH I can't bring myself to study it (see note below). Something like (where you use your own textbox names)
VBA Code:
Private Sub TextBox2_Change()
If Me.TextBox2 = "test" Then
   Me.TextBox2.BackColor = vbRed
   Else
   Me.TextBox2.BackColor = vbWhite
End If

End Sub
I used the word "test". Can't recall what your criteria is at the moment.
The else part should ensure that if it goes red it won't stay that way if circumstances would otherwise cause that.
Note: Please code tags when posting code (use vba button on posting toolbar) to maintain readability and indentation. If I could earn .25 cents every time I have written that here and elsewhere in the last few years I could pay off the new car I recently bought! ;)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top