Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Calculation on UserForm

    On a userform: The user will enter gross sales, adjustments and sales tax into three text boxes. "Net" Sales is derived from that information.

    The question: Is it possible to show net sales on that userform and have it auto-calculate WHILE the user is entering information? This would act as a cross-check and could prevent the user from making a mistake.
    - Ricky

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation on UserForm

    Here's something simple as a starting point:
    <pre>Option Explicit

    Private Sub txtAdjustments_Change()
    Call calcTotal
    End Sub

    Private Sub txtSales_Change()
    Call calcTotal
    End Sub

    Private Sub txtTax_Change()
    Call calcTotal
    End Sub

    Sub calcTotal()
    Dim curNet As Currency
    curNet = 0
    If IsNumeric(txtSales.Text) Then
    curNet = curNet + CCur(txtSales.Text)
    End If
    If IsNumeric(txtAdjustments.Text) Then
    curNet = curNet - CCur(txtAdjustments.Text)
    End If
    If IsNumeric(txtTax.Text) Then
    curNet = curNet - CCur(txtTax.Text)
    End If
    lblTotal.Caption = Format(curNet, "$0.00")
    End Sub</pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculation on UserForm

    Thanks again Geoff - That was way too cool! I added the code almost word for word (a few name changes) and it worked like a charm. But I don't know what "Option Explicit" means and I did not enter those words anywhere. Will this present a problem?
    - Ricky

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation on UserForm

    Ricky,

    "Option Explicit" means that all variables MUST be declared explicitly. It's not mandatory to include this- but it is very good coding practice.

    For instance, if you had accidentally used "currNet" instead of "curNet", you may not have noticed it was the incorrect name. If you left the "option Explicit" out, it would not have given you a compile error- but you would have gotten the wrong answer, and it may have taken you some time to find out why. With the "Option Explicit" left in, you would have received a compile error.

    You can set your editor to always include the Explicit in new code- in the VBA editor, Tools, Options, and under the Editor tab, click on "Require Variable Declaration".
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

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