How to run a VB script within a protected sheet

[James Norton]

I have put together a sheet with the exceptional help of William from this
newsgroup. A lot has been accomplished and there is a VB script William
wrote for me (located below) that work perfectly when the sheet is
unprotected. Problem is that when I unprotect the sheet it returns a Visual
Basic error message "400".

The script searches for a jpg image and places it in a cell on the page. I
have even unprotected those cells where the images will be located but I get
the same error.

Any assistance would be most appreciated.

Regards,

James Norton




Sub test()
Application.ScreenUpdating = False
Dim i As Integer, p As Picture, r As Range, c As Range, ii As Integer
ii = 1
Set r = ActiveSheet.Range("G5:G14")
ActiveSheet.DrawingObjects.Delete
For Each c In r
ii = ii + 1
If c <> "" Then
With Application.FileSearch
..NewSearch
..LookIn = "c:\drugpics"
..SearchSubFolders = False
..Filename = "*" & c & ".jpg"
..Execute
For i = 1 To .FoundFiles.Count
With ActiveSheet
Set p = .Pictures.Insert(Application.FileSearch.FoundFiles(i))
..DrawingObjects(p.Name).Left = .Columns(ii).Left
..DrawingObjects(p.Name).Top = .Rows(16).Top
..DrawingObjects(p.Name).Width = .Columns(ii + 1).Left - .Columns(ii).Left
..DrawingObjects(p.Name).Height = .Rows(17).Top - .Rows(16).Top
..DrawingObjects(p.Name).Placement = xlMoveAndSize
..DrawingObjects(p.Name).PrintObject = True
End With
Exit For
Next i
End With
End If
Next c
Application.ScreenUpdating = True
End Sub

 

[Paul B]

James, here is one way

ActiveSheet.Unprotect
'your code here
ActiveSheet.Protect


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 2003
** remove news from my email address to reply by email **