Running Total on Userform

R

Richard

I have a useform, which has many options on it. Each
option selected affects the costs of the project
concerned. This is all taken care of on the worksheets
behind.
What i would like however is to have a box which contains
the running total, so a user could click an option or a
supplier, thn change it to see instantly the difference in
costs.

This value already exists on the worksheet, buti am unable
to keep it 'live' on the userform.

Any ideas?
Thanks
Richard
 
T

Tom Ogilvy

Link your textbox to a worksheet cell such as B2. Then use the worksheet
calculate event to update that cell with the running total from the cell
where the running total is calculated (in this example, B1). This will
avoid the linking of the textbox overwriting your formula:

Private Sub Worksheet_Calculate()
On Error goto ErrHandler
Application.EnableEvents = False
Range("B2").Value = Range("B1").Value
ErrHandler:
Application.EnableEvents = True
End Sub
 
B

Bob Phillips

Hi Richard,

Something like this. I have assumed option buttons, but if it is checkboxes
it is the same principle

Private Sub OptionButton1_Click()
With TextBox1
If OptionButton1.Value = True Then
.Value = .Value + Worksheets("Sheet1").Range("A1").Value
Else
.Value = .Value - Worksheets("Sheet1").Range("A1").Value
End If
.Value = Format(.Value, "£#,##0.00")
End With
End Sub

Private Sub OptionButton2_Click()
With TextBox1
If OptionButton2.Value = True Then
.Value = .Value + Worksheets("Sheet1").Range("A2").Value
Else
.Value = .Value - Worksheets("Sheet1").Range("A2").Value
End If
.Value = Format(.Value, "£#,##0.00")
End With
End Sub

Private Sub UserForm_Initialize()
TextBox1.Value = 0
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top