Maybe I am wrong in what I have done, pl help me correct myself, if so. Like
you have posted, " Right click on the sheet tab. Select View Code and paste
the code below into the window that opens." I did that. I then pasted the
code in the 2nd post that you have written, " Oops, minor change in code
previously posted:"
I have a excel workbook which has 24 sheets. The purpose of this workbook is
to store the data of payments from patients who pay by credit card. Each
month carries data from the credit card machine of two banks, so there are
two sheets per month corresponding to two credit card machine each linked
with one bank. There is one column in each sheet which stores credit card
nos in text format. And like you can see in this thread that I had started 1
1/2 yrs back, Gord has mentioned how to enter tdata in text format. If you
permit,me, I would like to upload the workbook so you could tell me where I
am going wrong.
OK. As posted, the code will only work on the particular worksheet where you
have entered the data.
From your description, it now seems as if you have multiple worksheets which
need to behave the same way.
Probably you would be better off with a Workbook SheetChange event.
To enter that, after you right click on the sheet tab and select view code,
examine the Project Explorer window. You will find your particular "project"
named by workbook name. Highlight This Workbook within that project and
"double-click". A code window will open and you can paste the code below into
that window.
===========================================
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim c As Range
Dim rRng As Range
Dim sTemp As String
Set rRng = Range("a:a")
Application.EnableEvents = False
If Not Intersect(Target, rRng) Is Nothing Then
For Each c In Intersect(Target, rRng)
'remove <space> and <hyphen>
sTemp = Replace(c.Text, " ", "")
sTemp = Replace(sTemp, "-", "")
If Not Len(sTemp) = 16 Then
'output error message
'could have other checks here, too
c.Value = CVErr(xlErrValue)
Else
c.Value = Format(sTemp, "0000 0000 0000 0000")
End If
Next c
End If
Application.EnableEvents = True
End Sub
======================================
If the target column is not formatted as Text, you may get a VALUE error. You
may want to avoid having to do this manually by using a Worksheet SheetActivate
event:
===========================
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("A:A").NumberFormat = "@"
End Sub
===============================
--ron
