String Lengths

[phillypdh]

i just started vba programming yesterday

i'm having a problem filtering a number that is between 6-12 characters in excel 2007. can anyone assist?

 

[phillypdh]

sorry forgot the code:

If IsEmpty(ActiveCell) Then
StringLength = Len(ActiveCell)
If (StringLength < 6) Then
MsgBox ("You entered a invalid Loan Number")
Application.Undo
GoTo Reset
End If
If (StringLength >= 13) Then
MsgBox ("You entered a invalid Loan Number")
Application.Undo
GoTo Reset
End If
End If

 

[taigovinda]

Welcome to the board!

Can you elaborate on what you are trying to do?

 

[phillypdh]

i'm trying to restrict users fron entering a loan number that is less than 6 digits or more than 12 digits because it would be an invalid loan number

 

[Jonmo1]

Welcome to the board...

Try Data - Validation
Custom
=AND(LEN(A1)>=6,LEN(A1)<=12)

A1 is the cell you're entering the validation on.

Hope that helps.

 

[phillypdh]

i'm a novice i don't know how to convert to lines of code

 

[phillypdh]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msgEmptyFields As String, Cstm1 As String, sEditedRange As String
Dim NumOfFields As Byte, NumEmptyFields As Byte
Dim EName As String, FName As String
Dim DataRange As Range, cell As Range
Dim TClmn As Long, lTranGUIDCount As Long
Dim StringLength As String
Const msgNoEdit As String = "You may not edit this field."
Const msgUndo As String = "Last action undone"
Const sconDprtmnt As String = "FinalMod"
Const NumHeaderRows As Byte = 1

If IsEmpty(ActiveCell) Then
StringLength = Len(ActiveCell)
If (StringLength < 6) Then
MsgBox ("You entered a invalid Loan Number")
Application.Undo
GoTo Reset
End If
If (StringLength >= 13) Then
MsgBox ("You entered a invalid Loan Number")
Application.Undo
GoTo Reset
End If
End If

 

[taigovinda]

I would suggest doing this with data validation instead of doing it with code. I'm not sure the menus for Excel 2007, but you can get there by selecting a cell or cells where you want to apply data validation and press alt+D, then press L

From there you can set the condition for data entry into that cell... Use a 'custom' condition and formula like this (presuming validation applied to cell A1):

=AND(LEN(A1)>5,LEN(A1)<13)

Now Excel won't let the user type in something unless it's between 6 and 12 characters. Next, you can use the other tabs in data validation (input message, error alert) to set what messages the user sees. Maybe you would just use the error alert tab and put title = "invalid loan number" and error message = "please try again"

...You could do this with code but in my view it would be less reliable and more difficult to implement. You would then also have to make sure that all of the users always enabled macros, which in itself is more complicated than the solution above. A simple, far from foolproof bit of code to do it though, might look like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
If Len(Target.Value) < 6 Or Len(Target.Value) > 12 Then
    MsgBox "You entered an invalid loan number - please try again.", vbCritical, "Error"
    Application.EnableEvents = False 'turn off events, prevent this sub from triggering itself in an endless loop
    Target.ClearContents
    Application.EnableEvents = True 'turn events on - never leave this off
    Target.Select
End If
End Sub

...you would apply that code by right-clicking the tab name, 'view code' and paste in the code. Note that it will fire if you change a cell in column A. You could modify that range to be whatever you would like, including a named range that covers the area where loan numbers are to be entered.

Hope that helps.

Tai

 

[Jonmo1]

The problem is this line
If IsEmpty(ActiveCell) Then

As soon as you enter any value, it's no longer empty.
So the rest of the code is ignored.

Are you trying to have it allow blanks, but any value actually entered then apply the rules?

Try

If Not IsEmpty(ActiveCell) Then
    StringLength = Len(ActiveCell)
    If (StringLength < 6) Then
        MsgBox ("You entered a invalid Loan Number")
        Application.Undo
        GoTo Reset
    End If
    If (StringLength >= 13) Then
        MsgBox ("You entered a invalid Loan Number")
        Application.Undo
        GoTo Reset
    End If
End If

 

[Scott Huish]

The Len check alone won't insure a 6 - 12 digit integer value.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
    For Each c In Target
        If IsNumeric(c) And c <> "" Then
            If c < Int(c) Or c < 999999 Or c > 999999999999# Then InvalidEntry (c.Address)
        Else
            InvalidEntry (c.Address)
        End If
    Next
Application.EnableEvents = True
End Sub
 
Sub InvalidEntry(r)
Dim r1 As Range
Set r1 = Range(r)
MsgBox "Invalid Entry in Cell " & r1.Address
r1.ClearContents
r1.Select
End Sub