Cell Validation using Date Time Group Format

[stirlingmw]

Good Morning All

I am trying to put together a workbook for my ships (Royal Navy) to ease data collection whilst at sea. I am almost there, but i am having difficulty with one specific cell and its data validation.

General Information.Range("K6") should contain the Date Time Group (DTG) that the data was collected in a specific format. I have a macro which adds the current DTG,

Worksheets("General Information").Range("K6").Value = Format(Now - LocalOffsetFromGMT / 24, "ddhhmmZmmmyy")

but if a crew forgets to add this DTG they need to enter it manually. I need to he a piece of code that then checks that the entered data is in the correct format and then warns if not.

can you help?

regards

Steve

 

[onlyadrafter]

Hello,

your post is a bit confusing (to me anyway), you are only referencing cell K6, but mention if crew forget to add, they need to add it manually. But then you have code to enter it.
Just finding it hard to follow the logic.

 

[stirlingmw]

Morning Onlyadrafter

Apologies for the confusion. I have 2 ways that the crew can add the DTG, the first is by button and the macro above, the other is manually, cell K6 accepts both methods, but it is the manual side of things i am trying to address. If the crew adds the DTG manually and get the format wrong when they input it, I would like a msgbox to appear indicating the fault.

regards

Steve

 

[onlyadrafter]

Hello,

how about this, it is only checking for the cell length to be 12 characters in length, you can have as many checks as necessary.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$K$6" Then
        Application.EnableEvents = False
        If Len(Target.Value) <> 12 Then
            MY_CHOICE = MsgBox("Cell K6 not correct format, press yes for auto entry", vbYesNo, "K6 DATA")
                If MY_CHOICE = vbYes Then
                    Target.Value = Format(Now - LocalOffsetFromGMT / 24, "ddhhmmZmmmyy")
                Else
                    Target.Select
                End If
        End If
    End If
    Application.EnableEvents = True
End Sub

the code needs to go in the General Information code window, not a standard module.

The code gives the option of entering the code automatically (by using your code).