Running macros in 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

 

[Ron de Bruin]

Hi James

You can unprotect your sheet in the code
and protect it again after your code is ready.

But I like this way.

Protect your worksheets with code like this
Copy this in the Thisworkbook module.

The macro's will be working now
It will only protect for the userfaceonly

Private Sub Workbook_Open()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Protect "ABCD", , , userinterfaceonly:=True
Next sh
Application.ScreenUpdating = True
End Sub

 

[James Norton]

Hi Ron,

Thanks for your assistance, but I can't seem to get this working. I still
get the error 400 when I run the script even after I added this to the
Thisworkbook module.

Your help would be most appreciated.

Best regards,

James Norton