VB Macro problem using TopLeftCell

bscanlan

New Member
Joined
Sep 3, 2009
Messages
1
I have a macro that I'm using in Excel 2003... when a range of cells change the macro checks to see if a picture is assigned to that cell and deletes it and replaces the picture based on the new value. Below is the section of code that I'm using and that is no longer working with Excel 2007. I've searched the help in Excel 2007 and the TopLeftCell functionality seems to still exist but I'm getting a run-time error stating that the 'object doesn't support this property or method'

ActiveSheet.Shapes.SelectAll
For Each sh In Selection.ShapeRange
If sh.TopLeftCell.Address(False, False) = "N" + Trim(LTrim(Str(y))) Then
sh.Delete
End If
Next
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Firstly, you need to ensure that code in your message is formatted correctly - as I have with my code below. I had to magnify it to read it because the text was so small. Please use the [code and [/code tags (with the following bracket ] ). You were saved by the fact that there was not much code there. Generally badly formatted code does not get read if there is much more than a few lines. It is difficult enough to understand someone else's code even if we can read it.

Normal Excel practice shows that we do not delete a single shape if all shapes are selected. Usually when we use code Select is not necessary anyway.

TopLeftCell returns a Range object, so we can use it as is.

As your code is incomplete I cannot see if there are any other errors - so here is a partial working solution. Please note how much easier it is to read and understand.

Code:
'================================================
'- SHOW TOP LEFT CELL CONTENTS FOR EACH SHAPE IN A WORKSHEET
'================================================
Sub test()
    For Each sh In ActiveSheet.Shapes
        MsgBox (sh.TopLeftCell.Value)
    Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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