trying to use data in textboxes for calculations

Ricardo2

New Member
Joined
Apr 8, 2002
Messages
14
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top