Solved

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

Posted on 2013-02-04
17
271 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Microsoft Edge, Outlook OWA 7 43
Highlighting cells in Excel 9 17
Error in formula not increasing value 2 15
Vlookup formula error 15 11
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Outlook Free & Paid Tools
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now