Microsoft Office Forums - View Single Post

fireman0174 · 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!!