Help with WorkSheet_Change

[Andrew B]

Hi All,

I have an Excel worksheet where one of the columns should only contain entries that begin with a number, for instance, a cell with a proper entry might read: 1 BOOK (or 2 CASES or 3 BOXES for that matter). I would like it to be the case that with any entry that does not begin with a number, the cell would be cleared and remain selected for the user to input the data in the way I prescribed above, that is with a number preceding the item type.
I do not yet know how to code from scratch, but managed to script something piecemeal (I call it Frankencode) that almost does the trick, but will not handle all cases:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
 Set rng = Target.Parent.Range("E201:E301")
            If Target.Count > 1 Then Exit Sub
            If Target.Column <> 5 Then Exit Sub
            
           Select Case Target.Text
                                                       
           Case "A" To "Z"
                               
                    
 Msg = Space(24) & "ERROR: YOU HAVE JUST MADE AN INVALID ENTRY." & Chr(13) & Chr(13) & Space(24) & "A NUMBER MUST PRECEDE THE ITEM TYPE." & Chr(13) & Chr(13) & Space(24) & "FOR EXAMPLE: 1 BOX, 2 ENVELOPES, 3 BAGS, ETC." & Chr(13) & Chr(13) & Space(24) & "PLEASE TRY AGAIN." & Chr(13) & Chr(13) & Space(24) & "THANK YOU!"
          
 RETVAL = MsgBox(Msg, vbExclamation)

                   
         Target.Select
            
                  Target.ClearContents
                
         End Select
           
         If Target.Value > 1 Then Target.Value = LTrim(Target.Value)
        
                
    End Sub

What this does is check to see if the cell begins with a letter value rather than a numeric value, produces a warning message if the former is the case and clears the cell upon ok so that the user can input data correctly. It can handle cases (thanks to LTrim) where the entry begins with a space rather than a number or letter, but it cannot handle cases where the user begins the entry with some other symbol like an asterisk, a dollar sign, or even a period!

All of this leads me to believe that my method of solving the problem is misguided for two reasons 1) the script above is probably ill suited to the task and 2) All I really need to prevent is the failure of the user to enter singular noun entries like a box as “BOX” as opposed to “1 BOX” which, for reasons not worth going into here, is what I require. Incidentally, all of the singular cases (and the plural ones as well) the user using this sheet would ever encounter are listed in cells hidden in rows (200 rows to be exact) above in the same column that the user would be entering this type of data. I suspect that the answer to my problem might be to 1) match the user input with the hidden cells above in the same column and 2) if no match is found to clear the cell, perhaps after a message box warning, and leave the cell selected for the user to make a proper entry. The problem is that I have no idea how to do this. Any ideas?

Sincerely,
Andrew

PS: My worksheet and workbook are protected.

 

[tweedle]

You're on the right track...

This would be my approach in testing the entries:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim c As Range
For Each c In Target
    If c.Column = 5 Then
        If patternMatch(c.Text) Then
            'Passed
        Else
            'Failed
            Msg = Space(24) & "ERROR: YOU HAVE JUST MADE AN INVALID ENTRY." & Chr(13) & Chr(13) & Space(24) & "A NUMBER MUST PRECEDE THE ITEM TYPE." & Chr(13) & Chr(13) & Space(24) & "FOR EXAMPLE: 1 BOX, 2 ENVELOPES, 3 BAGS, ETC." & Chr(13) & Chr(13) & Space(24) & "PLEASE TRY AGAIN." & Chr(13) & Chr(13) & Space(24) & "THANK YOU!"

            RETVAL = MsgBox(Msg, vbExclamation)

            Application.EnableEvents = False
            c.ClearContents
            Application.EnableEvents = True

            c.Select

        End If
    End If
Next c


End Sub

Public Function patternMatch(parm$)


    patternMatch = False


    If Not (parm$ Like "[0-9]*") Then
        patternMatch = False
    Else
        patternMatch = True
    End If


End Function

 

[mumps]

Try this code in the worksheet module. It assumes that entries are made in column A. Change 'Range("A:A") to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo exitsub
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
        If Not IsNumeric(Left(Target, 1)) Or Target = "" Or Left(Target, 1) = " " Then
            MsgBox ("Please enter data that starts with a number (do not use leading spaces).")
            Target.ClearContents
            Target.Select
            Application.EnableEvents = True
            Exit Sub
        End If
Application.EnableEvents = True
exitsub:
Application.EnableEvents = True
Exit Sub
End Sub

 

[Andrew B]

Thank you very much, Tweedle, for your speedy reply and your expert help. It works great! I had to add this before the first If statement

If c.Column <> 5 Then Exit Sub

in order for it to work properly. Does that look right to you?

Thanks Again!

Andrew

 

[Andrew B]

Mumps, thank you also for your help. Your script was really cool, too, and it worked great on it's own; unfortunately, it didn't play nice with some of the other macros in my workbook.

Sincerely,
Andrew