Application.Volatile with shapes? 2

[N1GHTEYES]

I have a UDF which returns the number of "shape" objects on its parent worksheet. I want it to keep track of the number and change its output if shapes are added or deleted. Therefore I've included Application.Volatile to cause it to update if the number changes, but it does not work.

I suppose it is because adding a shape does not cause any recalculation. Does anyone have any suggestions as to how to get the desired effect in this case?

I could start changing the spreadsheet to actively keep track of the shapes, but I'd prefer to keep the code in the UDF if at all possible.

 

[SkipVought]

Hi,

My crystal ball is a tad cloudy. Could you spring on the code?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[N1GHTEYES]

Some problem with the code may be the issue, but I suspect it is more a case of Application.Volatile not picking up changes to the worksheet's shapes collection.

However, just to be sure, here is the code...

Public Function AL_CountShapes(Optional shapetype As Variant) As Long
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This function returns the number of Shape objects on the worksheet from which the function was called.
'If shapetype is specified, it returns only the count of that type of shape.
'
'INPUTS:
'Optional shapetype as variant - the type of shape to be counted
'
'Shape type. Can be one of the following MsoShapeType constants:
'msoAutoShape, msoCallout, msoChart, msoComment, msoEmbeddedOLEObject,
'msoFormControl, msoFreeform, msoGroup, msoLine, msoLinkedOLEObject,
'msoLinkedPicture, msoMedia, msoOLEControlObject, msoPicture, msoPlaceholder,
'msoShapeTypeMixed, or msoTextEffect.
'Read-only Long. In Microsoft Excel, this property cannot be msoMedia or msoPlaceholder
'(these constants are used with shapes in other Microsoft Office applications).
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Application.Volatile
If IsMissing(shapetype) Then
    AL_CountShapes = Application.Caller.Worksheet.Shapes.count
Else
    Dim cnt As Long
    If Application.Caller.Worksheet.Shapes.count > 0 Then
        Dim x As Shape
        For Each x In Application.Caller.Worksheet.Shapes
            If x.Type = shapetype Then cnt = cnt + 1
        Next x
    Else
        cnt = 0
    End If
    AL_CountShapes = cnt
End If
        
End Function

 

[N1GHTEYES]

I don't know if it makes any difference, but I ought to have mentioned I was running Office 2K under win2k.

Tony

 

[SkipVought]

Like you stated, there's no event associated with addding or deleting shapes.

F8.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[macropod]

Hi Nighteyes,

If you work on the principle that any UI-based change to the shapes on a worksheet will be associated with a change to the parent cell or to the current selection, you could force the Worksheet_Change and Worksheet_SelectionChange events to fire your UDF.

Cheers

[MS MVP - Word]

 

[combo]

Worksheet_Change and Worksheet_SelectionChange refer to changes in cells, do not see the drawing layer. If you accept some delay, the SelectionChange event can fire when the user leaves the drawing layer and select cell(s).
You could try to pick the drawing layer state with timer, but this causes annoying effect of blinking cursor.

combo

 

[N1GHTEYES]

Thanks everyone for the comments. Combo's suggestion seems like the optimum route, but, as I have about 50 sheets in this workbook, does anyone know of a quick way of adding code to 50 worksheet event procedures?

I had been using the UDF partly to avoid having to put code in each book - sigh.

Tony

 

[macropod]

Hi Nighteyes,

There is a workbook-level version of the Change and SelectionChange events. Using those should obviate the need to add the code to all 50 sheets.

Cheers

[MS MVP - Word]

 

[combo]

With the above tip:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Calculate
End Sub

combo

 

[N1GHTEYES]

Doh! I knew that. Thanks for reminding me.

The slight grinding sound you hear is that of my brain being put into gear.

I just tried it, to make sure that calculate caused the shape-counting UDF to actually re-calculate its answer, and yes - it does. So thanks again. Prob solved.

Tony

p.s. I'm not sure how I got a star out of all that.

 

[N1GHTEYES]

Pesky users! One of my colleagues, who uses an add-in into which I had inserted the UDF which started this thread, has asked if there is any way of modding the UDF itself so that it really does pick up on the change to the shapes collection in a truly "volatile" fashion, so that he can use this function without having to write code for his sheets.

To clarify:
My UDF displays the number of shapes on its parent sheet. When pasted / created it works fine.
If I subsequently add (or delete) a shape, the function output does NOT change.
I have set Application.Volatile in the function code, but, because changing the number of shapes does not cause a sheet recalculation, it is not triggered.

So... can anyone think of any code I can put IN THE FUNCTION ITSELF which will cause an update to be triggered when the number of shapes changes?