?
Solved

Why doesn't "i" Work with Pictures

Posted on 2012-08-16
3
Medium Priority
?
605 Views
Last Modified: 2012-08-16
I have a worksheet that has several "Pictures" or, Picture objects, on it that I would like to remove.

In running the Macro Recorder, it lists these objects as ActiveSheet.Shapes("Picture #") or
ActiveSheet.Shapes("Picture 1").Select

Open in new window

In putting together some code to delete these I used - This is the short version to my question:

Sub DeleteShapes()
Dim shp As Shape, i As integer
i = 1
   For Each shp In ActiveSheet.Shapes
     ActiveSheet.Shapes("Picture [b]i[/b]").Delete
    i = i + 1
   Next
i = 0
End Sub

Open in new window


That doesn't work though, it doesn't recognize the "i".  However, If I replace the i with a 1, then it will work.  I have tried changing i to Long, String, etc. Even though i = 1 is the right value, the code won't allow it to excecute, it has to be the actual number.  Why is this?  It defeats the whole purpose for variables.
0
Comment
Question by:Cook09
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 38301893
You can run this directly.

Sub DeleteShapes()
Dim shp As Shape
   For Each shp In ActiveSheet.Shapes
     shp.Delete
   Next
End Sub

Open in new window


For your code to work you'd need to write the following, otherwise your variable name is just considered as part of the string.
ActiveSheet.Shapes("Picture " & i ).Delete

Open in new window


Thomas
0
 

Author Closing Comment

by:Cook09
ID: 38302645
Thanks, your result worked better than what I had put together, but there was one oddity.  When I tried to run your code initially, I got an error that "Cannot Enter into Break Mode at this Time."  When I added the On Error Resume Next, then it worked fine.  That was the first time I've seen that error, have you seen it before?
0
 

Author Comment

by:Cook09
ID: 38302649
I think I may have figured out the error, there are some Merged Cells and it may have errored out on those.  That's the thought anyway.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question