ActiveSheet.Shape

leonlai

Board Regular
Joined
Mar 22, 2019
Messages
77
Hi, everybody

I have a macro which works perfectly:

Code:
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
Code:
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:



Code:
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
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi Leon

Try this to check if the shape exists:

Code:
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
 
Upvote 0
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)


Code:
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:

Code:
[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
 
Upvote 0
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.

Code:
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.
 
Upvote 0
It's the first time I use error handling to check if something exits.

Your explanations are great.

Thanks a lot

Leon
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top