Solved

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

Posted on 2013-02-04
17
276 Views
Last Modified: 2013-02-05
I have an Excel column where users must enter dates in the format "yyyy/mm/dd". The cells are formatted as text, not dates, as required by our system. My problem is that sometimes users will enter the numbers as "yyyy-mm-dd" which causes errors in our system because they use dashes instead of slashes.

How can I change their incorrect entry to the correct format automatically or at least have a message pop up telling them they have entered the wrong format? I would prefer to automatically change the dashes to slashes without them having to do anything.

Thanks in advance!
0
Comment
Question by:calbais
  • 7
  • 6
  • 4
17 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38852084
Use the REPLACE function.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38852102
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

Open in new window

0
 
LVL 26

Expert Comment

by:pony10us
ID: 38852156
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 

Open 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.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 26

Expert Comment

by:pony10us
ID: 38852262
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
0
 

Author Comment

by:calbais
ID: 38852372
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!
0
 
LVL 26

Expert Comment

by:pony10us
ID: 38852476
Here is a word document with step by step instructions for using validation in Excel 2007
excel-validation-instructions.doc
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38852582
Sheet1 code
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.
0
 
LVL 26

Expert Comment

by:pony10us
ID: 38852613
@MartinLiss:

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

Expert Comment

by:Martin Liss
ID: 38852705
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.
0
 
LVL 26

Expert Comment

by:pony10us
ID: 38852720
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.
0
 

Author Comment

by:calbais
ID: 38853080
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
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38853135
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

Open in new window

0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 300 total points
ID: 38853179
This also works. The DoneOnce variable and use in the previous post was to avoid an infinite loop that occurred when inStr wasn't used as it is here.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
    If InStr(1, Target.Value, "-") Then
        Target.Value = Replace(Target.Value, "-", "/")
    End If
End If
End Sub

Open in new window

0
 

Author Comment

by:calbais
ID: 38853509
Thank you Martin. Now it works perfectly.

I appreciate your help very much!


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

Author Closing Comment

by:calbais
ID: 38853511
Quick response and solution does exactly what I was looking for!!
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38853520
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
 
LVL 26

Expert Comment

by:pony10us
ID: 38855623
@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.   :)
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question