VBA help needed for making a range show certain table stats after the user has selected an autoshape

[taryn_1]

I am using Microsoft Excel 2007 and I have two sheets. One is called Navigation and the other one is called Products.

The Products sheet contains a table called productsT which has two columns namely: Name Of Product and Units Sold.

The code I'm using is the following on the Workbook Open Event:

Private Sub Workbook_Open()


Sheets("Navigation").Activate

Range("B6") = " "
Range("B6") = "Select Your Product to View the Total Units Sold:"
Range("B6").Font.Color = vbWhite
Range("B6").Font.Bold = True
Range("B6").Font.Size = 12
Range("B6").Select
Selection.Columns.AutoFit

'The next procedure clears all existing autoshapes and generates new ones on demand
Call DGS1


Dim shp1 As Shape
Dim shp2 As Shape
Dim shp3 As Shape

Set shp1 = ActiveSheet.Shapes.AddShape(5, 123, 110, 200, 45)
shp1.TextFrame.Characters.Text = "Ice Cream"
shp1.TextEffect.FontBold = msoTrue
shp1.TextEffect.FontSize = 15

shp1.ShapeStyle = msoShapeStylePreset37

Set shp2 = ActiveSheet.Shapes.AddShape(5, 123, 155, 200, 45)
shp2.TextFrame.Characters.Text = "Chocolate Cake"
shp2.TextEffect.FontBold = msoTrue
shp2.TextEffect.FontSize = 15

shp2.ShapeStyle = msoShapeStylePreset37

Set shp3 = ActiveSheet.Shapes.AddShape(5, 123, 200, 200, 45)
shp3.TextFrame.Characters.Text = "Sandwiches"
shp3.TextEffect.FontBold = msoTrue
shp3.TextEffect.FontSize = 15

shp3.ShapeStyle = msoShapeStylePreset37

So what I want to do is if the User selects Autoshape 1 (shp1) which displays the text Ice Cream, range("E6") on the Navigation spreadsheet shows the sum of all the ice cream units sold which will be retrieved from the productsT table from the Products spreadsheet.

So I need help with an If then statement or a Select case statement:

Depending on the shape the user selects on the Navigation spreadsheet range("E6") shows a different sum. The problem is what is the event needed to describe this since I've tried

If shp1.activate = true then and its told me the method does not exist for this object.

Also once the user has selected the shape, I'm having trouble with accessing the sumif function via VBA:

Since basically if the user selects shape 2 then the

Dim Result1 as variant
Result1 =  Application.WorksheetFunction.Sumif(ListObjects("productsT").ListColumns(1),"Chocolate  Cake",ListObjects("productsT").ListColumns(2))

syntax is not working either.

Range("E6") = Result1

So how do I access the table columns for the sumif function via VBA as well.


Thanks