I created a userform in excel to be used for displaying data from an excel worksheet. When an item is selected from a combo box drop-down list, it populates all of the textboxes with the corresponding information. Many of the combo boxes contain dates. One box shows a FORECAST date of when a task will be completed and the other box shows the ACTUAL date of when that task was completed. Here is what I'm looking to do:
1) Compare the FORECAST textbox to the ACTUAL text box...if there is a date in the ACTUAL text box then do nothing but if there isn't a date in the ACTUAL box then do the following
2) f there is a date in the FORECAST box and the date is currently more than 6 days from today's date, then do nothing and keep background color as the normal WHITE
3) If there is a date in the FORECAST box and the date is currently less then 5 days from today's date, then turn the forecast box background color to YELLOW
4) If there is a date in the FORECAST box and If the forecast date is now past today's date then turn the forecast background color to RED
I'm really new to userforms and textboxes so not sure if the textbox needs to know that I'm working with dates and not text (or are dates considered text?) so that might be some of the issues I've been having. Thanks for your help.....
Private Sub UserForm_Initialize()
Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets("Sheet1")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
Me.ComboBox1.AddItem ws.Cells(i, "A").Value
Next i
End Sub
Private Sub ComboBox1_Change()
Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets("Sheet1")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If (Me.ComboBox1.Value) = ws.Cells(i, "A") Then
Me.TextBox1 = ws.Cells(i, "B").Value 'Task 1 Forecast Date
Me.TextBox2 = ws.Cells(i, "C").Value 'Task 1 Actual Completed Date
Me.TextBox3 = ws.Cells(i, "D").Value 'Task 2 Forecast Date
Me.TextBox4 = ws.Cells(i, "E").Value 'Task 2 Actual Completed Date
End If
Next i
End Sub
Display More