Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25
  1. #16
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    10,056
    Thanks
    426
    Thanked 1,612 Times in 1,456 Posts
    Zeddy,

    Ok, you got me stumped! What does this line of code do?

    If I enter 1377 it jumps right over this line of code and presents my message and leaves the cursor at the next line.

    If I enter a text value like 'K' it errors out.

    What am I missing?

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  2. #17
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,460
    Thanks
    168
    Thanked 656 Times in 624 Posts
    Hi RG

    When you enter 1377, then, just as before, your error trapping kicks in, i.e. 1377 is still NOT allowed because 77 minutes is not allowed.

    Now, enter 1230 into say, [A4]
    It will be 'converted' and display as 12:30 in the cell.
    In the formula bar, it will show as 12:30:00 i.e. will show the contents in te formula bar as hh:mm:ss

    Now, in the formula bar, change this 30 to 35
    The cell will now display 12:35
    No error message is displayed.

    ..but, if you tried this edit with your posted file, it will trigger your error message.

    So, the line of code I put in now allows you to re-enter say, a new four-digit valid number e.g. 1145 into an existing time-cell, or edit the contents in the formula bar, without triggering the error message.

    zeddy

  3. #18
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,460
    Thanks
    168
    Thanked 656 Times in 624 Posts
    Hi RG

    ..I was thinking about allowing text entries so that instead of a time, the User could enter something like "holiday", or "sick", "fired', "sacked' ,
    ..maybe "promoted" even

    ..but that's something else.

    zeddy

  4. #19
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,460
    Thanks
    168
    Thanked 656 Times in 624 Posts
    Hi RG

    ..so another fix is still required to deal with 'text' input etc etc etc
    ..so my update wasn't a 'six' after all.
    ..not even a 'four'
    ..umpires call

    zeddy

  5. #20
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,460
    Thanks
    168
    Thanked 656 Times in 624 Posts
    Hi RG

    ..and I meant to say nice to see you using
    If WorksheetFunction.IsNumber(Target) Then

    ..rather than VBA's
    If IsNumeric(Target)

    Have you ever seen this example:
    Code:
    Sub test()
    
    zVar = "(123,,,3.4,5,,45E67)"
    
    If IsNumeric(zVar) Then
    MsgBox zVar & " is numeric"
    Else
    MsgBox "not numeric"
    End If
    
    End Sub
    zeddy

  6. #21
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    10,056
    Thanks
    426
    Thanked 1,612 Times in 1,456 Posts
    Zeddy,

    Yep I've seen it and it would be useful here!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #22
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    9,169
    Thanks
    65
    Thanked 1,132 Times in 1,055 Posts
    zeddy, I want some of what you're on!

    cheers, Paul

  8. #23
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    201
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi zeddy,

    Whoops! I missed the no macro bit!

    Chip's solution does it in the same cell, which I needed when I used his solution.

    Congrats on your solution - great!

    Peter

  9. #24
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    279
    Thanks
    0
    Thanked 20 Times in 20 Posts
    Dear All:
    Science says time is infinite, but in Excel there are only 1,440 minutes in a day. Based on that principal see the attached

    I am sure others can further simplify this even more, but I do know this one works.

    If you need to copy the results remember to the Copy Values rather than the formulas.

    Hope this helps

    DuthieT
    Attached Files Attached Files
    Last edited by duthiet; 2017-01-11 at 15:45.

  10. #25
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,460
    Thanks
    168
    Thanked 656 Times in 624 Posts
    duthiet

    Albert Einstein said
    "Only two things are infinite: the universe and human stupidity.
    And I'm not sure about the universe"

Page 2 of 2 FirstFirst 12

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •