Displaying Total CMD Button

[sammclean23]

Hi All,

I'm very nearly there but just don't seem to be able to get the last bit to function correctly.

Basically, the user enters 'x' amount of numbers through the use of a form i.e. a number is typed into a text box and upon clicking 'enter' the value is added to the spreadsheet starting from A1 using an offset.

The final part is calculating the total of these numbers and displaying that total on the next available row. To do this, the user will click 'checkout' on the form.

I understand this is simple VBA but just can't succeed on this last bit. Im guessing I have to create a running total because there is no specific amount of numbers its not a case of "=sum(a1:a5)" for example.

I appreciate any help on this. Also, if setting the 'total' variable for cmdEnter, would I have to set it again for cmdCheckout to work?

The code I have so far is this:


Private Sub cmdCheckout_Click()
Dim Total As Double
Total = Total + txtPrice
ActiveCell.Value = Total


End Sub
Private Sub cmdEnter_Click()

ActiveCell.NumberFormat = "£0.00"
ActiveCell.Value = txtPrice.Text
ActiveCell.Offset(1, 0).Activate

txtPrice.Text = ""
txtPrice.SetFocus

End Sub

Private Sub UserForm_Click()
End Sub

Private Sub UserForm_Initialize()

Range("A1").Select
'Ensures range a1 is selected upon opening
End Sub

[Simon Lloyd]

If the numbers are going in to a specific column then you could do something likeApplication.WorksheetFunction.Sum(Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row))other than that im not sure i understand what you want :)

[sammclean23]

Yeah that would work but the range isnt specific!

Now you can probably see what I wanted, looking at it, I didnt really explain it that will did i :)

Basically, I have a form in which a user inputs a number (txtPrice) and presses (cmdEnter).

After clicking cmdCheckout, the total of the numbers entered is displayed and if the total is greater than 500, discount of 15% is applied.



Private Sub cmdCheckout_Click()
ActiveCell.NumberFormat = "£0.00"
'set the cell format to pounds
If Total > 500 Then
'if the total is greater than 500..
ActiveCell.Value = Total - (Total * 0.15)
'...the total is equal to the total - the total including discount

Else

ActiveCell.Value = Total
'Else the cell is equal to the total without discount

End If
Total = 0
End Sub
Private Sub cmdEnter_Click()
UserPrice = txtPrice
Total = Total + UserPrice
ActiveCell.NumberFormat = "£0.00"
'Sets the format of cells to pounds
ActiveCell.Value = txtPrice.Text
'The value in the cell is equal to the user entered value
ActiveCell.Offset(1, 0).Activate
'select cell on the next row
txtPrice.Text = ""
'Clear text box
txtPrice.SetFocus
'Set focus in text box

End Sub
Private Sub UserForm_Initialize()
Range("A1").Select
'Ensures range a1 is selected upon opening
End Sub