Solved: VBA Userforms and Cell based formulas not getting on together!

[maxhayden]

Hi everyone!

I have written a user form that adds dates and percentages to cells on a spreadsheet.

I have an If statement in another cell that analyses the values that have been input:

=IF(H7="","",IF(AND(H7<TODAY(),I7<100%),"Overdue",IF(AND(H7=TODAY(),I7<100% ),"Due Today",IF(AND(H7=TODAY()+1,I7<100%),"Due Tomorrow",IF(AND(G7<TODAY(),H7>TODAY()),"In Progress",IF(G7>TODAY(),"Not Started",IF(I7=100%,"Complete","Unknown")))))))

Yes! Its ugly alright!!

Anyway!! When I input the dates manually, the formula works fine!

When I input the dates using the user form - it doesn't recognise it as a date and comes up with "Not Started" for any date I put in!!!

Can anyone help me? I don't really want to run the above formula as a macro, because I want it to be "real time" without having to run the macro constantly!

[maxhayden]

FYI - If I then go to the cell with the date, double click on it, then press enter to come out of it, it then activates the formula!!

wtf?

[maxhayden]

Here's a short version of the user form code. Can I input the value into the cell in a different way that will maybe do the same as pressing "enter" after it has insert the value - or make it acknoledge that it is actually a date.

For h = 7 To 100
If CheckBox1 = True Then
Cells(h, 3) = AddTask.TextBox1.Value

[Kenneth Hobs]

Textbox values are strings, not dates. You need to convert strings to a date. e.g.
[vba] Cells(h, 3).Value = CDate(AddTask.TextBox1.Value)[/vba]

You might also want to set the numeric format. e.g.
[vba]With Worksheets("Sheet1").Range("H3")
.Value = CDate(AddTask.TextBox1.Value)
.NumberFormat = "mm/dd/yyyy"
End With[/vba]

[maxhayden]

Thank you Mr Hobs! You are a legend amongst men and women alike! It has worked a treat.

Being fairly new to VBA I wasn't familiar with the cdate code.

Thanks again!