Data entry asistance needed

gmayor · #1 09-03-2014, 10:37 PM

Right click the worksheet tab and select View Code

In the module that appears copy and paste the following code
Change the value of oRng (here ("A:A") to the column you wish to monitor.
Save the workbook as a macro enabled workbook.
Enter a number in the column

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oRng As Range
    Set oRng = Range("A:A")
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Intersect(Target, oRng) Is Nothing Then
        If IsNumeric(Target) Then
            On Error Resume Next
            Application.EnableEvents = False
            Target = "BC102014" & CStr(Target)
            Application.EnableEvents = True
            On Error GoTo 0
        End If
    End If
End Sub

Catalin.B · #2 09-04-2014, 08:58 AM

Hi Fireman,
You already have a great answer from mr. Graham Mayor, but i suggest an alternative solution using a very simple Custom Format:
Select column A for example, if this is your column with this code format, go to ribbon's Home tab, Numbers section, choose custom format, and place this custom format in Type section: "BC102014"#
Note that this custom format will only work with numbers, if you type text in that column, the prefix code will not be added. This means that you will be able to use the exact number you typed. If you type 11234 in a cell, after you press enter, the prefix will be added; when you select the cell, even if it will show BC10201411234, if you look in the formula bar, the cell contains only what you typed: 11234...
Cheers,
Catalin Bombea

fireman0174 · #3 09-06-2014, 05:48 AM

Quote:

Originally Posted by Catalin.B

Hi Fireman,
You already have a great answer from mr. Graham Mayor, but i suggest an alternative solution using a very simple Custom Format:
Select column A for example, if this is your column with this code format, go to ribbon's Home tab, Numbers section, choose custom format, and place this custom format in Type section: "BC102014"#
Note that this custom format will only work with numbers, if you type text in that column, the prefix code will not be added. This means that you will be able to use the exact number you typed. If you type 11234 in a cell, after you press enter, the prefix will be added; when you select the cell, even if it will show BC10201411234, if you look in the formula bar, the cell contains only what you typed: 11234...
Cheers,
Catalin Bombea

Only glitch is that any entry that starts with a zero (0), for example 00123, does not add the zeroes.

fireman0174 · #4 09-06-2014, 05:50 AM

Quote:

Originally Posted by gmayor

Right click the worksheet tab and select View Code

In the module that appears copy and paste the following code
Change the value of oRng (here ("A:A") to the column you wish to monitor.
Save the workbook as a macro enabled workbook.
Enter a number in the column

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oRng As Range
    Set oRng = Range("A:A")
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Intersect(Target, oRng) Is Nothing Then
        If IsNumeric(Target) Then
            On Error Resume Next
            Application.EnableEvents = False
            Target = "BC102014" & CStr(Target)
            Application.EnableEvents = True
            On Error GoTo 0
        End If
    End If
End Sub

Works well. Had to format the column as text so that leading zeroes (0) would added.

Is there any way the modify this so that ONLY numeric values can be inputted?

Thanks again!!

Catalin.B · #5 09-06-2014, 06:01 AM

In this case, you should provide all details in the first post.
Using a custom format like: "BC102014"0000 , if your last 2 digits are 23 for example, you just have to type 23, no leading zeros, this format will add 2 leading zeros to your number, and that cell will look like this: BC1020140023. If you type 555, it will show BC1020140555. Use as many zeros you need in that custom format to set the desired number of characters that follows after the prefix.
Use data validation for all cells in that range, this way you can have only numeric entries. In the file attached, the yellow cells has data validation to allow only numeric entries.
Cheers,
Catalin

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Automatic Entry	fjdesjardins	Word	1	08-18-2014 05:07 PM
ON ENTRY Command	la_gillespie25	Word VBA	1	05-15-2013 02:18 AM
How to create a Main category mirrored in Sub category data entry type of solution?	Geza59	Excel	0	10-19-2012 05:44 AM
example of radio buttons in for data entry?	derohanes	Excel	1	03-05-2011 09:37 AM
Printable Entry Form	eJames	Excel	1	01-07-2010 09:50 AM