Solved If my drop down menu consists of shapes, can I sum them up?

June 18, 2018 at 21:44:05
Specs: Windows 10
What I mean is, I have three shapes, a triangle (A1), a circle (A2), and a rectangle (A3). In the B column, I have drop down menus consisting of these three choices.
What I want to do is tally up all the triangles, circles, and rectangles at the end of the column.
How can I do it?

See More: If my drop down menu consists of shapes, can I sum them up?

Report •

June 19, 2018 at 04:02:45
See the 2nd response at the link below, then come back with any questions:


How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03

Report •

June 19, 2018 at 08:41:27
What version of Excel are you using?

Excel 2013 introduced the function UNICHAR() and UNICODE()

The function UNICODE() works by returning a character's Unicode value.
the function UNICHAR() which returns the character of the Unicode value given in decimal notation.

Using the UNICODE() function, you could probably do some type of count on the unicode value and get what your looking for.

I do not have 2013 so I have no way of checking.


message edited by mmcconaghy

Report •

June 19, 2018 at 11:05:46
✔ Best Answer
This code should work, but it is very dependent on the type of shapes that are in the sheet.

For example:

1 - There are a couple of types of rectangles, so I included them both just to be safe.
2 - There is no shape known as a "circle". There are only ovals, so the code assigns the string "Circle" to any shape that Excel calls an Oval. This occurs within the code only. It does not actually change the internal name of the shape as it is known by Excel.

Assume a Drop Down menu in B1 containing Triangle, Circle and Rectangle. The code will check each shape in the sheet to determine what type of shape it is. It then assigns one of those 3 names to the shape and if that "friendly name" matches the choice in the Drop Down, it counts the shape.

Once it has checked all shapes, it presents a count of the shape chosen in the Drop Down.

This is just an example of how it could be done. Is the count needs to be placed in a cell, that could be done. If the count needs to occur automatically as soon as the choice is made, that could be done too. There are lots of options, this code just shows the concept.

Let me know what you think.

Sub CountChosenShape()
Dim shp As Shape, Osp As String, c As Long

'Loop Through Shapes

 For Each shp In ActiveSheet.Shapes
'Ignore Drop Downs (They Are Shapes)
   If shp.Name Like "*Drop*" Then GoTo NoDrop
'Determine Shape Type
     Select Case shp.AutoShapeType
        Case Is = msoShapeIsoscelesTriangle: shpName = "Triangle"
        Case Is = msoShapeRightTriangle: shpName = "Triangle"
        Case Is = msoShapeRectangle: shpName = "Rectangle"
        Case Is = msoShapeRoundedRectangle: shpName = "Rectangle"
        Case Is = msoShapeOval: shpName = "Circle"
        Case Is = msoShapeOvalCallout: shpName = "Circle"
        Case Else: shpName = ""
     End Select
'Match Shape Type With Drop Down Choice

       If shpName Like "*" & Cells(1, 2) & "*" Then
         c = c + 1
       End If

   Next shp

'Display Count Of Chosen Shape

     MsgBox "I Found " & c & " " & Cells(1, 2) & "(s)"

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Report •
Related Solutions

Ask Question