Hi Svetlana,
There's no good way to do this, as Tom pointed out. But you could "hide"
the SSNs from most users by storing the SSNs in hidden names. The only way
a user could get to them is via VBA code. Here's an example:
Private mbDisableEvents As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim regExp As regExp
If Not mbDisableEvents Then
For Each rng In Target
If rng.Column = 2 Then
'/ store SSN and hide all except last 4 digits
'/ -- quick check of format first
Set regExp = New regExp
regExp.Pattern = "^\d{3}-\d{2}-\d{4}$"
If regExp.Test(rng.Value) Then
'/ check OK, store it
ThisWorkbook.Names.Add Name:="SSN_" & _
rng.Parent.Name & "_" & CStr(rng.Row) & _
"_" & CStr(rng.Column), _
RefersTo:="=" & rng.Value, Visible:=False
'/ reformat cell
mbDisableEvents = True
rng.Value = "xxx-xx-" & Right$(rng.Value, 4)
mbDisableEvents = False
End If
Set regExp = Nothing
End If
Next rng
End If
End Sub
This example assumes your SSNs are going to be entered into column B. If
not, you should change the If rng.Column=2 line to the appropriate column
number. This code would go "behind" the worksheet that the SSNs would be
entered into. To get there, just right-click the sheet tab and select View
Code. In order to check the format of the SSN before processing it, the
code uses the RegExp object, which requires a reference to the Microsoft
VBScript Regular Expressions x.x Library (pick the latest version available
to you via Tools | References in the VBE). If you don't care about checking
the format first, you can get rid of that code and the reference.
Now, whenever a user enters a validly-formatted SSN into the worksheet in
the appropriate column, it will be stored as a hidden name, and the cell
will display only the last 4 digits.
To get the SSN back, you need to use code:
Private Function msGetSSN(rrng As Range) As String
On Error Resume Next
If rrng.Cells.Count = 1 Then
msGetSSN = Mid$(ThisWorkbook.Names("SSN_" & _
rrng.Parent.Name & "_" & CStr(rrng.Row) & _
"_" & rrng.Column).RefersTo, 2)
End If
End Function
This code should go into a standard module, and you just pass in the range
for which you wish to get the SSN. If no SSN is associated with that range,
the function returns an empty string.
MsgBox msGetSSN(Sheets("Sheet1").Range("B2"))
I made the function Private so it can't be called externally, but you may
need to change it to Public depending on from where you're calling it. If
you want to use it as a worksheet function (UDF), you would have to make it
Public.
Make sure you lock down your VBA project with a password so nobody can view
the code (Tools | <projectname> Properties from VBE).
Like I said, this isn't foolproof, as a savvy user could get the hidden
names just like the msGetSSN function does. But at least it will protect
the data from casual users.
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
