Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cumulative Totals on Userforms (VBA/Word/97)

    I have a userform where numeric values are entered. I do not know how to create a visual display on the form that updates as the user enters numbers. It is desired as the user will then get instant feedback for the values he/she enters as these must not total more than 100 (as in 100%). After completing the form entries, these individual values are then passed thru to a new word document that is populated with bookmarks.

    The attachment shows part of the form and the running total circled in red. (No, I haven't got that far, it was drawn using a graphics program.)

    As I have no idea on how to create the required effect, any suggestions would be appreciated.

    Thanks, Leigh

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cumulative Totals on Userforms (VBA/Word/97)

    The text boxes used for entering data have three events that you could use for this:
    <UL><LI>The Change event occurs when the value of the text box changes, i.e. while the user is typing. This will provide the most direct feedback, but as a downside, it will react to all typos too, before the user has had a chance of correcting them
    <LI>The BeforeUpdate event occurs when the user moves on to another control; you can set the Cancel argument to True to prevent the update from happening.
    <LI>The AfterUpdate event occurs when the user moves to another control, after BeforeUpdate. AfterUpdate can't be canceled.[/list]You can recalculate the total in each of these events and set the caption of a label to the new total.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cumulative Totals on Userforms (VBA/Word/97)

    Thanks Hans for that info, but what specific properties for my txtRunTotal text box do I set? (Assuming txtRunTotal is the control under the red ellipse.)

    Leigh

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cumulative Totals on Userforms (VBA/Word/97)

    Hello Leigh,

    You should use events for the text boxes used for data entry. Let's assume that they are named txtMetamyelocytes, txtProNormoblasts, etc. up to txtMyeloidErythroidRatio.

    You can create a function that will be called from the BeforeUpdate event of each of these:

    Private Function UpdateTotal() As Boolean
    Dim dblTotal As Double
    ' Calculate total
    dblTotal = CDbl(txtMetamyelocytes) + CDbl(txtPrNormoblasts) + ... + CDbl(txtMyeloidErythroidRatio)
    ' Set text box value
    txtRunTotal.Text = dblTotal
    If dblTotal > 100 Then
    ' Optional - issue warning message
    MsgBox "Warning: the total is " & dblTotal & " %"
    ' Return True to cancel update
    UpdateTotal = True
    End If
    End Function

    For each of the data entry text boxes, type an event procedure:

    Private Sub txtMetamyelocytes_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = UpdateTotal
    End Sub

    (repeat for the others)

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cumulative Totals on Userforms (VBA/Word/97)

    Thanks Hans, this is the direction I needed.

Posting Permissions

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