Display a running total on a form 1

[cmonthetic]

Hi,

Using Excel 97.

I have several forms used for data input. On 1 of the forms I need to have the running total displayed which updates when each new record is input, the running total is referenced from one of the worksheets.

I have tried using a Label to display the total, this does display the total from the cell referenced but it then puts that total displayed in the Label into the orignal cell referenced deleting the formula, obviously not what I need!

Does anyone have any ideas on how to display the total from the spreadsheet on the form without messing up the original formula?

TIA

 

[xlbo]

how are you referencing this cell ?? sounds like you are trying to use a userform but in that case, it should only write back to the worksheet if you tell it to.....need some more info here (and pertinent code would be good as well)

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[cmonthetic]

hi xlbo,

The code for the form and the 3 buttons on the form is below:

Private Sub CancelB_Click()
unload Me

End Sub

Private Sub ClearB_Click()
Call UserForm_Initialize
End Sub


Private Sub OKB_Click()
ActiveWorkbook.Sheets("SectionB").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = SerialNoB.Value
ActiveCell.Offset(0, 2) = DateB.Value
ActiveCell.Offset(0, 3) = DescriptionB.Value
ActiveCell.Offset(0, 4) = RequestedByB.Value
ActiveCell.Offset(0, 5) = SupplierB.Value
ActiveCell.Offset(0, 6) = InvoiceNoB.Value
ActiveCell.Offset(0, 7) = NetB.Value
ActiveCell.Offset(0, 8) = VatB.Value
ActiveCell.Offset(0, 10) = DateGoodsReceivedB.Value
ActiveCell.Offset(0, 11) = NotesB.Value
Range("A1").Select

End Sub

Private Sub UserForm_Initialize()
SerialNoB.Value = ""
DateB.Value = ""
DescriptionB.Value = ""
RequestedByB.Value = ""
SupplierB.Value = ""
InvoiceNoB.Value = ""
NetB.Value = ""
VatB.Value = ""
DateGoodsReceivedB.Value = ""
NotesB.Value = ""
SerialNoB.SetFocus

End Sub

I did try putting a label directly onto the form and using the cell with the running total on the worksheet(SectionB!B101) in as the control source but I know that is incorrect.

I really just need to get the Value from cell B101 to display on the form.

Regards

 

[xlbo]

In the initialise sub, just use

lbl_MyLabel.caption = Range("B101").text

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[cmonthetic]

Thanks for the response, works a treat.

Have a star