PDA

View Full Version : confirm entered data with msg box



maninjapan
09-16-2008, 04:40 AM
After posting a number of rows of data to the spreadsheet through CommandNextContract_Click I would like to subtotal the data. What would a command look like to total all data since the last total....(the number of rows to subtotal will vary each time,)
This is what I have so far

Private Sub CommandNextContract_Click()
' Ensure all data is entered

If ComboBoxTrader.Text = "" Or _
ComboBoxContract.Text = "" Or _
ComboBoxmonth.Text = "" Or _
TextBoxLots.Text = "" Or _
TextBoxPL.Text = "" Or _
ComboBoxCurrency.Text = "" Then
MsgBox "Enter Data!", vbInformation, "Error Message"
TextBoxDate.SetFocus
Exit Sub

Else



' Make sure Sheet1 is active
Sheets("Sheet1").Activate

' Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
' Transfer the Date,Trader,Contract,Month,Lots,P&L,Currency
Cells(NextRow, 1) = TextBoxDate.Text
Cells(NextRow, 2) = ComboBoxTrader.Text
Cells(NextRow, 3) = ComboBoxContract.Text
Cells(NextRow, 4) = ComboBoxmonth.Text
Cells(NextRow, 5) = TextBoxLots.Text
Cells(NextRow, 6) = TextBoxPL.Text
Cells(NextRow, 7) = ComboBoxCurrency.Text

' Clear Trade Info for next entry
ComboBoxContract.Text = ""
ComboBoxmonth.Text = ""
TextBoxLots.Text = ""
TextBoxPL.Text = ""
ComboBoxCurrency.Text = "USD"
ComboBoxContract.SetFocus
End If


End Sub

MaximS
09-16-2008, 05:05 AM
Add small text box called like ComboBoxNumberOfRows

and add folowing code after line:

' Clear Trade Info for next entry



'Remove -1 if you don't have header
ComboBoxNumberOfRows.Text = NextRow - 1

maninjapan
09-16-2008, 05:19 AM
Sorry Maxim I dont think I explained myself well. Ill try agian
TextBoxPL.Text will be a Number and I would like the subtotal to give me the totals of all the entered TextBoxPL.Text

Bob Phillips
09-16-2008, 06:12 AM
How do you know wher posting the entries fro the textbox started and when they have ended?

maninjapan
09-16-2008, 09:40 AM
How do you know wher posting the entries fro the textbox started and when they have ended?

Thats the million dollar question XLD. Thats the part I cant figure out....
Anyone got any ideas.....???

I wouldnt mind if it held it somewhere and then placed all the data and it subtotal at once if that would make things easier.....

Bob Phillips
09-16-2008, 10:10 AM
Surely, only the user can determine when it has finished?

maninjapan
09-16-2008, 11:36 PM
My idea is that pressing the subtotal button would total all the figures entered. Maybe the data could be in a seperate spread sheet, and clicking Subtotal would add a total for all the data and then transfer it over to the main sheet.

Bob Phillips
09-17-2008, 01:13 AM
Okay, as you will be driving it from a button, try this



Sub AddTotals()
Const THE_COL As String = "E"
Dim LastRow As Long
Dim TotalsCells As Range

With ActiveSheet

On Error Resume Next
Set TotalsCells = .Columns(THE_COL).SpecialCells(xlCellTypeFormulas)
Set TotalsCells = TotalsCells.Areas(TotalsCells.Cells.Count)
On Error GoTo 0
If TotalsCells Is Nothing Then
Set TotalsCells = .Cells(1, THE_COL)
Else
Set TotalsCells = TotalsCells.Offset(1, 0)
End If
LastRow = .Cells(.Rows.Count, THE_COL).End(xlUp).Row
.Cells(LastRow + 1, THE_COL).Formula = _
"=SUM(" & TotalsCells.Address & ":" & .Cells(LastRow, THE_COL).Address & ")"
End With
End Sub