Mira
Scooby Slapper
Joined: 14 Feb 2009
Karma :  
|
 Posted: 14:19 - 08 Feb 2012  Post subject: VBA for excel. Allow users to enter time with either . or : |
 |
|
Hi folks,
I did a search on a excel forum and found some code that does what i want (mostly) which is to allow users to enter a time in either 7.3 or 7:30 and have it display as 7:30. Except it doesn't work if some one enters a time less than 1 for expamle enters 0.3 (meaning 0:30) it shows 7:12
| Code: |
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C4:V23"
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsNumeric(.Value) And .Value >= 1 Then
.Value = (Int(.Value) + (.Value - Int(.Value)) * 100 / 60) / 24
.NumberFormat = "[h]:mm"
End If
End With
End If
ws_exit:
Application.EnableEvents = TrueEnd Sub |
I have tried changing the Part of the code
If IsNumeric(.Value) And .Value >= 1 Then
to
If IsNumeric(.Value) And .Value >= 0 Then
That fixes the 0.3 problem but now if you try and enter 1:00 it changes it to 0:04 instead of leaving it as 1:00
Any sugestions?
Thanks, Mira. 
____________________
I have heard there are troubles of more than one kind. Some come from ahead and some come from behind. But I've bought a big bat. I'm all ready you see.
Now my troubles are going to have troubles with me! ~ Dr Seuss. |
|
daemonoid
World Chat Champion
Joined: 27 Jun 2008
Karma : 
|
Posted: 16:10 - 08 Feb 2012  Post subject: |
|
|
not a vba programmer, but as a rough guess you should use this test instead:
if instr(1, .Value, ".") Then
instr returns the position of the . also so you can use that for the replacement. You'll have to tidy up the syntax yourself, like I said, don't do vba.
____________________
current: ducati monster 750
past: hyosung gt250r, bajaj pulsar 180, hyosung gt 125 comet
@thomasgarrard | www.straitjkt.com | www.racingseven.com |
|
Mira
Scooby Slapper
Joined: 14 Feb 2009
Karma :
|
| Posted: 23:30 - 08 Feb 2012 Post subject: |
|
|
Thanks 
I'm not a VBA programmer either but I'll have a play with what you suggested and see what happens  
____________________
I have heard there are troubles of more than one kind. Some come from ahead and some come from behind. But I've bought a big bat. I'm all ready you see.
Now my troubles are going to have troubles with me! ~ Dr Seuss. |
|
Help & FAQs
Search BCF
Members
Groups
Events Calendar
rating
rating