Excel VBA Userform IIf(IsDate) doesn't save blank

Question

Morning all, I've got a workbook, with a number of UserForms on which allow entry of multiple types of data, including text, numerics, and a lot of date fields. All seems to be working well, except if a user wants to delete a date, using the appropriate userform, it doesn't overwrite the value in the worksheet. Below is the code I'm using to check if the value in the userform is a date, and then populate the worksheet, but it's doing nothing. Any help appreciated.

ws.Cells(lastRow, txtCollectionDate.Tag).Value = IIf(IsDate(txtCollectionDate), CDate(txtCollectionDate), "")

Update: txtCollectionDate is a textbox, it's value originally was 09/01/2016, but the user wants to delete this, as it was entered in error. User should be able to highlight the value, press delete (keyboard), then save.

Please show the content of txtCollectionDate if it does nothing and mention what are your default date settings for short date in Excel and in your operating system. — Axel Richter
Thanks Axel, updated the main question. default date settings in Excel/OS are dd/mm/yyyy (UK style) — Tony Davis-Coyle
it doesn't overwrite the value in the worksheet - does that mean it does nothing? or that it replaces the value with blank? — SierraOscar
Hi Macro Man, it didn't do anything. I wanted it to replace the value with a blank — Tony Davis-Coyle

Axel Richter Axel Richter · Accepted Answer · 2016-04-07T07:45:59

Seems as if with IIf the then part will be processed even if the criterion part is not true. This leads to an error 13 type mismatch with CDate("").

So you should do:

If IsDate(txtCollectionDate) Then
 ws.Cells(lastRow, txtCollectionDate.Tag).Value = CDate(txtCollectionDate)
Else
 ws.Cells(lastRow, txtCollectionDate.Tag).Value = ""
End If

Edit:

Delete "Seems". That behavior is documented: https://msdn.microsoft.com/en-us/library/office/gg264412.aspx

Excel VBA Userform IIf(IsDate) doesn't save blank

1 Answers