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

[bclark3001]

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.

 

[Micron]

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

 

[bclark3001]

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?

 

[Micron]

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.

 

[bclark3001]

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.

 

[bclark3001]

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.

 

[Micron]

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.

 

[bclark3001]

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?

 

[bclark3001]

'''''''''''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.

 

[Micron]

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)

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!