I have a user form with a text box that I want only to
accept numbers with 6 digits.
How do you format text box to accept only numbers - which
can have preceding zeros? Can this format be carried
through to the worksheet where these numbers will be
stored?
How do you alert the user, prior to them moving onto the
other text boxes on the user form, that they have not
entered valid data and should reenter the data?

Thanks a million,
Kev.

More detail to help refine an answer.
I would like an "Error Alert" to occur at the time the
user enters or tabs away from the text box to the next
text box - thereby not having them enter data in all text
boxes on the user form and then the transfer of data to
the worksheet fails.
I currently have an "if" <6 statement at time of
selecting "OK" but unsure of how to relate this to the
actual textbox to save frustration of user having to then
reenter all data.
What do you use to display "error alerts"

Thanks again,
Kev.
-----Original Message-----
I have a user form with a text box that I want only to
accept numbers with 6 digits.
How do you format text box to accept only numbers -
which
can have preceding zeros? Can this format be carried
through to the worksheet where these numbers will be
stored?
How do you alert the user, prior to them moving onto the
other text boxes on the user form, that they have not
entered valid data and should reenter the data?

Thanks a million,
Kev.
.

First, maybe you could force them to only type numbers.

Second, I misread your post. I thought you had multiple textboxes to check.
This kind of thing will work with a single box, though.

Third. The value in the textbox will be text. But when you plop into a cell,
excel will see it as a number. You can either format that cell (custom 000000)
to show leading 0's or precede the value with an apostrophe (or even preformat
the cell as text).

Fourth, this seemed to work ok:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim DestCell As Range
Dim iCtr As Long
Set DestCell = Worksheets("sheet1").Range("a1")

For iCtr = 1 To 2
With DestCell.Offset(0, iCtr - 1)
.Value = Me.Controls("textbox" & iCtr).Value
.NumberFormat = "000000"
End With
Next iCtr
Unload Me
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.TextBox1
.Value = Format(.Value, "000000")
End With
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.TextBox2
.Value = Format(.Value, "000000")
End With
End Sub
Private Sub TextBox1_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)
Call ValidateTextBoxes(Me.TextBox1, KeyAscii)
End Sub
Private Sub TextBox2_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)
Call ValidateTextBoxes(Me.TextBox2, KeyAscii)
End Sub
Sub ValidateTextBoxes(tb As MSForms.TextBox, KeyAscii As MSForms.ReturnInteger)
With tb
If Len(.Value) < 6 Then
Select Case KeyAscii
Case 8 'backspace
If Len(.Value) 0 Then
.Value = Left(.Value, Len(.Value) - 1)
End If
Case 48 To 57
Case Else
KeyAscii = 0
End Select
Else
KeyAscii = 0
End If
End With
End Sub

=======
and here's the code cleaned up for just one textbox:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim DestCell As Range

Set DestCell = Worksheets("sheet1").Range("a1")
With DestCell
.Value = Me.TextBox1.Value
.NumberFormat = "000000"
End With

Unload Me
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.TextBox1
.Value = Format(.Value, "000000")
End With
End Sub
Private Sub TextBox1_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)

With Me.TextBox1
If Len(.Value) < 6 Then
Select Case KeyAscii
Case 8 'backspace
If Len(.Value) 0 Then
.Value = Left(.Value, Len(.Value) - 1)
End If
Case 48 To 57
Case Else
KeyAscii = 0
End Select
Else
KeyAscii = 0
End If
End With
End Sub



Kev wrote:

I have a user form with a text box that I want only to
accept numbers with 6 digits.
How do you format text box to accept only numbers - which
can have preceding zeros? Can this format be carried
through to the worksheet where these numbers will be
stored?
How do you alert the user, prior to them moving onto the
other text boxes on the user form, that they have not
entered valid data and should reenter the data?

Thanks a million,
Kev.

--

Dave Peterson

Thanks Dave... out of my league in coding and
interpretation at present but good for future reference.
In trying to create code for my problem, I get the
following - basic by all means compared to your response:

Private Sub cmdOK_Click()
AccNum = UserForm1.AccNum
CustName = UserForm1.CustName
'To only allow 6 digit account numbers to be used
If Len(AccNum) < 6 Then
msg = "Account Number must be 6 digits"
response = MsgBox(msg, vbOKCancel)
If response = vbOK Then
AccNum.Value = AccNum
CustName.Value = CustName
AccNum.SetFocus
If response = vbCancel Then
Unload Me
Exit Sub
-----------------
Where the user selects OK on the MsgBox, I am trying to
take the user back to the AccNum textbox to allow them to
only have to correct the account number - CustName
details retained in CustName textbox. I then need to go
back to the start of cmdOK_Click() routine to confirm
correct entry ie. 6 digits.
How can this be achieved??

Thanks again for any assistance,
Kev.

-----Original Message-----
First, maybe you could force them to only type numbers.

Second, I misread your post. I thought you had multiple
textboxes to check.
This kind of thing will work with a single box, though.

Third. The value in the textbox will be text. But when
you plop into a cell,
excel will see it as a number. You can either format
that cell (custom 000000)
to show leading 0's or precede the value with an
apostrophe (or even preformat
the cell as text).

Fourth, this seemed to work ok:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim DestCell As Range
Dim iCtr As Long
Set DestCell = Worksheets("sheet1").Range("a1")

For iCtr = 1 To 2
With DestCell.Offset(0, iCtr - 1)
.Value = Me.Controls("textbox" & iCtr).Value
.NumberFormat = "000000"
End With
Next iCtr
Unload Me
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
With Me.TextBox1
.Value = Format(.Value, "000000")
End With
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
With Me.TextBox2
.Value = Format(.Value, "000000")
End With
End Sub
Private Sub TextBox1_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)
Call ValidateTextBoxes(Me.TextBox1, KeyAscii)
End Sub
Private Sub TextBox2_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)
Call ValidateTextBoxes(Me.TextBox2, KeyAscii)
End Sub
Sub ValidateTextBoxes(tb As MSForms.TextBox, KeyAscii As
MSForms.ReturnInteger)
With tb
If Len(.Value) < 6 Then
Select Case KeyAscii
Case 8 'backspace
If Len(.Value) 0 Then
.Value = Left(.Value, Len
(.Value) - 1)
End If
Case 48 To 57
Case Else
KeyAscii = 0
End Select
Else
KeyAscii = 0
End If
End With
End Sub

=======
and here's the code cleaned up for just one textbox:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim DestCell As Range

Set DestCell = Worksheets("sheet1").Range("a1")
With DestCell
.Value = Me.TextBox1.Value
.NumberFormat = "000000"
End With

Unload Me
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
With Me.TextBox1
.Value = Format(.Value, "000000")
End With
End Sub
Private Sub TextBox1_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)

With Me.TextBox1
If Len(.Value) < 6 Then
Select Case KeyAscii
Case 8 'backspace
If Len(.Value) 0 Then
.Value = Left(.Value, Len
(.Value) - 1)
End If
Case 48 To 57
Case Else
KeyAscii = 0
End Select
Else
KeyAscii = 0
End If
End With
End Sub



Kev wrote:

I have a user form with a text box that I want only to
accept numbers with 6 digits.
How do you format text box to accept only numbers -
which
can have preceding zeros? Can this format be carried
through to the worksheet where these numbers will be
stored?
How do you alert the user, prior to them moving onto
the
other text boxes on the user form, that they have not
entered valid data and should reenter the data?

Thanks a million,
Kev.

--

Dave Peterson
.

I still would allow the user to enter less than 6 digits--supply your own
leading 0's.

Option Explicit

Sub testme()

Dim AccNum As String
Dim iCtr As String
Dim NonNumericFound As Boolean

'just for testing.
AccNum = "123"

if trim(AccNum) = "" then
'bad if left blank???
end if

AccNum = Right(String(6, "0") & AccNum, 6)

'this will catch most, but not all.
'scientific notation will be seen as ok
'1e3 will get OK
If IsNumeric(AccNum) Then
MsgBox "Ok"
Else
MsgBox "bad"
End If

'I think I'd use the other code,
'or check character by character
NonNumericFound = False
For iCtr = 1 To Len(AccNum)
If IsNumeric(Mid(AccNum, iCtr, 1)) Then
'keep looking
Else
NonNumericFound = True
Exit For
End If
Next iCtr

If NonNumericFound Then
MsgBox "bad"
Else
MsgBox "ok"
End If

End Sub

kev wrote:

Thanks Dave... out of my league in coding and
interpretation at present but good for future reference.
In trying to create code for my problem, I get the
following - basic by all means compared to your response:

Private Sub cmdOK_Click()
AccNum = UserForm1.AccNum
CustName = UserForm1.CustName
'To only allow 6 digit account numbers to be used
If Len(AccNum) < 6 Then
msg = "Account Number must be 6 digits"
response = MsgBox(msg, vbOKCancel)
If response = vbOK Then
AccNum.Value = AccNum
CustName.Value = CustName
AccNum.SetFocus
If response = vbCancel Then
Unload Me
Exit Sub
-----------------
Where the user selects OK on the MsgBox, I am trying to
take the user back to the AccNum textbox to allow them to
only have to correct the account number - CustName
details retained in CustName textbox. I then need to go
back to the start of cmdOK_Click() routine to confirm
correct entry ie. 6 digits.
How can this be achieved??

Thanks again for any assistance,
Kev.

-----Original Message-----
First, maybe you could force them to only type numbers.

Second, I misread your post. I thought you had multiple
textboxes to check.
This kind of thing will work with a single box, though.

Third. The value in the textbox will be text. But when
you plop into a cell,
excel will see it as a number. You can either format
that cell (custom 000000)
to show leading 0's or precede the value with an
apostrophe (or even preformat
the cell as text).

Fourth, this seemed to work ok:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim DestCell As Range
Dim iCtr As Long
Set DestCell = Worksheets("sheet1").Range("a1")

For iCtr = 1 To 2
With DestCell.Offset(0, iCtr - 1)
.Value = Me.Controls("textbox" & iCtr).Value
.NumberFormat = "000000"
End With
Next iCtr
Unload Me
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
With Me.TextBox1
.Value = Format(.Value, "000000")
End With
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
With Me.TextBox2
.Value = Format(.Value, "000000")
End With
End Sub
Private Sub TextBox1_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)
Call ValidateTextBoxes(Me.TextBox1, KeyAscii)
End Sub
Private Sub TextBox2_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)
Call ValidateTextBoxes(Me.TextBox2, KeyAscii)
End Sub
Sub ValidateTextBoxes(tb As MSForms.TextBox, KeyAscii As
MSForms.ReturnInteger)
With tb
If Len(.Value) < 6 Then
Select Case KeyAscii
Case 8 'backspace
If Len(.Value) 0 Then
.Value = Left(.Value, Len
(.Value) - 1)
End If
Case 48 To 57
Case Else
KeyAscii = 0
End Select
Else
KeyAscii = 0
End If
End With
End Sub

=======
and here's the code cleaned up for just one textbox:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim DestCell As Range

Set DestCell = Worksheets("sheet1").Range("a1")
With DestCell
.Value = Me.TextBox1.Value
.NumberFormat = "000000"
End With

Unload Me
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
With Me.TextBox1
.Value = Format(.Value, "000000")
End With
End Sub
Private Sub TextBox1_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)

With Me.TextBox1
If Len(.Value) < 6 Then
Select Case KeyAscii
Case 8 'backspace
If Len(.Value) 0 Then
.Value = Left(.Value, Len
(.Value) - 1)
End If
Case 48 To 57
Case Else
KeyAscii = 0
End Select
Else
KeyAscii = 0
End If
End With
End Sub



Kev wrote:

I have a user form with a text box that I want only to
accept numbers with 6 digits.
How do you format text box to accept only numbers -
which
can have preceding zeros? Can this format be carried
through to the worksheet where these numbers will be
stored?
How do you alert the user, prior to them moving onto
the
other text boxes on the user form, that they have not
entered valid data and should reenter the data?

Thanks a million,
Kev.

--

Dave Peterson
.


--

Dave Peterson

My learning of VBA has been disjointed - ie. as you are
exposed to functions/procedures you then know about their
existence. As such this question should come as no
surprise - is this a sub routine of the Private Sub cmdOK
or a separtate procedure? If separate how do you call
that routine during the cmdOK procedure??

Also trying to format the range that contains the numbers
from the textbox1 with the following:
Worksheets("Sheet1").Range("Range1").NumberFormat
= "000000" .... but still stays as TEXT. What have I
missed???? Trying to incorporate "DestCell" from your
original code but having trouble making "DestCell" be the
last unused cell in a column? Trying to use it
with "clastrow+1" code.
Appreciate your time....
Many thanks,
Kev.


-----Original Message-----
I still would allow the user to enter less than 6 digits-
-supply your own
leading 0's.

Option Explicit

Sub testme()

Dim AccNum As String
Dim iCtr As String
Dim NonNumericFound As Boolean

'just for testing.
AccNum = "123"

if trim(AccNum) = "" then
'bad if left blank???
end if

AccNum = Right(String(6, "0") & AccNum, 6)

'this will catch most, but not all.
'scientific notation will be seen as ok
'1e3 will get OK
If IsNumeric(AccNum) Then
MsgBox "Ok"
Else
MsgBox "bad"
End If

'I think I'd use the other code,
'or check character by character
NonNumericFound = False
For iCtr = 1 To Len(AccNum)
If IsNumeric(Mid(AccNum, iCtr, 1)) Then
'keep looking
Else
NonNumericFound = True
Exit For
End If
Next iCtr

If NonNumericFound Then
MsgBox "bad"
Else
MsgBox "ok"
End If

End Sub

kev wrote:

Thanks Dave... out of my league in coding and
interpretation at present but good for future
reference.
In trying to create code for my problem, I get the
following - basic by all means compared to your
response:

Private Sub cmdOK_Click()
AccNum = UserForm1.AccNum
CustName = UserForm1.CustName
'To only allow 6 digit account numbers to be used
If Len(AccNum) < 6 Then
msg = "Account Number must be 6 digits"
response = MsgBox(msg, vbOKCancel)
If response = vbOK Then
AccNum.Value = AccNum
CustName.Value = CustName
AccNum.SetFocus
If response = vbCancel Then
Unload Me
Exit Sub
-----------------
Where the user selects OK on the MsgBox, I am trying to
take the user back to the AccNum textbox to allow them
to
only have to correct the account number - CustName
details retained in CustName textbox. I then need to
go
back to the start of cmdOK_Click() routine to confirm
correct entry ie. 6 digits.
How can this be achieved??

Thanks again for any assistance,
Kev.

-----Original Message-----
First, maybe you could force them to only type
numbers.

Second, I misread your post. I thought you had
multiple
textboxes to check.
This kind of thing will work with a single box,
though.

Third. The value in the textbox will be text. But
when
you plop into a cell,
excel will see it as a number. You can either format
that cell (custom 000000)
to show leading 0's or precede the value with an
apostrophe (or even preformat
the cell as text).

Fourth, this seemed to work ok:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim DestCell As Range
Dim iCtr As Long
Set DestCell = Worksheets("sheet1").Range("a1")

For iCtr = 1 To 2
With DestCell.Offset(0, iCtr - 1)
.Value = Me.Controls("textbox" &
iCtr).Value
.NumberFormat = "000000"
End With
Next iCtr
Unload Me
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
With Me.TextBox1
.Value = Format(.Value, "000000")
End With
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
With Me.TextBox2
.Value = Format(.Value, "000000")
End With
End Sub
Private Sub TextBox1_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)
Call ValidateTextBoxes(Me.TextBox1, KeyAscii)
End Sub
Private Sub TextBox2_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)
Call ValidateTextBoxes(Me.TextBox2, KeyAscii)
End Sub
Sub ValidateTextBoxes(tb As MSForms.TextBox, KeyAscii
As
MSForms.ReturnInteger)
With tb
If Len(.Value) < 6 Then
Select Case KeyAscii
Case 8 'backspace
If Len(.Value) 0 Then
.Value = Left(.Value, Len
(.Value) - 1)
End If
Case 48 To 57
Case Else
KeyAscii = 0
End Select
Else
KeyAscii = 0
End If
End With
End Sub

=======
and here's the code cleaned up for just one textbox:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim DestCell As Range

Set DestCell = Worksheets("sheet1").Range("a1")
With DestCell
.Value = Me.TextBox1.Value
.NumberFormat = "000000"
End With

Unload Me
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
With Me.TextBox1
.Value = Format(.Value, "000000")
End With
End Sub
Private Sub TextBox1_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)

With Me.TextBox1
If Len(.Value) < 6 Then
Select Case KeyAscii
Case 8 'backspace
If Len(.Value) 0 Then
.Value = Left(.Value, Len
(.Value) - 1)
End If
Case 48 To 57
Case Else
KeyAscii = 0
End Select
Else
KeyAscii = 0
End If
End With
End Sub



Kev wrote:

I have a user form with a text box that I want only
to
accept numbers with 6 digits.
How do you format text box to accept only numbers -
which
can have preceding zeros? Can this format be
carried
through to the worksheet where these numbers will be
stored?
How do you alert the user, prior to them moving onto
the
other text boxes on the user form, that they have
not
entered valid data and should reenter the data?

Thanks a million,
Kev.

--

Dave Peterson
.


--

Dave Peterson
.

It was just a sample that you could steal from an include in your own
cmdOK_click routine:

Just some comments.

You should declare all your variables. It'll make debugging much simpler (seems
like more work up front, but it pays for itself in the long run).

Adding Option Explicit to the top of your module tells the VBE that you want to
use only declared variables. If you have something like:

Dim MyLevel as long
.....
MyLevel = MyLeve1 + 1

This will cause an error. One of those MyLevel's has a one, not ell as the last
character. This kind of error is difficult for me to find, so I let excel do it
for me.

And I changed the name of your textboxes. CustName became tbCustName and AccNum
became tbAccNum. I don't want variables to have the same name as the controls.

In fact, this line:
AccNum = UserForm1.AccNum
didn't set a variable named AccNum to the value in the textbox named AccNum. It
just grabbed the value from the textbox and plopped it right back where it came
from.

AccNum (by itself) is the same as Userform1.Accnum when it's in that userform's
codemodule.

Instead of using UserForm1. in your code, you can use the Me. keyword. Me.
represents the thing that holds the code. In this case, it's UserForm1.

If you decide to make another userform (called Userform2), then if you
copy|paste code, you'll have one less thing to worry about fixing.

And instead of putting up a msgbox that allows the user to dismiss the form, I'd
just put up an info only msgbox. Let the user hit the Cancel button on the user
form to dismiss it.

And I didn't use cLastRow. I think you're combining responses from others, too.

But something like this may get you going again:

Option Explicit
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdOK_Click()

Dim AccNum As String
Dim CustName As String
Dim response As Long
Dim AccNumIsOk As Boolean
Dim iCtr As Long
Dim DestCell As Range
Dim NextRow As Long
Dim msg As String
Dim maxLength As String

maxLength = 6

AccNum = Me.tbAccNum
AccNumIsOk = True
CustName = Me.tbCustName
'To only allow 6 digit account numbers to be used
If Len(AccNum) < maxLength Then
AccNumIsOk = False
Else
For iCtr = 1 To Len(AccNum)
If IsNumeric(Mid(AccNum, iCtr, 1)) Then
'keep looking
Else
AccNumIsOk = False
Exit For 'stop looking for more errors
End If
Next iCtr
End If

If AccNumIsOk Then
With Worksheets("sheet1")
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
Set DestCell = .Cells(NextRow, "A")
End With
With DestCell
.Value = AccNum
.NumberFormat = "000000"
.Offset(0, 1).Value = CustName
'if you want to clear the value and let the user do it again!
Me.tbAccNum = ""
Me.tbCustName = ""
'Unload Me 'if you want to get out after you do a single entry
End With
Else
msg = "Account Number must be exactly 6 digits"
MsgBox prompt:=msg, Buttons:=vbOKOnly
tbAccNum.SetFocus
'let them dismiss this warning message and
'use the cancel button on the form to exit
End If

End Sub



Kev wrote:

My learning of VBA has been disjointed - ie. as you are
exposed to functions/procedures you then know about their
existence. As such this question should come as no
surprise - is this a sub routine of the Private Sub cmdOK
or a separtate procedure? If separate how do you call
that routine during the cmdOK procedure??

Also trying to format the range that contains the numbers
from the textbox1 with the following:
Worksheets("Sheet1").Range("Range1").NumberFormat
= "000000" .... but still stays as TEXT. What have I
missed???? Trying to incorporate "DestCell" from your
original code but having trouble making "DestCell" be the
last unused cell in a column? Trying to use it
with "clastrow+1" code.
Appreciate your time....
Many thanks,
Kev.

<<snipped
--

Dave Peterson