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 this approach. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the column number (in red) to match the column in which the date will be entered. Close the code window to return to your sheet. Click on a cell in your date column. Enter a date in the pop-up an click the 'OK' button.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Dim strDate As String
    strDate = InputBox("Insert date in format dd-mm-yyyy", "User date", Format(Now(), "dd-mm-yyyy"))
    If IsDate(strDate) Then
      Target = Format(CDate(strDate), "dd-mm-yyyy")
    Else
      MsgBox "Wrong date format"
    End If
End Sub

 

[dmt32]

Hi,
if your clients are directly entering a date in any recognised format to your worksheet then I would think all should need to do is to apply the required numberformat to the appropriate cell(s)

Place following in your worksheets code page

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim keycells As Range
   
    Set keycells = Me.Range("D5")
   
    On Error GoTo myerror
    If Not Intersect(Target, keycells) Is Nothing Then
        Application.EnableEvents = False
        With Target
            If .Value Like "##.##.####" Then .Value = DateValue(Replace(.Value, ".", "-"))
            .NumberFormat = "dd-mm-yyyy"
        End With
    End If
myerror:
Application.EnableEvents = True
End Sub

Change the key cell(s) shown in BOLD as required

As the period (.) or full stop is as far as I am aware, not a valid date separator, I have included a conversion based on format you state clients are entering such dates.
Other than this, you should be able to enter any valid date in the key cells ( 12 Feb 2022 for instance ), and cell should the display your required date format dd-mm-yyyy

Dave

 

[DharmeshChauhan]

Try this approach. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the column number (in red) to match the column in which the date will be entered. Close the code window to return to your sheet. Click on a cell in your date column. Enter a date in the pop-up an click the 'OK' button.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Dim strDate As String
    strDate = InputBox("Insert date in format dd-mm-yyyy", "User date", Format(Now(), "dd-mm-yyyy"))
    If IsDate(strDate) Then
      Target = Format(CDate(strDate), "dd-mm-yyyy")
    Else
      MsgBox "Wrong date format"
    End If
End Sub

Hi, wicked thanks for this it worked as required, can’t thank you enough

Would it be possible to create the same for mobile numbers that are entered on the same spreadsheet

This information to be in this format +44xxxxxxxxxx, is there a way of automatically converting this once the client has entered the 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 mobile number in this way

I guessing it will be similar VB coding (sorry my VB coding isn’t my strong point) and how to enter this after the Date coding

Thanks In Advance

Dharm

 

[DharmeshChauhan]

Try this approach. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the column number (in red) to match the column in which the date will be entered. Close the code window to return to your sheet. Click on a cell in your date column. Enter a date in the pop-up an click the 'OK' button.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Dim strDate As String
    strDate = InputBox("Insert date in format dd-mm-yyyy", "User date", Format(Now(), "dd-mm-yyyy"))
    If IsDate(strDate) Then
      Target = Format(CDate(strDate), "dd-mm-yyyy")
    Else
      MsgBox "Wrong date format"
    End If
End Sub

Hi, as mentioned in the above reply this worked well, but just had a thought is it possible to do something with the coding whereby the client cant move to next cell unless they have entered a date of birth in the correct format

Sorry for not putting this all in one post, just started a new job and they wanted my ideas on the spreadsheet

**I have added the below on a previous reply, but just incase it was missed I thought it was best to add again

Would it be possible to create the same for mobile numbers that are entered on the same spreadsheet

This information to be in this format +44xxxxxxxxxx, is there a way of automatically converting this once the client has entered it and then moves 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 mobile number in this way

I guessing it will be similar VB coding (sorry VB coding isn’t my strong point) and how to enter this after the Date coding

Thanks In Advance

 

[mumps]

Is the birth date entered in column A? In which column is the mobile number entered? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Pop Up Window/Message
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[DharmeshChauhan]

Is the birth date entered in column A? In which column is the mobile number entered? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Hi

As mention by the Moderator I have posted this in another forum (sorry I dint know the rules about cross posting please forgive me, I'm new to all this)

Would you be able to use the link below where I have posted as that post I was able to attached the spreadsheet, with an example of the required data in the first row

Pop Up Window/Message

 

[mumps]

Format the cells in col D to "dd-mm-yyyy" and then try these macros in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 12 Then Exit Sub
    Application.EnableEvents = False
    If Not Target Like "##########" Then
        Target.ClearContents
        Target.Select
        MsgBox ("Please enter the contact number using the format: '##########'")
    End If
    Application.EnableEvents = True
End Sub

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
    strDate = InputBox("Please enter date of birth below, in date format dd-mm-yyyy", "Date Of Birth", Format("dd-mm-yyyy"))
    If IsDate(strDate) Then
      Target = Format(CDate(strDate), "dd-mm-yyyy")
      Target.Offset(, 1).Select
    Else
      MsgBox "Wrong date format"
      Target.ClearContents
      Target.Offset(, 1).Select
    End If
End Sub

 

[DharmeshChauhan]

Format the cells in col D to "dd-mm-yyyy" and then try these macros in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 12 Then Exit Sub
    Application.EnableEvents = False
    If Not Target Like "##########" Then
        Target.ClearContents
        Target.Select
        MsgBox ("Please enter the contact number using the format: '##########'")
    End If
    Application.EnableEvents = True
End Sub

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
    strDate = InputBox("Please enter date of birth below, in date format dd-mm-yyyy", "Date Of Birth", Format("dd-mm-yyyy"))
    If IsDate(strDate) Then
      Target = Format(CDate(strDate), "dd-mm-yyyy")
      Target.Offset(, 1).Select
    Else
      MsgBox "Wrong date format"
      Target.ClearContents
      Target.Offset(, 1).Select
    End If
End Sub

Hi

Thanks, the contact number works as required now

Regarding the Date Of Birth coding is there a way off adding code so that the client HAS TO enter a date, if not then they cant move on to the next cell?
Currently when you move on to the cell a window pops up, you can press "cancel" then a little window pops up which displays "Wrong Date Format" you then click "Ok" and it will let you move on to the next cell, but can it be coded so that when you click OK it takes you back to Inputbox to enter a Date Of Birth and you cant move until you have entered a date in

The Date Of Birth is one of the mandatory field that the client must enter data in

@mumps thanks for spending your time on this and helping me out