excel ; sum only items that a user selects

[tav1035]

I want to build a chart with each cell that contains an item with a hidden price or value. Then a user can choose the cells or items and the items sum up to a total.
Kinda like a shopping cart in excel.
Also as they choose the cell it will highlight that chosen cell.

 

[Chance1234]

off the top of my head and a bit messy way of doing it,

on the code for the sheet under the doubleclick property add in something like

Mycell as Target

Mycell.interior.colorindex = 5

that will highlight the cell purple if they double click on it

then create a module and add in a function like this

Funciton SumHighlightedItems(Shopping as Range)
'nb shopping range would eb the range with the cells which contains the item

For each cell in Shopping

if cell.interior.colorinex = 5 then

shopping = shoping + cell.value
end if
next

cant o[pen excel on my machine at the moment but it could work somewhere along those lines


Chance

 

[tav1035]

I guess i'm confused... but the idea of double clicking/highlighting the cell and totaling the values is what i'm looking for. But I'm new at writing macros. Help?

 

[segmentationfault]

Try this.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim pcelCell As Range
Dim pdblSum As Double

For Each pcelCell In Target.Cells
If IsNumeric(pcelCell) Then
pdblSum = pdblSum + pcelCell.Value
End If
Next

Application.StatusBar = "Sum: " & pdblSum
End Sub

 

[segmentationfault]

This one will is much much faster if the user selects entire rows or columns.

By the way, these should go in ThisWorkbook.


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim pcelCell As Range
Dim pdblSum As Double
Dim plngMaxRow As Long
Dim plngMaxCol As Long

plngMaxRow = Sh.UsedRange.Row + Sh.UsedRange.Rows.Count
plngMaxCol = Sh.UsedRange.Column + Sh.UsedRange.Columns.Count

For Each pcelCell In Target.Cells
If pcelCell.Row > plngMaxRow Or pcelCell.Column > plngMaxCol Then Exit For
If IsNumeric(pcelCell) Then
pdblSum = pdblSum + pcelCell.Value
End If
Next

Application.StatusBar = "Sum: " & pdblSum
End Sub

 

[segmentationfault]

I still wish we could edit our posts on this forum. I must average about 3 corrections to every post I make.

I reread your question and see that I gave the wrong answer twice. If you truly want to implement a shopping cart in Excel, I imagine you would want to allow for more than 1 of each item. I include two examples, one which allows for a quantity column and one which doesn't.


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim pcelCell As Range
Dim pdblSum As Double
Dim plngMaxRow As Long
Dim plngMaxCol As Long

plngMaxRow = Sh.UsedRange.Row + Sh.UsedRange.Rows.Count
plngMaxCol = Sh.UsedRange.Column + Sh.UsedRange.Columns.Count

For Each pcelCell In Target.Cells
If pcelCell.Row > plngMaxRow Or pcelCell.Column > plngMaxCol Then Exit For
If Not IsNumeric(pcelCell) Then
' Cells(pcelCell.Row, pcelCell.Column + 1) points to the quantity cell,
' in this case, it is the same row, next column. We set this to
' 1, because 0 is meaningless (the user won't select the item for 0).
If Cells(pcelCell.Row, pcelCell.Column + 1) = 0 Then Cells(pcelCell.Row, pcelCell.Column + 1) = 1
pdblSum = pdblSum + Cells(pcelCell.Row, pcelCell.Column + 1) * Prices(pcelCell.Value)
End If
Next

Application.StatusBar = "Sum=" & pdblSum
End Sub


And the example with no quantity input:


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim pcelCell As Range
Dim pdblSum As Double
Dim plngMaxRow As Long
Dim plngMaxCol As Long

plngMaxRow = Sh.UsedRange.Row + Sh.UsedRange.Rows.Count
plngMaxCol = Sh.UsedRange.Column + Sh.UsedRange.Columns.Count

For Each pcelCell In Target.Cells
If pcelCell.Row > plngMaxRow Or pcelCell.Column > plngMaxCol Then Exit For
If Not IsNumeric(pcelCell) Then
pdblSum = pdblSum + Cells(pcelCell.Row, pcelCell.Column + 1) * Prices(pcelCell.Value)
End If
Next

Application.StatusBar = "Sum=" & pdblSum
End Sub


And both of these require a second function to define the prices of your items.


Private Function Prices(Item As String)
Prices = 0
If Item = "Car" Then Prices = 25000
If Item = "Rock" Then Prices = 2.5
If Item = "Pencil" Then Prices = 0.99
If Item = "Penny" Then Prices = 0.01
End Function


And cells bearing the text "Car" will add 25000 times the quantity, if specified, and so on.

 

[tav1035]

When it prompts me for a macro name? what the name I use

 

[segmentationfault]

This macro will run whenever a cell is selected, therefore do not start it from the tool bar.

 

[tav1035]

Can't seem to get it to work can you e-mail me the excel file at ----> tvondra@hotmail.com
Then I can explore it. Thanks for your help