Data entry asistance needed

fireman0174 · #1 09-03-2014, 04:00 PM

I’m trying to input some data into an Excel 2010/2011 sheet. One data field (cell) is beyond my ability and so I’m hopeful that someone can assist me.

Here’s an example of the data that will get entered as text:

BC10201412345

The BC102014 is a “constant”. What I want to do, is enter, as in this example, “12345” (no quotes to be inputted).

So I want to type 12345 and have it entered into the cell as BC10201412345. Hope that makes sense.

I think Visual Basic is the answer, but I don’t know to do it. Or perhaps there’s a different way.

I’m all ears!

In advance, thanks!!

gmayor · #2 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 · #3 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 · #4 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 · #5 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 · #6 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

gmayor · #7 09-06-2014, 06:11 AM

The following should work

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oRng As Range
    Set oRng = Range("A:A")
    If Target.Cells.Count > 1 Or 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
        Else
            MsgBox "Not a valid number"
            Target.Select
            Target = ""
        End If
    End If
End Sub

gebobs · #8 09-06-2014, 08:23 AM

Catalin's solution is the most parsimonious IMHO.

Catalin.B · #9 09-06-2014, 09:48 AM

I think it does not matter how "parsimonious" is the solution, both solutions have pro's and con's depending on fireman's specific needs. If he needs to process that codes with formulas or codes, he has to be aware that those cells does not contain the entire code, the formulas must add that prefix to the numbers from codes column, this may complicate further formulas.
This problem is solved in mr. Graham Mayor's version, it's a WYSIWYG situation.

But i do believe that VBA must be used only when something cannot be done using built-in tools. And there are lots of tools in excel waiting to be used

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