Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts

    Smile Formatting Time in VBA so user can enter am/pm in cell

    Hi Experts,

    When a user enters time in a cell using am/pm what is the best way to have the proper greeting appear based on the following code?

    If I use military it works properly. The problem comes when am/pm times are entered.

    I would like to use VBA formatting of some kind to make this approach work properly.


    Thanks in advance for your help.



    Sub casehello()


    Dim msg As String


    Select Case Range("A1").Value

    Case Is < 0.5
    msg = "Good Morning"
    Case Is >= 0.5 And Time < 0.75
    msg = "Good Afternoon"
    Case Is >= 0.75 And Time <= 0.9
    msg = "Good Evening"
    Case Is > 0.91667
    msg = "Good Night"

    End Select


    MsgBox msg


    End Sub
    Last edited by Excelnewbie; 2015-09-02 at 19:12.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    E.N.,

    Place the following in the Sheet1 Module:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
       Dim isect As Range
       Dim msg As String
    
       Set isect = Application.Intersect(Range("A1"), Target)
       If isect Is Nothing Then Exit Sub
    
       Select Case Range("A1").Value
     
            Case Is < 0.5
               msg = "Good Morning"
            Case Is < 0.75
               msg = "Good Afternoon"
            Case Is < 0.918
               msg = "Good Evening"
            Case Else
               msg = "Good Night"
       End Select
    
       MsgBox msg, vbOKOnly, "Greeting"
    
    End Sub
    Note: with Select Case once a condition is met the others are ignored. Thus, the need to only check for less than in this situation.

    Also if this doesn't work with AM/PM then check if you have applied any formatting to A1. I tested with a new blank workbook which has General formatting applies to all cells.

    HTH
    Last edited by RetiredGeek; 2015-09-02 at 19:27.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Excelnewbie (2015-09-03)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Excel,

    Another variation would be to format the cell as text then apply the following code:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo errorhandler
       If Target.Address = "$A$1" Then
          Select Case TimeValue(Target)
               Case Is < 0.5
                  MsgBox "Good Morning"
               Case Is < 0.75
                  MsgBox "Good Afternoon"
               Case Is < 0.91667
                  MsgBox "Good Evening"
               Case Else
                  MsgBox "Good Night"
          End Select
          Exit Sub
        Else: Exit Sub
        End If
    errorhandler:
    MsgBox "Please enter military or standard time in the correct format"
    End Sub
    This would accept military time (22:00), standard time (10:00 PM, 10:00PM, 10 PM, 10PM). It also offers error handling if an improper time format was entered. If you receive an unexpected error, make sure A1 is formatted as text.

    HTH,
    Maud
    Last edited by Maudibe; 2015-09-02 at 22:37.

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    Excelnewbie (2015-09-03)

  6. #4
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts
    RG-I set this up and it worked flawlessly . Thank you!

  7. #5
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts
    Maudibe-I set this up and it worked flawlessly. Thank you.

    I have a question. Why does Private Sub Worksheet_Change(ByVal Target As Range) event need to be in a sheet module? Does it ever go into a module?

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    E.N.,

    It's so you can have different processes for each worksheet. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Excel,

    Standard modules do not have events. It is an event specific to the Worksheet object. Each worksheet is an object and has the same events but the code, if any, can be different for each sheet which is what RG is referring. The workbook and userforms (and its controls) also have events but while there may be more than one userform, each with their set of events, their is only one workbook object.

    Hope that explains things further.
    Maud

  10. #8
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts
    RG and Maudibe,

    Thanks for your help and explanation. You are the best.

Posting Permissions

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