Thread: Counting Autoshapes

Hi,

I am doing some process mapping on excel using the flowchart symbols in the Autoshapes menu. Each symbol represents a different type of process e.g. square represents an automatic process, circle represents a manual process, triangle represents storage, etc. It would be great to summarize my process in a table which counts how many squares, circles, triangles, etc I have used in the map on the worksheet.

Is it possible to set up a code that can sum or count how many of a specific autoshape there are in a worksheet? Alternatively, if I set up the symbols to appear upon the click of a button (using macros)... is there a way visual basic can count how many times I press that particular button, hence giving me the same result?

Any advice toward a solution is greatly appreciated!

Stuart

Could use this

Dim Shp As Object
Dim x As Long
For Each Shp In ActiveSheet.Shapes
    If Shp.Name Like "*Rectangle*" Then
        x = x + 1
    End If
Next
    MsgBox x

or

Dim Shp As Object
Dim x As Long, y As Long
For Each Shp In ActiveSheet.Shapes
    Select Case True
    Case Shp.Name Like "*Rectangle*"
        x = x + 1
     Case Shp.Name Like "*Oval*"
        y = y + 1
    End Select
Next
    MsgBox x
    MsgBox y

VBA Noob

Thanks!!

I got close, but didn't quite manage to get it working.. I have attached the file I'm working on to show exactly what I'm trying to do if that helps.

To give you an example of the problem, I put "msoShapeFlowchartProcess" in the quotation marks in order to count the rectangles (or 'Flowchart: Process' boxes) from autoshapes however it didn't seem to pick it up. Am I referring to these incorrectly?

Any further help is very VERY much appreciated.

Cheers.

See if this helps

Dim Sh As Object
With ActiveSheet
   For Each Sh In .Shapes
       If Sh.AutoShapeType = msoShapeFlowchartProcess Then
             x = x + 1
       End If
    Next Sh
End With
    MsgBox x

VBA Noob

Thats great! I still have two minor issues to iron out if possible:

1. The count appears when I program it into a command button via a prompt window... is there anyway of getting this number to appear in a cell so when I run the code, the count appears for each type of shape in seperate cells. If this is possible in a cell or other message box / text box, how would I get it to show?

2. I actually have two of each of the shapes in a legend key that I do not want to count as part of the analysis - is there anyway I can change the formula to always discount these two? I tried x = (x-2) + 2; x = x + 1 - 2; etc however these didnt' work.

Thanks so much for the help so far - I am very grateful.