Macro doesn't insert image when spreadsheet is protected

[ATang]

Hello, my name is ATang. I have put in the below codes in an excel file in
order to allow the users to select a company name in a particular cell, then
the respective logo will automatically pop up in another cell. It works well
when the spreadsheet is NOT protected. If the spreadsheet is protected, the
logo stays the same and the logic doesn't work even I change the company
name. Have tried to unlock this cell which contains the logo, protect the
worksheet and change the company name again, the correct logo still doesn't
pop up. Is there a way to make this macro work by protecting the worksheet
at the same time? Appreciate your advice. Below captures the code details:

********************************************
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$6" Then
Application.EnableEvents = False
HandleBMP
Application.EnableEvents = True
End If
End Sub

Sub HandleBMP()
Dim myCell As Range
Set myCell = Selection

On Error Resume Next

If Range("G6").Value = "Vendor" Then
ActiveSheet.Shapes("B1 Picture").Delete
ActiveSheet.Shapes("A1 Picture").Delete
Range("A1").Select
ActiveSheet.Pictures.Insert( _
"P:\Merchandising\GSL Logo Picture.bmp").Select
Selection.Name = "A1 Picture"
Else

ActiveSheet.Shapes("A1 Picture").Delete
End If

If Range("G6").Value = "Customer" Then
ActiveSheet.Shapes("B1 Picture").Delete
ActiveSheet.Shapes("A1 Picture").Delete
Range("A1").Select
ActiveSheet.Pictures.Insert( _
"P:\Merchandising\Solito Logo Picture.bmp").Select
Selection.Name = "B1 Picture"
Else

ActiveSheet.Shapes("B1 Picture").Delete
End If

myCell.Select
End Sub
********************************************

Thank you very much,
ATang

 

[Allllen]

Hi ATang,

Your code seems good so I won't bother to correct anything in there.

If you didn't have that statement "On Error Resume Next" I believe you would
see an error coming up when the sheet is protected and you try to add and
delete the pictures. (Try it without that statement and you will see an
error text).

The tip that I think you really need to move forwards is this:

sheets("sheetname").protect password:="fish"
'some code here
sheets("sheetname").unprotect password:="fish"

Hope that helps! Please rate me. Thanks!

 

[ATang]

Hi Allllen,
Thank you for your quick reply. Yes, you are right, by taking out the "On
Error Resume Next", I was prompted with error message when trying to protect
the file. But, I'm really not good at coding in Excel, to get around this
problem, can you kindly advise exactly how and where should I insert your
suggested 2 statements into the below paragraph in order to achieve the
mentioned mission???

sheets("sheetname").protect password:="fish"
'some code here
sheets("sheetname").unprotect password:="fish"

Really appreciate your help in advance!!!
Regards, ATang