trying to use data in textboxes for calculations

[Ricardo2]

This is my first try at VBA. I have a UserForm with 4 textboxes for a user to input data. I have written a subroutine to use the data and perform some mathematical calculations. What code is needed for the subroutine to get the data entered in the textboxes so they can be used in the calculations? What code do I need in the textbox itself?
Example:
Private Sub TextBox1_Change()
(Code?????)
End Sub
Can I use textboxes to receive numerical data (i.e., numbers)?
I'm new at this so I may not have explained this well. Please let me know if you need more detail.

[Tom Schreiner]

Hi
There are a number of ways you could do this.
To perform a calculation, the change event may or may not serve your purpose. Just depends. Just remember, if you use the change event with no further instructions, the code is going to run everytime a single character is entered or removed. Every "change".

If you need the data in all four textboxes before you can perform your calculation, then use a command button for now.


Place your routine in the commandbutton1_click event???

Post all of your code for better help...
Use as much detail as you are willing to give.
Tom

[Ricardo2]

Tom,

Here is the subroutine code that will run when the CommandButton_click button is clicked:

Sub ImprovedAccrualCalculation()
Dim amount, percentage, fromdate As Date, todate As Date
FormattedAmount = Format(amount, "$##,##0.00")
RevisedPercentage = percentage / 100
FormattedPercentage = Format(RevisedPercentage, "0.000000%")
result = amount * RevisedPercentage * (todate - fromdate + 1) / 365
RoundedResult = Application.WorksheetFunction.Round(result, 2)
FormattedResult = Format(RoundedResult, "$##,##0.00")
accrualdays = todate - fromdate + 1
Msg = FormattedAmount & " x " & FormattedPercentage & " accrued from "
Msg = Msg & fromdate & " to " & todate & " (" & accrualdays & " days)" & " = "
Msg = Msg & FormattedResult
MsgBox (Msg)
End Sub

I tested this code originally using InputBoxes to receive the data and it works fine. I don't know how to assign the value entered in Private Sub TextBox1_Change() (amount), Private Sub TextBox2_Change() (percentage), Private Sub TextBox3_Change() (fromdate), & Private Sub TextBox4_Change() (todate) on the UserFrom and have it carryover into the ImprovedAccrualCalculation subroutine so the calculation can occur.

The code may be a bit "wordy". This is my first attempt.

I appreciate the help.

Ricardo

[ This Message was edited by: Ricardo2 on 2002-04-10 05:43 ]

[Tom Schreiner]

This is not very pretty either.
Just thrown together...
But it might give you an idea on how to communicate the values from your text boxes....

Just copy this into your userform class module.

Assumes four textboxes and one button named

Textbox1
Textbox2
Textbox3
Textbox4
commandbutton1

Tom

[Tom Schreiner]

Just to let you know
When Variables are Dim inside a procedure, they are only available to that procedure.
If you put them at the top of the module then they are available to all procedures in the module.

Dim Amount As Currency
Dim Percentage As Double
Dim FromDate As Date
Dim ToDate As Date

Sub ImprovedAccrualCalculation()
FormattedAmount = Format(Amount, "$##,##0.00")
RevisedPercentage = Percentage / 100
FormattedPercentage = Format(RevisedPercentage, "0.000000%")
Result = Amount * RevisedPercentage * (ToDate - FromDate + 1) / 365
RoundedResult = Application.WorksheetFunction.Round(Result, 2)
FormattedResult = Format(RoundedResult, "$##,##0.00")
AccrualDays = ToDate - FromDate + 1
Msg = FormattedAmount & " x " & FormattedPercentage & " accrued from "
Msg = Msg & FromDate & " to " & ToDate & " (" & AccrualDays & " days)" & " = "
Msg = Msg & FormattedResult
MsgBox (Msg)
End Sub

Private Sub CommandButton1_Click()
If Not IsNumeric(TextBox1) Then Exit Sub
'you can place msgbox here to let user know what's up
If Not IsNumeric(TextBox2) Then Exit Sub
If Not IsDate(TextBox3) Then Exit Sub
If Not IsDate(TextBox4) Then Exit Sub
Amount = TextBox1.Value
Percentage = TextBox2.Value
FromDate = TextBox3.Value
ToDate = TextBox4.Value
ImprovedAccrualCalculation
End Sub

[Ricardo2]

It works!

But I've noticed something. I thought that you had to put subroutines in the Module and put code in UserForm to refer to that subroutine. That's how I had this originally set up. Even after making your changes, the calculation still didn't work. I cut and pasted the ImprovedAccrualCalculation subroutine in Private Sub CommandButton1_click and it works.

One last thing....

I wanted to insert MsgBoxes if the data entered was incorrect. I tried this:

If Not IsNumeric(TextBox1) Then MsgBox "Double check amount"
Exit Sub
If Not IsNumeric(TextBox2) Then MsgBox "Double check percentage"
Exit Sub
If Not IsDate(TextBox3) Then MsgBox "Double check From Date"
Exit Sub
If Not IsDate(TextBox4) Then MsgBox "Double check To Date"
Exit Sub

I tested this and the first MsgBox works. But when I tried with the other three fields, no MsgBox appeared, as though it skips the MsgBox code and goes straight to Exit Sub. What am I missing?

Thanks again.

Ricardo

[Ricardo2]

I figured out how to get around the problem. The code is:

If Not IsNumeric(TextBox1) Then
MsgBox "Double check amount"
Exit Sub
End If
If Not IsNumeric(TextBox2) Then
MsgBox "Double check percentage"
Exit Sub
End If
If Not IsDate(TextBox3) Then
MsgBox "Double check From Date"
Exit Sub
End If
If Not IsDate(TextBox4) Then
MsgBox "Double check To Date"
Exit Sub
End If

Actually, I found the answer in a Dummies book. Hey, at least it works!