Help with WorkSheet_Change

Andrew B

New Member
Joined
Jan 21, 2011
Messages
27
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:

Code:
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.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
You're on the right track...

This would be my approach in testing the entries:

Code:
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



Code:
Public Function patternMatch(parm$)


    patternMatch = False


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


End Function
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,141
Try this code in the worksheet module. It assumes that entries are made in column A. Change 'Range("A:A") to suit your needs.

Code:
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
 
Last edited:

Andrew B

New Member
Joined
Jan 21, 2011
Messages
27
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

New Member
Joined
Jan 21, 2011
Messages
27
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top