trying to use data in textboxes for calculations
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: trying to use data in textboxes for calculations

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com