If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Harlan didn't use "-" and he didn't use the Format statement, either:
Option Explicit Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean) If IsError(Evaluate("TIMEVALUE(""" & txtTime.Value & """)")) Then txtTime.Value = "" Cancel = True End If End Sub Dave Unger wrote: Hi Tom, I'll have to do a 180 from what I just said - for some reason when I did a copy/paste of Harlan's code, an extra character got thrown in. Now that I've corrected that, ALL number combinations seem to pass. My form field (txtTime) restricts the entry to numbers only, the 2nd line in the code converts it to a time format. Thanks for having a look, Dave Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean) txtTime.Value = Format(txtTime.Value, "00:00") If IsError(Evaluate("TIMEVALUE(""*" & txtTime.Value & """)")) Then txtTime.Value = "" Cancel = True End If End Sub -- Dave Peterson |
#12
|
|||
|
|||
Dave,
I only use the Format statement because I'm entering the time as 900, not 09:00 - however, I've tried it without the Format statement, doesn't work either. It's interesting that you mention the "-", I don't see it in my posting, but that's the extra character I was getting when I copied and pasted Harlan's example. thanks, Dave |
#13
|
|||
|
|||
I didn't realize you were entering your times that way, but I added that format
statement back and it sure seemed to work ok for me (without that "-" stuff). But if I enter 966, your format statement makes it look like: 9:66 and excel is smart enough to change it to 10:06. Is that bad? If yes, then maybe you can incorporate Harlan's error checking and your conversion into one procedu Option Explicit Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim res As Variant Dim myStr As String myStr = Format(txtTime.Value, "00:00") res = Evaluate("TIMEVALUE(""" & myStr & """)") If IsError(res) Then txtTime.Value = "" Cancel = True Else txtTime.Value = Format(res, "hh:mm") End If End Sub Dave Unger wrote: Dave, I only use the Format statement because I'm entering the time as 900, not 09:00 - however, I've tried it without the Format statement, doesn't work either. It's interesting that you mention the "-", I don't see it in my posting, but that's the extra character I was getting when I copied and pasted Harlan's example. thanks, Dave -- Dave Peterson |
#14
|
|||
|
|||
That appears to be a bug in the google beta. It appears to add a hyphen
sometimes when you paste code. Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim s as String s = Trim(txtTime) if instr(s,":") then s = replace(s,":","") End if if len(s) = 4 then if isnumeric(s) then txtTime.Value = format(clng(s),"00:00") if Not IsError(Evaluate("TIMEVALUE(""" _ & txtTime.Value & """)")) then Exit sub End if end if End if txtTime.Value = "" Cancel = True End Sub there should be no hyphens (-) in this code although there is an underscore "_" -- Regards, Tom Ogilvy "Dave Unger" wrote in message oups.com... Dave, I only use the Format statement because I'm entering the time as 900, not 09:00 - however, I've tried it without the Format statement, doesn't work either. It's interesting that you mention the "-", I don't see it in my posting, but that's the extra character I was getting when I copied and pasted Harlan's example. thanks, Dave |
#15
|
|||
|
|||
Hello,
Dave and Tom, thanks for your code, but in both cases, it would let all entries through, valid or invalid. I'm wondering, I'm using Excel 97, and the REPLACE function in Tom's code, "s = Replace(s, ":", "")" wasn't recognized, used "s = Left$(s, 2) & Right$(s, 2)" instead. Might it be that Excel 97 handles the ISERROR function differently than later versions? I won't bother you any more with this. For the time being I'll go back to using the ONERROR GOTO statement, that seems to work for me. Tom, thanks for the heads up about Google inserting the "-", that did throw me off at first. On a side note, what's the reason for the CLng in Format(CLng(s), "00:00")? Harlan, Tom, Dave, thank you for your efforts, much appreciated, and it was a learning experience for me. Until later, Thanks Dave "Tom Ogilvy" wrote in message ... That appears to be a bug in the google beta. It appears to add a hyphen sometimes when you paste code. Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim s as String s = Trim(txtTime) if instr(s,":") then s = replace(s,":","") End if if len(s) = 4 then if isnumeric(s) then txtTime.Value = format(clng(s),"00:00") if Not IsError(Evaluate("TIMEVALUE(""" _ & txtTime.Value & """)")) then Exit sub End if end if End if txtTime.Value = "" Cancel = True End Sub there should be no hyphens (-) in this code although there is an underscore "_" -- Regards, Tom Ogilvy "Dave Unger" wrote in message oups.com... Dave, I only use the Format statement because I'm entering the time as 900, not 09:00 - however, I've tried it without the Format statement, doesn't work either. It's interesting that you mention the "-", I don't see it in my posting, but that's the extra character I was getting when I copied and pasted Harlan's example. thanks, Dave |
#16
|
|||
|
|||
Thanks ste,
The Cancel=True was the key Dave |
#17
|
|||
|
|||
Replace was added in xl2k.
This line: s = replace(s,":","") could be replaced with: s = application.substitute(s,":","") and it'll work in all versions. And format() works on numbers. So Tom converted the string in the textbox to a number before he applied the formatting. David Unger wrote: Hello, Dave and Tom, thanks for your code, but in both cases, it would let all entries through, valid or invalid. I'm wondering, I'm using Excel 97, and the REPLACE function in Tom's code, "s = Replace(s, ":", "")" wasn't recognized, used "s = Left$(s, 2) & Right$(s, 2)" instead. Might it be that Excel 97 handles the ISERROR function differently than later versions? I won't bother you any more with this. For the time being I'll go back to using the ONERROR GOTO statement, that seems to work for me. Tom, thanks for the heads up about Google inserting the "-", that did throw me off at first. On a side note, what's the reason for the CLng in Format(CLng(s), "00:00")? Harlan, Tom, Dave, thank you for your efforts, much appreciated, and it was a learning experience for me. Until later, Thanks Dave "Tom Ogilvy" wrote in message ... That appears to be a bug in the google beta. It appears to add a hyphen sometimes when you paste code. Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim s as String s = Trim(txtTime) if instr(s,":") then s = replace(s,":","") End if if len(s) = 4 then if isnumeric(s) then txtTime.Value = format(clng(s),"00:00") if Not IsError(Evaluate("TIMEVALUE(""" _ & txtTime.Value & """)")) then Exit sub End if end if End if txtTime.Value = "" Cancel = True End Sub there should be no hyphens (-) in this code although there is an underscore "_" -- Regards, Tom Ogilvy "Dave Unger" wrote in message oups.com... Dave, I only use the Format statement because I'm entering the time as 900, not 09:00 - however, I've tried it without the Format statement, doesn't work either. It's interesting that you mention the "-", I don't see it in my posting, but that's the extra character I was getting when I copied and pasted Harlan's example. thanks, Dave -- Dave Peterson |
#18
|
|||
|
|||
Dave,
The reason I was asking - the Format line seems to work whether CLng is present or not. As I'm still on the steep part of the learning curve, I probably wouldn't have realized the need for it. Dave |
#19
|
|||
|
|||
Tom
True. I am finding I have to be very careful when copying from postings. The hyphen(s) are thrown in with more frequency lately. I have noticed it in posted worksheet formulas also. Gord Dibben Excel MVP On Sun, 20 Mar 2005 17:41:23 -0500, "Tom Ogilvy" wrote: That appears to be a bug in the google beta. It appears to add a hyphen sometimes when you paste code. |
#20
|
|||
|
|||
Gord Dibben wrote...
True. I am finding I have to be very careful when copying from postings. The hyphen(s) are thrown in with more frequency lately. I have noticed it in posted worksheet formulas also. .... I wrote about this a week ago. It's unsafe to copy anything from Google Groups beta *except* from *ORIGINAL* versions of postings which include all the lovely NNTP tags above the body of the message. Those originals seem to be literal text without any HTML interpretation, so HTML 'soft' hyphens become visible. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
focus stuck in control | Walter | Using Forms | 2 | March 12th, 2005 01:45 PM |
How do I programmatically set focus to the first control in the tab order | [email protected] | General Discussion | 4 | February 24th, 2005 08:18 AM |
Inconsistent focus in fields | Keith | Using Forms | 2 | December 10th, 2004 09:15 AM |
Odd Focus problem with Form/subform | WingLady | Using Forms | 1 | December 3rd, 2004 02:05 AM |
Change focus to control | John Barnes | General Discussion | 3 | December 2nd, 2004 02:29 PM |