Data validation list crashes VBA macro

[crazybumpbaby]

I have a VBA macro for Excel 2007 below that loops through a workbook and deletes a picture (shape) in a range at the top of each worksheet.

The macro works fine until a cell which contains a seemingly unrelated data validation list on Sheets(1) is changed. The macro then repeatedly trips up with a 'Run-time error 1004 - Application defined or object defined error'. Am somewhat out of ideas...

The cell with the data validation is outside of the range in which the shapes are deleted and does not set any of the variables in the macro.

Many thanks in advance for any advice.



Sub DeleteLogos()
Dim Count As Integer
Dim NumberOfWorksheets As Integer
Dim Logo As Shape
Dim LogoZone As Range

NumberOfWorksheets = Worksheets.Count
For Count = 1 To NumberOfWorksheets
With Sheets(Count)
Set LogoZone = .Range("A1:J7")
For Each Logo In .Shapes
If Not Application.Intersect(Logo.TopLeftCell, LogoZone) Is Nothing Then
If Logo.Type = msoPicture Then Logo.Delete
End If
Next Logo
End With
Next Count
End Sub

 

[Trevor G]

Welcome to the forum,

Without seeing the workbook I might be suggesting the wrong thing. But have you tried adding an error option such as On Error Resume Next, which can be added near the top of your code.

 

[CharlesChuckieCharles]

if you can step through the code when it errors that would be helpful

(controls are also considered to be shapes, but your code is very explicit)

Is there any more code in the spreadsheet because the error maybe emanating from other code!

 

[crazybumpbaby]

Trevor, thank you for the advice - the error handling irons out the problem, although I am still at a loss why this happens.

 

[crazybumpbaby]

Charles, thanks for the reply.

You could be right in suggesting that other code is affecting the macro above. There is one other macro that uses the data validation list on the first sheet to then insert logos on a title page (Sheets(1)) and then a different sized logo on further sheets (code below). The Name range 'Consultant' is the data validaton list on Sheets(1) in question.

Roger's suggestion above has got this working now, but as a learning experience it would of course be good to know why this happened.


Sub InsertLogos1()
Dim Calcsheet As Worksheet
Dim Count As Integer
Dim NumberOfWorksheets As Integer
Dim CalcSheetPath As String
Dim TitleSheetPath As String

CalcSheetPath = WorksheetFunction.VLookup(Range("Consultant"), Range("LogoPaths"), 2, False)
TitleSheetPath = WorksheetFunction.VLookup(Range("Consultant"), Range("LogoPaths"), 4, False)

' Inserts new logos into data sheet
Sheets(1).Shapes.AddPicture TitleSheetPath _
, False, True, 4, 3, 480, 80

' Inserts new logos into calcsheets
NumberOfWorksheets = (Worksheets.Count) - 4
For Count = 2 To NumberOfWorksheets
Set Calcsheet = Sheets(Count)
Calcsheet.Shapes.AddPicture CalcSheetPath _
, False, True, 8, 5, 200, 110
Next Count
End Sub