Cdate error when operating on blank string

bcfaigg

Board Regular
Joined
Dec 1, 2005
Messages
78
Hi, this is the first time I have posted on this wonderful source of info, so be gentle with me.

I am designing a form based tool which involves entering quite a lot of data on to a workbook, using vba forms.

I am using lots of expressions like

worksheets("Milestones").cells(SelectionRow, 21).value=cdate(txtDeadline.text)

I am also using an error handling procedure to ensure that only dates are entered, like:

on error goto DateError
...
unload me
goto finish:
DateError:
msgbox("One of the values entered is not a valid date. Please check and try again.")
finish:


This works perfectly except for the fact that if a text box is left blank on the form, you get the same error. I.e. Cdate operating on a blank string gives the same error.

The obvious (in my eyes) solution is wherever I use Cdate to create a function e.g. ConvertDate as follows:

function ConvertDate(date_to_convert)
if date_to_convert="" then goto continue
convertdate=cdate(date_to_convert)
continue:
end function


Can anyone think of a more elegant/better solution to my dilemma.
I need some sort of validation on the dates entered via the form, but need the ability for text boxes to be left blank at times too.
Thanks
Gopes
P.S. Apologies for poor formatting - cannot download programs such as VB HTML Maker at work
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
You can use the IsDate function to test for dates.

Is that what you're after?
 

bcfaigg

Board Regular
Joined
Dec 1, 2005
Messages
78
Problem is that there are more than 50 text boxes with dates on a number of forms. If I understand the IsDate function correctly, I would have to type in the corresponding number of times something along the lines of:

...

if isdate(txtDeadline.text)=false then
msgbox(Please enter date in valid format)
goto finish
end if

...


finish:
end sub

this would significantly increase the number of lines of code to debug (not to mention the typing). I had hoped that using the "on error" approach, I could get around this.

Thanks
Gopes
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Gopes

Can you explain further what you want to do?

I can't see how the 'On Error' approach would avoid repeating code, surely you would still have to have code for each textbox.

Where are these textboxes located?
 

bcfaigg

Board Regular
Joined
Dec 1, 2005
Messages
78

ADVERTISEMENT

Basically, I have a number of forms, each with several text boxes, many of which are dates.
When I fill all or part of the form and click the 'Update' button, the data is dumped onto several worksheets. Sometimes the contents of one text box is dumped onto more than one worksheet.
What I want to do is have a simple system that forces only dates to be entered in the relevant text boxes.
Without error handling, if you try and use Cdate on an unrecognized date, you get a Runtime Error 13 - Type Mismatch.

Using the error handling described above, I make a msgbox which prompts the user to revise his dates and correct the mistake. This works fine, as there is very little additional code to add (I have already built the system, and am now at the fine tuning stage) except for the fact that as this is basically a process tracking system, the dates will not all be entered simultaneously. I need the facility to leave date textboxes blank without generating the error message.

the code when I click on the update button on my form is something like
worksheets("Milestones").cells(SelectionRow, 21).value=cdate(txtDeadline.text)

and at the bottom of the update sub, I have the error routine which simply brings up a simple msgbox stating that a date is incorrect and bypassing the Unload Me stage, i.e. taking the user back to the form.

The solution in my original post should work, as would the more cumbersome use of if...end if statements around each textbox code, but ideally would like a simple and elegant way to achieve this.

Does this all make sense? It may be that i don't need to worry - does repeatedly calling a user-defined function (say ConvertDate) significantly slow things down?

Thanks for your help.
Gopes
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Gopes

Where are these text boxes located?

Are they on a userform?
 

bcfaigg

Board Regular
Joined
Dec 1, 2005
Messages
78
Apologies Norie,
i thought I had responded to your question. Yes, text boxes are located on a userform.
I have used the "On Error goto" solution, I hypothesized in my original post.
Might not be very elegant, but seems to do the trick.
Thanks for your help.
Gopes
 

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top