Solved

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

Posted on 2013-02-04
17
268 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 45

Expert Comment

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

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 45

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

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 45

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 45

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 45

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

13 Experts available now in Live!

Get 1:1 Help Now