Solved

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

Posted on 2013-02-04
17
274 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
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.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

770 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