Pop Up Window/Message

[DharmeshChauhan]

Hi

I currently have a spreadsheet whereby clients are entering date of births as 12.02.2022 or 12/02/2022

I require this information to be in this format 12-02-2022, is there a way of automatically converting this once the client has entered the DOB and moved on to the next cell, or can a warning/pop up message be displayed every time they click on the cell that would inform them to enter the DOB in a certain way

Thanks In Advance

Dharm

 

[mumps]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 4 Then Exit Sub
    Dim strDate As String
    Do
        strDate = InputBox("Please enter date of birth in date format: dd-mm-yyyy", "Date Of Birth", Format("dd-mm-yyyy"))
        If strDate <> "" Then
            If IsDate(strDate) Then
                Target = Format(CDate(strDate), "dd-mm-yyyy")
                Target.Offset(, 1).Select
                Exit Do
            Else
                MsgBox "Wrong date format"
                Target.ClearContents
            End If
        ElseIf strDate = "" Then
            MsgBox "You must enter a date.", vbOKOnly, ""
        End If
    Loop
End Sub

 

[DharmeshChauhan]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 4 Then Exit Sub
    Dim strDate As String
    Do
        strDate = InputBox("Please enter date of birth in date format: dd-mm-yyyy", "Date Of Birth", Format("dd-mm-yyyy"))
        If strDate <> "" Then
            If IsDate(strDate) Then
                Target = Format(CDate(strDate), "dd-mm-yyyy")
                Target.Offset(, 1).Select
                Exit Do
            Else
                MsgBox "Wrong date format"
                Target.ClearContents
            End If
        ElseIf strDate = "" Then
            MsgBox "You must enter a date.", vbOKOnly, ""
        End If
    Loop
End Sub

Hi

Thanks again

I noticed that if a correct format date is entered in the cell and you were tabbing (or using the arrowing keys) to move across the row it pops up with the messaged to entered the date, is there a way whereby if the correct date is in the correct format is entered that the pop wont come up, allowing the client to tab (with the tab button or arrow buttons) through each cell in that row

 

[mumps]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 4 Then Exit Sub
    Dim strDate As String
    If Target = "" Then
        Do
            strDate = InputBox("Please enter date of birth in date format: dd-mm-yyyy", "Date Of Birth", Format("dd-mm-yyyy"))
            If strDate <> "" Then
                If IsDate(strDate) Then
                    Target = Format(CDate(strDate), "dd-mm-yyyy")
                    Target.Offset(, 1).Select
                    Exit Do
                Else
                    MsgBox "Wrong date format"
                    Target.ClearContents
                End If
            ElseIf strDate = "" Then
                MsgBox "You must enter a date.", vbOKOnly, ""
            End If
        Loop
    End If
End Sub

 

[DharmeshChauhan]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 4 Then Exit Sub
    Dim strDate As String
    If Target = "" Then
        Do
            strDate = InputBox("Please enter date of birth in date format: dd-mm-yyyy", "Date Of Birth", Format("dd-mm-yyyy"))
            If strDate <> "" Then
                If IsDate(strDate) Then
                    Target = Format(CDate(strDate), "dd-mm-yyyy")
                    Target.Offset(, 1).Select
                    Exit Do
                Else
                    MsgBox "Wrong date format"
                    Target.ClearContents
                End If
            ElseIf strDate = "" Then
                MsgBox "You must enter a date.", vbOKOnly, ""
            End If
        Loop
    End If
End Sub

Hi really appreciate this, thanks again for your time on this

The blank spreadsheet is sent over to the clients to fill out, I have just been told that some times the client may copy the data cross from another spreadsheet instead of manually inputting all the data. is there a way of the above coding recognising this and still converting the data in the format I require?

Some (very few) client sometimes using Vlookup's to transfer the data on to my spreadsheet is there a way of the above coding recognising this and still converting the data in the format I require?

Thanks In Advance

 

[mumps]

Unfortunately, I don't think that copy/paste and using formulas will work because of how the macros are triggered.

 

[DharmeshChauhan]

Unfortunately, I don't think that copy/paste and using formulas will work because of how the macros are triggered.

oh ok, if they were to copy data (by paste values) from another spreadsheet would the macro be able to format in the way we require?

 

[mumps]

No, the copy/paste will not trigger the macros. They can copy paste the entire row or rows of data but they would have to enter the DOB and phone number manually.

 

[DharmeshChauhan]

No, the copy/paste will not trigger the macros. They can copy paste the entire row or rows of data but they would have to enter the DOB and phone number manually.

Hi

I thought this would be the case.

If a client was good with excel (very rare), then I think I would send him a blank copy without the macros so that they could copy/paste or use a Vlookup to get the data into my spreadsheet, and then they email me over the completed spreadsheet

Could I insert a new column next to each of the DOB/Phone number and use a formula to copy the data and format it in the way I require? and then paste value or delete their data? if so what would the formula would I need to do this for both DOB and phone number

 

[mumps]

Could I insert a new column next to each of the DOB/Phone number and use a formula to copy the data and format it in the way I require? and then paste value or delete their data?

I'm sorry but I don't understand what you mean. Please explain in detail using a few examples.

 

[DharmeshChauhan]

I'm sorry but I don't understand what you mean. Please explain in detail using a few examples.

If a client was sent a blank copy of the spreadsheet (without the macros) so that they could copy/paste or use a Vlookup to get their data into my spreadsheet

See picture1.png


Would I be able to insert a new column next to column D, then paste a formula into the new inserted column so that the formula would look at the text/data in the DOB “column D” and convert it into the format I needed, once its converted I would then delete column D

See picture2.png


I would do the same for the phone number column

I hope this explains it better

CECG Individual Exposure Form (QF 104) V2 - Dharmesh Macro.xlsm
	A	B	C	D	E	F
1	Colleague/Employee ID	Surname	Forename	DOB	Column1	Employee email address
2	1001	Bloggs	Joe	01.02.2022
3
4
Full list

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A4,F1:G4	Cell Value	duplicates	text	NO
A1:E1	Cell Value	duplicates	text	NO
A1:E1	Cell Value	="NoMatch"	text	NO