Showing images in a locked spreadsheet

[paulxl]

I have a spreadsheet that displays an image for a result using this event macro I found on McGimpsey & Associates website:

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("b11")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

This works great unless I protect the worksheet and then I get the following error message:

"Run time error '1004':

unable to set the Top property of the Picture class"

I suspect there would be a problem with the Left property too, if it ever got that far.

I need to protect the sheet because I want to let others (with no excel capabilities) use the sheet without modifying it>

 

[sykes]

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("b11")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
Me.unprotect "Password"
oPic.Top = .Top
Pic.Left = .Left
ooPic.Visible = True
Me.protect "Password"
Exit For
End If
Next oPic
End With
End Sub

Omit the password bit if your sheet's not password protected, and replace the text "password" with the real password, if it is. Also, if you do need to put the password in, best to protect your project from viewing as well, otherwise others could see the hard-coded password in your project.

 

[sykes]

Oh, and welcome to Mr Excel!
Sorry - didn't notice it was your first post here.

 

[paulxl]

Thank you. The simple solutions are always the best!

Nice ****!

 

[sykes]

Pleasure. Glad it worked for you, and thanks for the feedback.