Hello all,
The code below works fine except for, if user wants to cancel before
all of the seleciton has been checked. With this code I do not have a
way for the user to back out or cancel out of the Input Box process.
Any assistance is greatly appreciated. Thank you, Ron


Sub ValidateDataN()

'validate values in a column
Range("e12").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, 9).Select
Set Rng = Selection
FirstRow = Rng.Row
LastRow = Rng.Rows(Rng.Rows.Count).Row
col = "n"
'loop thru col n of database, but skip first row with headings
For i = FirstRow To LastRow
FixColumnN
Next i

End Sub

Private Sub FixColumnN()
Dim x As Integer, OK As Boolean
OK = True
'check to ensure six digits entered
If Len(Range("a1")(i, col).Value) < 6 Then
OK = False
End If

If OK = False Then
'Enter a new value in Column B
Range("a1")(i, col).Select
Range("a1")(i, col) = InputBox("Enter a 6 digit value")

'Range("a1")(i, col).Value = vbNullString
123 FixColumnN
End If

Rng.NumberFormat = "@"

End Sub

Hi Ron

Try

Sub test()
Dim L As Long
L = Application.InputBox("Six digs pls:", Type:=1)
Select Case L
Case 100000 To 999999
Case Else
MsgBox "Not six digits"
Exit Sub
End Select
MsgBox "rest of actions here"
End Sub

If you have more than one inputs like this, put them onto a userform
instead, where the user can fill in everything at once, or have a
next-back-cancel choice. See
http://www.contextures.com/xlUserForm01.html
on userforms.

HTH. Best wishes Harald

"Ron" skrev i melding
...
Hello all,
The code below works fine except for, if user wants to cancel before
all of the seleciton has been checked. With this code I do not have a
way for the user to back out or cancel out of the Input Box process.
Any assistance is greatly appreciated. Thank you, Ron


Sub ValidateDataN()

'validate values in a column
Range("e12").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, 9).Select
Set Rng = Selection
FirstRow = Rng.Row
LastRow = Rng.Rows(Rng.Rows.Count).Row
col = "n"
'loop thru col n of database, but skip first row with headings
For i = FirstRow To LastRow
FixColumnN
Next i

End Sub

Private Sub FixColumnN()
Dim x As Integer, OK As Boolean
OK = True
'check to ensure six digits entered
If Len(Range("a1")(i, col).Value) < 6 Then
OK = False
End If

If OK = False Then
'Enter a new value in Column B
Range("a1")(i, col).Select
Range("a1")(i, col) = InputBox("Enter a 6 digit value")

'Range("a1")(i, col).Value = vbNullString
123 FixColumnN
End If

Rng.NumberFormat = "@"

End Sub