Cumulative Totals on Userforms (VBA/Word/97)

Topic

New Reply

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

Reply | Quote

Replies

The text boxes used for entering data have three events that you could use for this:

• 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
• 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.
• 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.

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

Thanks Hans, this is the direction I needed.

Reply | Quote