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