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

**f41yaz** · 10-18-2018, 05:16 PM

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.

Sub Macro3()
'
' Macro3 Macro
'
Range("E3").Select
      Sheets("Form").Unprotect Password:="pass"
    Dim s As Shape, rng As Range
    Set rng = Range("O38:N38")

        For Each s In ActiveSheet.Shapes
            If Intersect(rng, s.TopLeftCell) Is Nothing Then
        Else
            s.Delete
        End If
    Next s
      Set rng = Range("AD27:AJ40")

        For Each s In ActiveSheet.Shapes
        If Intersect(rng, s.TopLeftCell) Is Nothing Then
        Else
            s.Delete
        End If
    Next s
    Sheets("Form").Protect DrawingObjects:=True, AllowFormattingColumns:=True, Contents:=True, Scenarios:=True, Password:="pass"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("U6:AA6").ClearContents
    Range("U8:AA8").ClearContents
    Range("U10:AA10") = "[ Please select from list ]"
    Range("U11:AA12").ClearContents
    Range("U14:AA14").ClearContents
    Range("U16:AA16").ClearContents
    Range("C21:N26").ClearContents
    Range("C29:AA32").ClearContents
    Range("G34:Q34").ClearContents
    Range("Y34:AA34").ClearContents
    Range("Y38:AA38").ClearContents
    Range("G38:L38").ClearContents
    Range("G39:L39").ClearContents
    Range("Q21") = "Y"
    Range("Q22") = "Y"
    Range("Q23") = "Y"
    Range("Q24") = "Y"
    Range("Q25") = "Y"
    Range("Q26") = "Y"
    Range("V21:W21") = "[ Select ] "
    Range("V22:W22") = "[ Select ] "
    Range("V23:W23") = "[ Select ] "
    Range("V24:W24") = "[ Select ] "
    Range("V25:W25") = "[ Select ] "
    Range("V26:W26") = "[ Select ] "
    Range("X21:Z21") = "[ Select or Enter ]"
    Range("X22:Z22") = "[ Select or Enter ]"
    Range("X23:Z23") = "[ Select or Enter ]"
    Range("X24:Z24") = "[ Select or Enter ]"
    Range("X25:Z25") = "[ Select or Enter ]"
    Range("X26:Z26") = "[ Select or Enter ]"
    Range("AA21") = "[ Select or Enter ]"
    Range("AA22") = "[ Select or Enter ]"
    Range("AA23") = "[ Select or Enter ]"
    Range("AA24") = "[ Select or Enter ]"
    Range("AA25") = "[ Select or Enter ]"
    Range("AA26") = "[ Select or Enter ]"
    Range("AF34") = "FALSE"
    Range("AF39") = "FALSE"
    Range("AF40") = "FALSE"
    Range("AF41") = "FALSE"
    Range("V27") = "£ GBP"
    ActiveWindow.SmallScroll Down:=-42
    Range("E3").Select
    ActiveCell.FormulaR1C1 = ""
 
Range("C21:I21").Select

End Sub

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

If Intersect(rng, s.TopLeftCell) Is Nothing Then

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

Thanks

**6StringJazzer** · 10-18-2018, 05:52 PM

What is the error message shown when the debug message comes up?

What module contains this code?

How is the code invoked?

The very first line of code selects a cell so it should not matter if a cell is selected before the code runs.

I can't tell what's wrong from the code, please consider attaching the file with private data removed.

**f41yaz** · 10-18-2018, 05:58 PM

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.

**Norie** · 10-18-2018, 06:17 PM

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

  Set rng = Range("AD27:AJ40")

**f41yaz** · 10-18-2018, 06:27 PM

Originally Posted by Norie

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

  Set rng = Range("AD27:AJ40")

The same sheet.

**f41yaz** · 10-18-2018, 06:22 PM

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

**6StringJazzer** · 10-18-2018, 07:01 PM

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.

**Norie** · 10-18-2018, 06:47 PM

Every unqualified range reference will refer to the active sheet.

**f41yaz** · 10-18-2018, 06:57 PM

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

**f41yaz** · 10-18-2018, 07:03 PM

pass is "1213"

**6StringJazzer** · 10-18-2018, 08:24 PM

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.

**f41yaz** · 10-19-2018, 04:19 AM

Thanks for trying mate. Look at this...

https://www.mrexcel.com/forum/excel-...rop-downs.html

https://social.msdn.microsoft.com/Fo...s?forum=isvvba

it might help?

Or

What about a code to delete all shapes except the ones in the sheet currently. Would this solve the problem? by deleting all the additional inserted images i.e. Picture 12 $N$38

**f41yaz** · 10-19-2018, 06:13 AM

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

Sub Macro3()
'
' Macro3 Macro
'
      Sheets("Form").Unprotect Password:="pass"
    Call Macro5
    Call Macro6
    Sheets("Form").Protect DrawingObjects:=True, AllowFormattingColumns:=True, Contents:=True, Scenarios:=True, Password:="pass"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("U6:AA6").ClearContents
    Range("U8:AA8").ClearContents
    Range("U10:AA10") = "[ Please select from list ]"
    Range("U11:AA12").ClearContents
    Range("U14:AA14").ClearContents
    Range("U16:AA16").ClearContents
    Range("C21:N26").ClearContents
    Range("C29:AA32").ClearContents
    Range("G34:Q34").ClearContents
    Range("Y34:AA34").ClearContents
    Range("Y38:AA38").ClearContents
    Range("G38:L38").ClearContents
    Range("G39:L39").ClearContents
    Range("Q21") = "Y"
    Range("Q22") = "Y"
    Range("Q23") = "Y"
    Range("Q24") = "Y"
    Range("Q25") = "Y"
    Range("Q26") = "Y"
    Range("V21:W21") = "[ Select ] "
    Range("V22:W22") = "[ Select ] "
    Range("V23:W23") = "[ Select ] "
    Range("V24:W24") = "[ Select ] "
    Range("V25:W25") = "[ Select ] "
    Range("V26:W26") = "[ Select ] "
    Range("X21:Z21") = "[ Select or Enter ]"
    Range("X22:Z22") = "[ Select or Enter ]"
    Range("X23:Z23") = "[ Select or Enter ]"
    Range("X24:Z24") = "[ Select or Enter ]"
    Range("X25:Z25") = "[ Select or Enter ]"
    Range("X26:Z26") = "[ Select or Enter ]"
    Range("AA21") = "[ Select or Enter ]"
    Range("AA22") = "[ Select or Enter ]"
    Range("AA23") = "[ Select or Enter ]"
    Range("AA24") = "[ Select or Enter ]"
    Range("AA25") = "[ Select or Enter ]"
    Range("AA26") = "[ Select or Enter ]"
    Range("AF34") = "FALSE"
    Range("AF39") = "FALSE"
    Range("AF40") = "FALSE"
    Range("AF41") = "FALSE"
    Range("V27") = "£ GBP"
    ActiveWindow.SmallScroll Down:=-42
    Range("E3").Select
    ActiveCell.FormulaR1C1 = ""
 
Range("C21:I21").Select

End Sub

Macro 5 =

Sub Macro5()
    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("N38:N38")) Is Nothing Then
                shp.Delete
            End If
        End If
    Next shp
End Sub

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

**romperstomper** · 10-19-2018, 06:26 AM

You don't really need two loops, and if you refer to DrawingObjects rather than Shapes, you don't have to worry about the DV dropdowns:

    Dim s As Object, rng As Range
    Set rng = Range("N38:N38,AD27:AJ40")
        For Each s In ActiveSheet.DrawingObjects
            If Not Intersect(rng, s.TopLeftCell) Is Nothing Then
            s.Delete
        End If
    Next s

**f41yaz** · 10-19-2018, 07:23 AM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

Re: VBA - Debug message pops up if Macro is used after a cell is selected, otherwise it wo

Re: VBA - Debug message pops up if Macro is used after a cell is selected, otherwise it wo

Re: VBA - Debug message pops up if Macro is used after a cell is selected, otherwise it wo

Re: VBA - Debug message pops up if Macro is used after a cell is selected, otherwise it wo

Re: VBA - Debug message pops up if Macro is used after a cell is selected, otherwise it wo

Re: VBA - Debug message pops up if Macro is used after a cell is selected, otherwise it wo

Re: VBA - Debug message pops up if Macro is used after a cell is selected, otherwise it wo

Re: VBA - Debug message pops up if Macro is used after a cell is selected, otherwise it wo

Re: VBA - Debug message pops up if Macro is used after a cell is selected, otherwise it wo

Re: VBA - Debug message pops up if Macro is used after a cell is selected, otherwise it wo

Re: VBA - Debug message pops up if Macro is used after a cell is selected, otherwise it wo

Re: VBA - Debug message pops up if Macro is used after a cell is selected, otherwise it wo

Re: VBA - Debug message pops up if Macro is used after a cell is selected, otherwise it wo

Re: VBA - Debug message pops up if Macro is used after a cell is selected, otherwise it wo

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] MAcro works OK in Step into debug mode but not doing job when run from command button

FindNext works when called from macro in debug but not from a cell formula

[SOLVED] Macro skipping If statements when run but works in debug

Macro needed to change cell color that only works if cell in range is selected first

[SOLVED] Macro Works but not in Debug Step mode

[SOLVED] Macro sends Excel file by Outlook - works in debug - not realtime

Bookmarks

Bookmarks

Posting Permissions