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