Intercepting an hour is written correctly in a specific column in VBA

Maury1704

New Member
Joined
Jan 13, 2016
Messages
24
[COLOR=rgba(0, 0, 0, 0.87)]Hello everyone My name is A.MaurizioI am writing to you as I would have a problem that apparently seems easy, but in reality it is not.The problem is This: On an Excel Sheet In Column (B2: B20) I need to enter the hour in this way "hh: mm" but the problem is that not everyone working in my studio has understood it .For here I was wondering is the possibility to evaluate if the time is written exactly.And not as many people use to write this way (hh, mm) or (hh.mm) I have even worse (hh-mm).In addition, I would always like it to be possible; Make sure that I not only get a message like "Smgbox" that indicates the error, but that can delete the wrong time in that cell and put me in its place in the right format.I try to Spegarmi Better:1) Let us hypothesize that :: I in a cell of the Column (B) write the hour in this way (05:12) the program should let it run because it has been inserted in the Correct mode.2) However, on the contrary: If you type the time in these ways (05.12) or (05,12) I have even worse (05; 12) from the program should send an error message on the screen to enter the desired time.3) After that the program should be able to enter the time in the right format.All of this, thanks from now for all those who want to give me a hand on this.Greetings from A.Maurizio[/COLOR]
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
give this a go on. Insert code in relevant worksheet.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ThisTime As String
    Dim NewTime As String
    
    If Target.Column = 2 And Target.Row > 1 And Target.Row < 21 Then
        If Target.Cells.Count > 1 Then
        MsgBox "Please select only one cell"
        Exit Sub
    End If
            ThisTime = Target.Value
            Select Case Len(ThisTime)
                
                Case 3
                    NewTime = Left(ThisTime, 1) & ":" & Right(ThisTime, 2)
                Case 4, 5
                    NewTime = Left(ThisTime, 2) & ":" & Right(ThisTime, 2)
                    
                Case Else
                    NewTime = ThisTime
            End Select
            
            Application.EnableEvents = False
            Target.Value = NewTime
            Application.EnableEvents = True


     End If
End Sub
 
Last edited:
Upvote 0
Hello Dryver14 You are a great and perfect and what I was trying to do for some time without ever succeeding Thanks and Good Sunday and Weekends
 
Last edited by a moderator:
Upvote 0
Ciao Dryver14 Sei un grande e perfetto e quello che stavo cercando di fare per qualche tempo senza mai riuscire Grazie e buona domenica e fine settimana
 
Last edited by a moderator:
Upvote 0
Ciao Dryver14 Sei un grande e perfetto e quello che sto cercando di fare per qualche tempo senza mai pensare Grazie e buona domenica e fine settimanaHello Dryver14 You are a great and perfect and what I was trying to do for some time without ever succeeding Thanks and Good Sunday and Weekends
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top