UserForm TextBoxes
 

I have created a few UserForms and one of them is for the User to input
various amounts as required.
I'm having some difficulty in understanding how the dim statement works in
determining what the input type that can be entered, etc.

Some things I can't get right a
I want to restrict the input to numbers only.
I want the TextBoxes to be blank and not show a "0" OR have it so that the 0
amount is highlighted so that when the user clicks in the box it overwrites
the 0. (When I use "" instead of 0 when resetting the Textboxes to blank I
get a type missmatch alert.)

Below is my "creation" which may be a bit rough but it works other than the
problems above.

Thanks for azny help!
Rob

Private Sub OK_Click()
Dim L As Integer
Dim G As Integer
Dim C As Integer

L = LoanTextBox.Value 'Loan amount
G = GiftTextBox.Value 'Gift amount
C = LoanGiftTextBox.Value 'Converted (Loan to a Gift) amount

If L = 0 And G = 0 And C = 0 Then
MsgBox "You must enter an amount!"
Exit Sub
End If
If C < 0 And L < 0 Then GoTo Message1
If C < 0 And G < 0 Then
Message1:
MsgBox "You cannot convert a loan and also enter any amounts for Loan or
Gift at the same time!"
LoanTextBox.Value = 0
GiftTextBox.Value = 0
LoanGiftTextBox.Value = 0
Exit Sub
End If
If L < 0 Then
ActiveCell.Offset(0, 1).Value = L
End If
If G < 0 Then
ActiveCell.Offset(0, 2).Value = G
End If
If C ufLoanGift.TextBox1.Text Then
Unload Me
MsgBox "The amount you have entered for convertion to Gift is more than the
total of the Loans for this person."
ufLoanGift.TextBox1.Text = ActiveCell.Offset(0, 6).Value
ufLoanGift.Show
Else
If C < 0 Then
ActiveCell.Offset(0, 1).Value = -C
ActiveCell.Offset(0, 2).Value = C
ActiveCell.Offset(0, 4).Value = Comment
End If
Range("C221").End(xlUp)(1, 3).Select
ActiveCell.Offset(0, 4).ClearContents
ActiveCell.Offset(0, 1).Select
With Sheet1
.PivotTables("PivotTable1").RefreshTable
End With
Unload Me
ufDate.Show
End If
End Sub

Private Sub LoanTextBox_Change()
If C < 0 Then
MsgBox "You cannot enter an amount in this box if there is an amounts in the
Convert Loan to Gift box!"
C = 0
End If
End Sub
Private Sub GiftTextBox_Change()
If C < 0 Then
MsgBox "You cannot enter an amount in this box if there is an amounts in the
Convert Loan to Gift box!"
C = 0
End If
End Sub
Private Sub LoanGiftTextBox_Change()
If L < 0 Then GoTo Message
If G < 0 Then
Message:
MsgBox "You cannot enter an amount in this box if there are amounts in the
Loan or Gift boxes!"
L = 0
G = 0
End If
End Sub

On Thu, 28 Jul 2005 09:56:08 +0930, "Rob" <NA wrote:
Some things I can't get right a
I want to restrict the input to numbers only.

I would use the WorksheetFunction ISNUMBER to check that the value is
numeric.. you can use a sub-routine that runs upon exiting the field..

Private Sub LoanTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Application.WorksheetFunction.IsNumber(txtBox) Then
Exit Sub
Else
MsgBox ("Sorry, please use only numbers")
LoanTextBox.SetFocus
End If
End Sub

Whenever someone leaves this text box, the sub-routine will check to
see if the value is numeric. If it is, it exits the sub-routine.
Otherwise, it gives them a message box that says they must use only
numbers, and sets the focus back into the text box. If they try to
leave again, it will do the same unless they changed their entry to
numeric.

I want the TextBoxes to be blank and not show a "0" OR have it so that the 0
amount is highlighted so that when the user clicks in the box it overwrites
the 0. (When I use "" instead of 0 when resetting the Textboxes to blank I
get a type missmatch alert.)

LoanTextBox.Value = "" 'Sets them to a null value (blank)

You could also do a sub-routine like the one above to clear it when
they click on it. Set your initial value to "0" and then use this:

Private Sub LoanTextBox_GotFocus()
LoanTextBox.Value = ""
End Sub

That will clear it when they tab to or click on the text box.

MP-
--
"Learning is a behavior that results from consequences."
B.F. Skinner

Thanks Mangus. Sorry It took so long for my reply. It seems to have
missed my Outlook Express and I had to go to the web to find your
answer.

Ihaven't had a chance to try it yet, but It looks good.

Rob


--
RobNobel
------------------------------------------------------------------------
RobNobel's Profile: http://www.excelforum.com/member.php...o&userid=25985
View this thread: http://www.excelforum.com/showthread...hreadid=390807