Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Total on Userform
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Total on Userform
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 -- Regards, Tom Ogilvy "Richard" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Total on Userform
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) "Richard" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
running total and average of that total after 3 events | Excel Discussion (Misc queries) | |||
running total and average of that total after 3 events | Excel Discussion (Misc queries) | |||
How can I use a worksheet while running a userform? | Excel Programming | |||
Closing the Userform when macro is running | Excel Programming |