Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last 4 digits of SSN
Is there a way to have the user input their whole social security number and
have Excel only display the last 4 digits of their number? Can I do this as a cell format or will this have to be done with a script. I tried the data validation with text length equal to 4, but if the last 4 digits start with a zero (0123), it gives an error because it doesn't recognize the zero as a digit. I'm guessing there has to be a script attached to that cell that will grab the last 4 digits. Any ideas would be great. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last 4 digits of SSN
Lets use A1 as the cell with the SSN, and B1 for the last four digits. I am
assuming that you are not doing any type of calculation with this, but just need the last four. Format the SSN cell(s) (A1) as SPECIAL SSN. In B1, type =RIGHT(A1,4) I did not format B1. It defaults to GENERAL and I tried it with a zero as the first digit of the last four numbers and it worked. Hope this works, Les "Matt" wrote: Is there a way to have the user input their whole social security number and have Excel only display the last 4 digits of their number? Can I do this as a cell format or will this have to be done with a script. I tried the data validation with text length equal to 4, but if the last 4 digits start with a zero (0123), it gives an error because it doesn't recognize the zero as a digit. I'm guessing there has to be a script attached to that cell that will grab the last 4 digits. Any ideas would be great. Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last 4 digits of SSN
Thanks for your suggestion. I'm sorry, but I did not explain the situation
properly. I don't want to grab the last 4 digits from another cell, but have the user put their social security number into cell A1 and when they press Enter, it only displays the last 4 digits that they entered. I hope this is a better explanation. Thanks. "WLMPilot" wrote: Lets use A1 as the cell with the SSN, and B1 for the last four digits. I am assuming that you are not doing any type of calculation with this, but just need the last four. Format the SSN cell(s) (A1) as SPECIAL SSN. In B1, type =RIGHT(A1,4) I did not format B1. It defaults to GENERAL and I tried it with a zero as the first digit of the last four numbers and it worked. Hope this works, Les "Matt" wrote: Is there a way to have the user input their whole social security number and have Excel only display the last 4 digits of their number? Can I do this as a cell format or will this have to be done with a script. I tried the data validation with text length equal to 4, but if the last 4 digits start with a zero (0123), it gives an error because it doesn't recognize the zero as a digit. I'm guessing there has to be a script attached to that cell that will grab the last 4 digits. Any ideas would be great. Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last 4 digits of SSN
If you want to keep the whole SSN number, but only display the last 4 digits for
security purposes, then don't do it. Excel's security isn't made to protect things like this. But you could have a macro that actually only keeps those final 4 digits--but why bother--just have the user type in the last 4 digits. Matt wrote: Is there a way to have the user input their whole social security number and have Excel only display the last 4 digits of their number? Can I do this as a cell format or will this have to be done with a script. I tried the data validation with text length equal to 4, but if the last 4 digits start with a zero (0123), it gives an error because it doesn't recognize the zero as a digit. I'm guessing there has to be a script attached to that cell that will grab the last 4 digits. Any ideas would be great. Thanks in advance. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last 4 digits of SSN
don't know exactly what you want to do, only have part of the entry visible?
maybe store the ssn in a hidden worksheet and use the formula to show the last 4 in A1. don't know what you're going to do with the data after it's entered, though. -- Gary "Matt" wrote in message ... Thanks for your suggestion. I'm sorry, but I did not explain the situation properly. I don't want to grab the last 4 digits from another cell, but have the user put their social security number into cell A1 and when they press Enter, it only displays the last 4 digits that they entered. I hope this is a better explanation. Thanks. "WLMPilot" wrote: Lets use A1 as the cell with the SSN, and B1 for the last four digits. I am assuming that you are not doing any type of calculation with this, but just need the last four. Format the SSN cell(s) (A1) as SPECIAL SSN. In B1, type =RIGHT(A1,4) I did not format B1. It defaults to GENERAL and I tried it with a zero as the first digit of the last four numbers and it worked. Hope this works, Les "Matt" wrote: Is there a way to have the user input their whole social security number and have Excel only display the last 4 digits of their number? Can I do this as a cell format or will this have to be done with a script. I tried the data validation with text length equal to 4, but if the last 4 digits start with a zero (0123), it gives an error because it doesn't recognize the zero as a digit. I'm guessing there has to be a script attached to that cell that will grab the last 4 digits. Any ideas would be great. Thanks in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last 4 digits of SSN
Thanks Dave. I don't want to keep the whole number but display the last 4.
I just want to keep the last 4 digits. Could you maybe give me an example of that macro, because as you stated, just have them enter the last 4 digits, management doesn't trust the people to only input the last 4 digits. They want me to make absolutely sure that the last 4 digits will be displayed no matter what the user puts in the cell. I completely agree with your statement, but unfortunately don't have the final say. Thanks again. "Dave Peterson" wrote: If you want to keep the whole SSN number, but only display the last 4 digits for security purposes, then don't do it. Excel's security isn't made to protect things like this. But you could have a macro that actually only keeps those final 4 digits--but why bother--just have the user type in the last 4 digits. Matt wrote: Is there a way to have the user input their whole social security number and have Excel only display the last 4 digits of their number? Can I do this as a cell format or will this have to be done with a script. I tried the data validation with text length equal to 4, but if the last 4 digits start with a zero (0123), it gives an error because it doesn't recognize the zero as a digit. I'm guessing there has to be a script attached to that cell that will grab the last 4 digits. Any ideas would be great. Thanks in advance. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last 4 digits of SSN
This works for me Phillip UK London Put this code in the sheet module Right click sheet tab and select view code Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Thiscell As Range Static Lastcell As Range If Target.Cells.Count 1 Then Exit Sub Set Thiscell = Target Set Lastcell = Thiscell If Lastcell Is Nothing Then Exit Sub Lastcell.Value = VBA.Right(Lastcell.Value, 4) End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last 4 digits of SSN
Matt,
You can use then the _Change event. Put this code in the sheet module where the user will input the data: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Application.EnableEvents = False Target.Value = Right$(Target.Value, 4) Application.EnableEvents = True End If End Sub and change the $A$1 reference to the cell that they will enter the data in. Regards, Juan Pablo González "Matt" wrote in message ... Thanks Dave. I don't want to keep the whole number but display the last 4. I just want to keep the last 4 digits. Could you maybe give me an example of that macro, because as you stated, just have them enter the last 4 digits, management doesn't trust the people to only input the last 4 digits. They want me to make absolutely sure that the last 4 digits will be displayed no matter what the user puts in the cell. I completely agree with your statement, but unfortunately don't have the final say. Thanks again. "Dave Peterson" wrote: If you want to keep the whole SSN number, but only display the last 4 digits for security purposes, then don't do it. Excel's security isn't made to protect things like this. But you could have a macro that actually only keeps those final 4 digits--but why bother--just have the user type in the last 4 digits. Matt wrote: Is there a way to have the user input their whole social security number and have Excel only display the last 4 digits of their number? Can I do this as a cell format or will this have to be done with a script. I tried the data validation with text length equal to 4, but if the last 4 digits start with a zero (0123), it gives an error because it doesn't recognize the zero as a digit. I'm guessing there has to be a script attached to that cell that will grab the last 4 digits. Any ideas would be great. Thanks in advance. -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last 4 digits of SSN
Phillip, thanks. That looks like exactly what I need. One question though?
Is it possible to make it only apply to a change in one particular cell (A1) instead of any cell on the spreadsheet? Thanks alot. "Phillip" wrote: This works for me Phillip UK London Put this code in the sheet module Right click sheet tab and select view code Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Thiscell As Range Static Lastcell As Range If Target.Cells.Count 1 Then Exit Sub Set Thiscell = Target Set Lastcell = Thiscell If Lastcell Is Nothing Then Exit Sub Lastcell.Value = VBA.Right(Lastcell.Value, 4) End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last 4 digits of SSN
Is it possible to make it only apply to a change in one particular cell (A1)
instead of any cell on the spreadsheet? Thanks alot. If Target.Address = "$A$1" Then 'do something End If Hth, Merjet |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last 4 digits of SSN
Look at Juan Pablo's suggestion.
Matt wrote: Phillip, thanks. That looks like exactly what I need. One question though? Is it possible to make it only apply to a change in one particular cell (A1) instead of any cell on the spreadsheet? Thanks alot. "Phillip" wrote: This works for me Phillip UK London Put this code in the sheet module Right click sheet tab and select view code Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Thiscell As Range Static Lastcell As Range If Target.Cells.Count 1 Then Exit Sub Set Thiscell = Target Set Lastcell = Thiscell If Lastcell Is Nothing Then Exit Sub Lastcell.Value = VBA.Right(Lastcell.Value, 4) End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you change single digits to recognized double digits? | Excel Worksheet Functions | |||
VBA write macro change column with 3 number digits to 4 digits the | Excel Discussion (Misc queries) | |||
number 12 digits to 15 digits | Excel Discussion (Misc queries) | |||
How to customize number to 10 digits including 2 digits after deci | Excel Worksheet Functions | |||
15 digits??? | Excel Discussion (Misc queries) |