Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-02-04
17
Medium Priority
?
289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
17 Comments
 
LVL 49

Expert Comment

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

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 49

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 49

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 49

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 49

Accepted Solution

by:
Martin Liss earned 1200 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 49

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

597 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