Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
iserror(Timevalue())
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
iserror(Timevalue())
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) "Basil" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
iserror(Timevalue())
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" wrote: 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) "Basil" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
iserror(Timevalue())
HI Basil,
In line "Basil" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
iserror(Timevalue())
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. -- Jim at Eagle "Bob Phillips" wrote: HI Basil, In line "Basil" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
iserror(Timevalue())
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 "Jim at Eagle" wrote: 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. -- Jim at Eagle "Bob Phillips" wrote: HI Basil, In line "Basil" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
timevalue function | Excel Worksheet Functions | |||
TimeValue formula | Excel Worksheet Functions | |||
Calculation to subtract TimeValue from Now() Value | Excel Worksheet Functions | |||
DATEVALUE and TIMEVALUE | Excel Programming | |||
Need Help with Code - TimeValue | Excel Programming |