Need to Compare userform textboxes that contain dates and have color of textbox change when certain conditions are met

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


  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags

    Note: no apostrophe in the tags, just used for demonstration here.

    ['code]


    your code goes between these tags


    ['/code]


    Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

  • Hi AJ,


    You need to change the text in the textbox to a date value using cdate, something like the following should work:



    Based of your text, I have not looked at your attachment sorry, I did not realise it was there.


    Also note that if you move the date value to and from the textboxes to cells etc, and use the format command to format them, it is a safe way to ensure you do not lose the correct date through some excel autoformatting.

    Something like:

    Code
    1. format(cdate(me.textbox1.text), "Short Date")
  • For some reason it doesn't work when I use the "AfterUpdate" but sort of works when I use "Change" instead. It also seems to be ignoring if Textbox2 is empty or not because when I run it, it will color TextBox1if it has a past due date even though it shouldn't because textbox2 had a date in it already which means skip with coloring textbox1.


    Any ideas?


    Also curious how this works with no End If statement

  • HI AJ,


    The code works fine on an example form with the two textboxes on it, if you want to load an example of what you are working with I will incorporate the code and we can get it working.


    The If statement works as shown if it is on a single line, however if you have multiple lines of code you need to have an enter after THEN and include an END IF statement.


    I tried downloading the example sheet you have but it is a .docx format and does not open, hopefully it is not a virus.


    Regards

    Justin

  • My Spreadsheet has at least 16 columns that contain dates; the first date is a forecast date of when the task will be completed and the 2nd column is the actual date the task was completed.



    The first 8 columns list First Name, Last Name, ID#, Address, City, State, Zip, and County. The remaining columns as I said are for forecast and completed tasks. Code is below and I uploaded a PDF of what the userform looks like…


  • I should have added that dates start at TextBox9 (Forecast Date) so of course the first box I would want to see if not empty would be TextBox10 (Actual Date). If TextBox is not empty then nothing to do and go to TextBox12 (paired with TextBox11). If TextBox10 is empty then the color routine I was looking for above would now come into play and would be used on TextBox9 (and so on...TextBox13 & Textbox14.......TextBox15 and Textbox16.....etc)

  • Hi AJ,


    You need to attach an excel worksheet with an example of your data, not a PDF and not a docx. If you have sensitive data just make a backup copy with all the data deleted and add some example data. Nobody is going to remake your form and create example data for you.


    Justin

  • Also the code you have shown is not going to work, it appears to be the combobox change event combined with the userform initialize event but with the second sub name removed.

    you should be able to replace your code above with something like:


    Code
    1. Private Sub UserForm_Initialize()
    2. Dim MyArr As Variant
    3. Dim ws As Worksheet: Set ws = Sheet1
    4. MyArr = ws.Range("A1").CurrentRegion
    5. Me.ComboBox1.List = MyArr
    6. End Sub


    Code
    1. Private Sub ComboBox1_Change()
    2. Dim i As Long, x As Long
    3. Dim ws As Worksheet: Set ws = Sheet1
    4. i = Me.ComboBox1.ListIndex + 1
    5. For x = 1 To 26
    6. Me.Controls("TextBox" & x) = Me.ComboBox1.Column(x)
    7. Next x
    8. End Sub


    Justin

  • The code you supplied works; sort of. For my database, the number of rows is never a set number but the number of columns will stay the same. In my current worksheet data, there are 64 rows and 119 columns (118 TextBoxs * one ComboBox). In line 5 of the code you supplied you had “For x = 1 to 26” which works fine but it only returns values for the first 26 TextBoxes. I changed that number to 118 and I got an error message stating “Could not find the specific object.” The highest value I could put “For x = 1 to 63” and anything greater than 63 returns the error message.


    I can’t figure out why it’s stopping at 63 (looks like it's tied to how many rows there are) and not at 118. Any ideas? Thanks

  • I just noticed that I'm missing Textbox64 (I have Texbox63 and Textbox65) so I must have deleted it and I think that is why I'm getting the error. Total coincidence that I had 64 rows and it got stuck at Textbox64. Is there a way to create Textbox64 again?

  • ... Is there a way to create Textbox64 again?

    Hello,


    As you insert a new TextBox .... the F4 key opens the Properties window ... where you can change the Name to TextBox64 (first row)


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Neve mind...the problem was the missing textbox and it now works once I made sure there wasn't a textbox missing. Now on to my color-coding. You supplied the code on Jan 1st. Does that color-code still apply to the code you gave me on Jan 4th? Thanks, Justin