Another umpth date format question

[NotMe2]

Hi willingfull reader,
I thought I had it figured it out to format my dates into my worksheets with this code

Dim bd As Date
Dim ld As Date
Dim dd As Date
Dim de As Date

bd = tbDob.Text
ld = tbLastDive.Text
dd = tbDep_date.Text
de = tbDisembark_date.Text

tbDob.Text = Format(bd, "D.MM.YYYY")
'some insert code here for worksheet 1
tbLastDive.Text = Format(ld, "MMM.YYYY")
'some insert code here for worksheet 2
tbDep_date.Text = Format(dd, "D.MM.YYYY")
tbDisembark_date.Text = Format(de, "D.MM.YYYY")
'some insert code here for worksheet 2

This formatting works fine if all the textboxes are filled out, but not always is all the date info available, so it is left blank when other data is filled out into my userform.
When this happens I get an error 13 mismatch. I have been reading up about this, but since I am a newbie I couldn't figure it out, only that it could have something to do with my variables.
I found that strange since when I used just 1 variable say Dim bd as Date and omitted the format for the other textboxes it worked fine.

So I tried it with OR statement

If tbDob.Text = "" Or tbLastDive.Text = "" Or tbDep_date.Text = "" Or tbDisembark_date.Text = "" Then
'End If

Which didn't work because I got the same error 13 back.
I tried with Elseif statements also to no avail either.
If anyone could help me out with this problem, I would be eternally gratefull

 

[Alex Blakenburg]

It is unclear as to what you are trying to do.
Once you dim a variable as date it won't allow you to put in a blank or "".

See if the below gives you any ideas.
PS: If you are writing a date to the spreadsheet, you don't want to use the Format function or you will finish up with a text value instead of a date.

Sub FormatDate()

    Dim bd As String
   
    bd = tbDob.Text
    If IsDate(bd) Then
       tbDob.Text = Format(CStr(bd), "D.MM.YYYY")
    End If

End Sub

 

[NotMe2]

It is unclear as to what you are trying to do.
Once you dim a variable as date it won't allow you to put in a blank or "".

See if the below gives you any ideas.
PS: If you are writing a date to the spreadsheet, you don't want to use the Format function or you will finish up with a text value instead of a date.

Sub FormatDate()

    Dim bd As String
  
    bd = tbDob.Text
    If IsDate(bd) Then
       tbDob.Text = Format(CStr(bd), "D.MM.YYYY")
    End If

End Sub

HI Alex,
Thanks for this, I will try it out.
What I try to accomplish is that all my dates are being formatted to my worksheets and if a textbox for a date is left open it should be ignored by the vba code and just continue.
Your suggestion to Dim as a string, did the trick, I only changed this and added MMM.YYYY to my custom format cells. I did get a date value and not a text
Thank you so much.

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.