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

[Maury1704]

[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]

 

[Dryver14]

give this a go on. Insert code in relevant worksheet.

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

 

[Maury1704]

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

 

[Maury1704]

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

 

[Dryver14]

Your welcome

 

[Maury1704]

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