How to change a users entry to comply to format in Excel cell

Use the REPLACE function.

For column A

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
    Target.Value = Replace(Target.Value, "-", "/")
End If
End Sub

Select allOpen in new window

To prevent certain characters you can add this code:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) 
    If InStr("/\? * [ ]-", Chr(KeyAscii)) Then KeyAscii = 0 
End Sub 

Select allOpen in new window

This should pop up a message box informing them that the characters listed in InStr are not allowed. However this will prevent them from being used anywhere in the spreadsheet I believe.

You can also use data validation.

In Excel 2007:

1. Click the Data tab
2. Select the first cell for the column
3. Clcik the Data Validation button
4. Select Custom under the Allow
5. Put this formula in:    =ISERROR(FIND("-",A1))    changing the cell to the one you need
6. You can customize a pop up message text you want when cell is selected
      I did:   Proper format for this cell is:   2013/01/04 (No Dashs)
7. You can customize a pop up message text you want when character "-" is entered
      I did:   Didn't you read the instructions? No dashs allowed.
8. Right click the cell "A1" and celect copy
9. Highlight the cells you want the formula in (I selected the whole row)
10. Right click and select Paste Special
11. Click the box next to validation

Martin. I like your solution but I can't get it to work. I put it in the worksheet by right clicking the sheet1 tab at the bottom and choosing "View Code". It shows "Worksheet" and "SelectionChange" at the top of the code page. I closed that and entered a date with dashes in column 1 of sheet1 but nothing seems to happen.

What am I doing wrong?

Thanks

Pony10us. I tried your data validation procedure but I can't get it to work either!

Here is a word document with step by step instructions for using validation in Excel 2007
excel-validation-instructions.doc

This should work for any value in Column A of the sheet where the code resides. Enter the value in Call A1 for example and click on any other cell.

@MartinLiss:

I like what you have done but I just want to be clear.  Will this automatically change the "-" to "/" as it is input?

It's not real-time in that when the user types a "-" it won't change it immediately to a "/" but rather after the entry is complete and focus shifts to another cell. If we were talking about values in an ActiveX textbox we could do it real-time but otherwise change on exit is the best you can do.

That is what the validation code does that I gave except that it on exit it forces the person entering the data to do it correctly instead of doing it for them is the major difference then.  Thank you, I learned something too.

Hi Martin,
I tried this in a new sheet. I formatted the cells in column A as text (as required by our system) and put your code in the Worksheet code area.
I entered a date using the dashes and clicked another cell. Nothing happened. I tried again and tabbed to another cell but nothing happened.
I then clicked back into the original cell (where I had entered the dashes) and immediately the dashes changed to slashes!!
What would cause that behaviour and how can I get it to work as you describe so it will change when entering another cell.

Regards

Sorry, wrong event.

Private Sub Worksheet_Change(ByVal Target As Range)
Static DoneOnce As Boolean

If DoneOnce Then
    DoneOnce = False
    Exit Sub
End If
If Target.Column = 1 Then
    DoneOnce = True
    Target.Value = Replace(Target.Value, "-", "/")
End If
End Sub

Select allOpen in new window

Thank you Martin. Now it works perfectly.

I appreciate your help very much!


Pony10us - thanks for your attempt but Martin's is much simpler!

Quick response and solution does exactly what I was looking for!!

You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013

@calbais

No worries, I agree that Martin's is much simpler.  Glad it works for you.

My motto is to make the user responsible so using my method would force them to enter properly is the primary difference and that takes a little more work on the back end.   :)