ActiveSheet.Shape

[leonlai]

Hi, everybody

I have a macro which works perfectly:

Sub Toggle_ListSelectedCmts()


Static Flag1 As Boolean


If Flag1 = True Then
Call CreateShape1_ListCommentsTextInShape1
Flag1 = False


ElseIf Flag1 = False Then
Call ClearShape1
Flag1 = True


End If

This code makes my button act like a toggle button.


To make the macro work, we select a range of Comments on the worksheet.
Then we click on the button: It creates a shape, and the texts of the comments are listed in the shape.

If we click on the button again, the shape is deleted.

Here is the code for Sub ClearShape1

Sub ClearShape1()


 On Error Resume Next
 ActiveSheet.Shapes("Shape1").Delete
End Sub

The code for Sub CreateShape1_ListCommentsTextInShape1 is more complicated.

The macro works perfectly.

There is only 1 small problem.

If we want to Create or Clear the shape, we must sometimes click twice (instead of once) on the Toggle Button.
This depends on the value Flag1 happens to have at that moment.

-----------

To avoid the problem, I want to stop using Flag1.

I want a macro which first checks if the Shape1 is displayed:

If No, it calls Sub CreateShape1_ListCommentsTextInShape1
If Yes, it calls Sub ClearShape1


How to do that?

I tried the following, but doesn't work:

Sub Toggle_ListSelectedCmts()
If ActiveSheet.Shapes("Shape1").Visible = False Then Call [I]CreateShape1_ListCommentsTextInShape1[/I]
If ActiveSheet.Shapes("Shape1").Visible = True Then Call [I]ClearShape1[/I]
End Sub

I am not sure "Visible" is the correct property to use.
I also tried = Nothing.

Can anybody help?



Thanks
Leon

 

[pgc01]

Hi Leon

Try this to check if the shape exists:

Sub ShapeExists()
Dim shp As Shape

On Error Resume Next
Set shp = ActiveSheet.Shapes("Shape 1")
On Error GoTo 0

MsgBox Not shp Is Nothing

End Sub

 

[leonlai]

Hi, pgc01

Thanks a lot for your code, and it works like a charm.

But I don't understand quite how it works:


Here is my complete code: (some names have been changed)

Sub Toggle_ListSelectedCmts()


Dim shp As Shape


[I][B]On Error Resume Next[/B][/I]


[I][B]Set shp = ActiveSheet.Shapes("Shape1")[/B][/I]


On Error GoTo 0


If Not shp Is Nothing Then Call ClearListOfSelectedCmts
If shp Is Nothing Then Call ListSelectedCmts


End Sub

I don't understand these 2 lines:

[I][B]On Error Resume Next                            '// LINE 1[/B][/I]


[I][B]Set shp = ActiveSheet.Shapes("Shape1")          '// LINE 2[/B][/I]

If I delete the 1st line, the macro crashes at the 2nd line. So, the 1st line seems indispensable for the macro to work correctly.

The 1st line says: If there is an error, skip the line which causes the error, and continue with the next line.

But why should the 2nd line cause an error?

Is is because there is no "Shape1" on the screen and therefore the macro crashes as it tries to set the variable "shp"?


Please confirm my mis/understanding.


nb: The macro does not crash if we disable: On Error GoTo 0
----

Is there a way to write the macro without using Error Handling?

Best Regards,
Leon

 

[pgc01]

Is is because there is no "Shape1" on the screen and therefore the macro crashes as it tries to set the variable "shp"?

Yes, that's it.

nb: The macro does not crash if we disable: On Error GoTo 0

Yes, but it's good practice to disable the error handling as soon as you don't need it.

Is there a way to write the macro without using Error Handling?

Well, I can think of 2 ways, but that depend on your knowledge of the specific worksheet.

1 . use the shapes count.

If you know that, for ex., there are no other shapes in the worksheet then just test the shapes count.

If ActiveSheet.Shapes.Count = 0 Then

2 . loop through all the shapes of the sheet and check the name.
This is also a simple method but may fail if you have duplicate names.
In this case you'd name your shape with a name that you know it's not used in any other shape like CommentListing or something like that.

 

[leonlai]

It's the first time I use error handling to check if something exits.

Your explanations are great.

Thanks a lot

Leon

 

[pgc01]

You're welcome. Thanks for the feedback.