If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
data gets rounded up !!!
No Gord, we manually enter the credit card number. Dr Alok Modi MD |
#22
|
|||
|
|||
data gets rounded up !!!
And you want to simply enter as 1234123412341234 and have formatted as
1234 1234 1234 1234 or some other format? Gord On Sun, 14 Dec 2008 17:39:05 -0800, Dr Alok Modi MD wrote: No Gord, we manually enter the credit card number. Dr Alok Modi MD |
#23
|
|||
|
|||
data gets rounded up !!!
On Sat, 13 Dec 2008 18:14:01 -0800, Dr Alok Modi MD
wrote: 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 |
Thread Tools | |
Display Modes | |
|
|