VBA - Debug message pops up if Macro is used after a cell is selected, otherwise it works!

Hi All,

I created the following Macro code which is used to clear a form. The code starts off with unprocteting the sheet then checking if a certain cells have images if so it deletes the images followed by doing some other stuff to clear others cells that have information in it.

Please Login or Register  to view this content.

The macro seems to work when the form is intially opened and clicked on straight away, but if a cell is selected and then the Macro is used it doesn't like it. A debug message pops up and the following line is highlighted in yellow.:

Please Login or Register  to view this content.

As always thank you for all your help. I don't know what I would do without you guys.

Thanks

The message that comes up is Run-time error '1004', Application-defined or object-defined error. (suprisingly the image clears though)

Module 2 contains the Macro

Macro is assigned to another image.

I am just really confused what it might be.

Thank you for looking into this.

Hi Jeff,

Please find attached file with private data removed (I've sent you the Password via PM)

Notice image in Cell N38. When you open file and click on "Clear Form" (located AE1) then it works. But when you open file, click on Cell V21, then click on "Clear Form" as error pops up.

Thanks

Originally Posted by Norie

Which sheet(s) are the unqualified ranges like this meant to refer to?

Please Login or Register  to view this content.

The same sheet.

So that should be fine right. Did you manage to get a chance to look at the file.

Originally Posted by f41yaz

Hi Jeff,

Please find attached file with private data removed (I've sent you the Password via PM)s

I do not have a PM from you.

pass is "1213"

I don't have an answer yet but I have some diagnostics. I can reproduce your error if I select V26 before clicking "Clear Form". If I do not click on anything first, I get this list of shapes in your sheet:

TextBox 1 $AN$6
Picture 25 $L$35
Picture 28 $L$35
TextBox 2 $K$35
TextBox 3 $M$41
AutoShape 52 $I$5
Picture 22 $AD$2
Picture 26 $AA$19
Picture 27 $Y$19
Picture 50 $M$37
TextBox 4 $R$38
Picture 4096 $AH$6
Picture 4102 $AH$6
Picture 4104 $AI$6
Picture 12 $N$38

However, if I first select V26 then click Clear Form, then I get this shape in addition to the above:

Drop Down 1

I am able to reference s.Name but s.TopLeftCell causes an error. I have not heard of data validation dropdown boxes being identified as shapes, and I don't know why it is in the Shapes collection only if you select V26 first.

So a little data, but I am stuck.

I seemed to be able to slove it my doing the following:

Please Login or Register  to view this content.

Macro 5 =

Please Login or Register  to view this content.

Macro 6 =
Sub Macro6()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If Not shp.Name Like "Drop Down *" And Not shp.Name Like "Comment *" Then
If Not Application.Intersect(shp.TopLeftCell, Range("AD27:AJ40")) Is Nothing Then
shp.Delete
End If
End If
Next shp
End Sub

Do you guys seen any concerns doing it this way? it seems to work surprisingly.

Thanks

I will just leave it alone as its working but thanks for this!!