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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,743
Messages
5,524,569
Members
409,587
Latest member
ChrisF1980

This Week's Hot Topics

Top