PDA

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



maxhayden
05-26-2009, 06:37 AM
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
05-26-2009, 07:05 AM
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
05-26-2009, 07:10 AM
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
05-26-2009, 07:32 AM
Textbox values are strings, not dates. You need to convert strings to a date. e.g.
Cells(h, 3).Value = CDate(AddTask.TextBox1.Value)

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

maxhayden
05-26-2009, 07:59 AM
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!