Solved: Input box - force certain no of characters?

[blackie42]

Is it possible to force a user to only 9 characters in an input box?

I have a delete sub that asks a user for a string to delete - most of which are numeric (but always 9 characters). Problem is if they only put in 4 characters and this matches some of the actual values it deletes them instead.

Sub Del_rows()
Dim Wrkst As Worksheet
Dim lLoop As Long
Dim rStart As Range
Dim vsearch As String

Application.ScreenUpdating = False

vsearch = InputBox("PLAN TO DELETE", "PLAN NUMBER ?", vbOKCancel)

If vsearch = "" Then Exit Sub

On Error Resume Next
For Each Wrkst In Worksheets
With Wrkst.UsedRange
Set rStart = .Cells(1, 1)
For lLoop = 1 To WorksheetFunction.CountIf(.Cells, vsearch)
Set rStart = .Find(What:=vsearch, After:=rStart, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows)
rStart.EntireRow.Delete
Next lLoop
End With
Next Wrkst
End Sub

thanks

[Bob Phillips]

Do
vsearch = InputBox("PLAN TO DELETE", "PLAN NUMBER ?")
If vsearch <> "" And Len(vsearch) <> 9 Then
MsgBox "Invalid"
End If
Loop Until vsearch = "" Or Len(vsearch) = 9
If vsearch <> "" Then
'rest of code
End If

[p45cal]

vsearch=""
Do
vsearch = InputBox("PLAN TO DELETE", "PLAN NUMBER ?", vsearch)
Loop Until Len(vsearch) = 9 Or vsearch = ""
If vsearch = "" Then Exit SubNote the replacement of vbokcancel by vsearch. You had vbokcancel as the default entry which caused a 1 to be in the input field. Now a previous try doesn't have to be reentered if, for example, the user had input all but one of the characters. Just a bit more user-friendly.

[blackie42]

Thanks a lot - I was tinkering witha user form but I'll give this a go

regards

Jon

[mikerickson]

Set rStart = .Find(What:=vsearch, After:=rStart, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows) will prevent the deletion of partial matches. But checking things at the user interface point is a good idea.

[blackie42]

Yep - good point - will change to whole

thanks again