iserror(Timevalue())

[Guest]

Hiya,

I have a form with a textbox in it.
I want the user to either enter 'TBA' or a valid time.

I have put the following code together but need to make an amendment to get
it work:

If UCase(txttime) = "TBA" Or Not IsError(TimeValue(txttime)) Then
txttime = UCase(txttime)
Else
lblerrtime.Visible = True
End If

An error and halt in code only arises if the textbox contains something that
cannot be converted to a time.
Could it be because "The IsError function is used to determine if a NUMERIC
expression represents an error" - as it says in helpfile?

Any ideas what I can do? I'd rather not go down the VBA error handling route
as there are loads of textboxes I have to deal with (each resulting in
different outcomes).

Many thanks for any thought,

Basil

 

[Bob Phillips]

On e way or another, you have to go down the error handling rout, but you
can mitigate by using a global function, like so

Function IsOK(txt As String)
Dim mTime

On Error Resume Next
mTime = TimeValue(txt)
On Error GoTo 0
If IsEmpty(mTime) Then
If UCase(txt) = "TBA" Then
IsOK = UCase(txt)
Else
IsOK = ""
End If
Else
IsOK = mTime
End If

End Function


and call in your textbox code with

If IsOK(TextBox1.Text) <> "" Then
MsgBox "OK"
Else
MsgBox "Not OK"
End If



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thank you Bob.

I kept working on it in the method you suggested (which worked well).

Eventually, due to the complexity of some of the other checks on the form
(eg searching for different pieces of text in one of the comboboxes as a
validation - found that troublesome!), I worked on a different method.

The final method used basically transferred the data from the form into the
spreadsheet, which had the validation formulas in the next column. I then
took the data from this column to action the error messages for the form.

It is annoying how iserror(timevalue()) will work on a spreadsheet but not
in the code - Microsoft might want to consider a bit more consistency between
how a function responds on a spreadsheet compared to VBA.

Thanks,

Basil

 

[Bob Phillips]

HI Basil,

In line

Thank you Bob.

I kept working on it in the method you suggested (which worked well).

Good. It's a commonly used technique for situations such as this.

It is annoying how iserror(timevalue()) will work on a spreadsheet but not
in the code - Microsoft might want to consider a bit more consistency between
how a function responds on a spreadsheet compared to VBA.

VBA is not Excel specific, but is aimed to work with all office products.
Consider what IsError might mean on a Word document, or an MS Project file,
and you can see that there is a problem. Not insurmountable perhaps, but MS
probably think that there is no real problem with keeping Excel
functionality separate from VBA, albeit linked.

Bob

 

[Guest]

Can you make the default entry as TBA (and displayed in textbox) and only
detect if a change is made (by the entry of time). If changed then format
time if no change use TBA.

 

[Guest]

Yes, but I don't think it is necessary. I would rather not have TBA displayed
as default as it will virtually never be used.

The method I am using whereby the data is transferred to the spreadsheet
(column A) - in column B there are formulas giving 'TRUE' or 'FALSE' for each
criteria (row) which is then used as the validation within the VBA.

I don't know if using the spreadsheet in this way is common practice - but I
have yet to find a better method - it works quickly and perfectly.

And all because Iserror(Timevalue(xxxxx)) doesn't work on text in VBA (but
does in the spreadsheet).

Thank you for the suggestion - it has given me a different way of looking at
such problems which I'm sure will be useful in the future.

Basil