Allow user to change instered picture on protected sheet

[Sven62]

Is there a way to allow users to place a new picture in place of an existing picture in a protected workbook/worksheet? I don't want them to be able to edit any other part of the sheet.

 

[Aaron Blood]

When you protect the sheet you could try putting a checkmark next to "Edit Objects"

 

[Sven62]

Yeah except there are many other objects on the sheet. Command buttons. Form buttons. Etc...

But Thanks for trying!

 

[Aaron Blood]

If you have command buttons on the sheet, then I would assume you have macros attached to them...

So if you are familiar with VBA, you could add a command button with a macro that swaps the picture. In which case, I would use VBA to protect the sheet and set the UserInterfaceOnly option of the Sheet.Protect method to True. This would allow your macro to swap the picture to function uninhibited on an otherwise completely protected worksheet.

 

[Sven62]

Thank you!

I know enough VBA to be dangerous. UserInterfaceOnly is set to true. But I don't know the code to create a button that lets me swap an existing photo on the worksheet for another photo on the computer. Can you help me with that?

 

[Aaron Blood]

Sure, do you need to maintain a certain size/location for the picture?

 

[Aaron Blood]

I'm gonna assume it's just OK to swap the picture and use the TopLeftCell property of the existing picture to place the new picture.

This code is actually pretty cool! I think I'm gonna keep it.

Sub SwapPic()
   

    Dim PicFileName As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .Show
        PicFileName = .SelectedItems(1)
    End With

    With ActiveSheet.Shapes(Application.Caller)
        .TopLeftCell.Select
        .Delete
    End With
       

    With ActiveSheet.Pictures.Insert(PicFileName)
        .Name = "UserPic"
        .OnAction = "SwapPic"
    End With
    

End Sub

1) Copy/paste the above code, exactly as-is, into a module code pane.
2) Right-click your picture that you want to make "swappable" and select "Assign Macro" (choose SwapPic of course!)
3) Click the picture to run the macro

Cool part is, it swaps out the picture and automagically reassigns the SwapPic macro to the new picture!
Now all the users have to do is click the pic to swap it out. Pretty nifty!

Notice that it does set the swapped picture name to the constant "UserPic" shape object name. If you want to assign this macro to multiple pics on the same sheet you would have to comment-out or delete that line of code so the default "Picture1", "Picture2", "Picture..." filename would be allowed to increment. I just set it as a constant in the event that you may want to refer to the picture in your code.

 

[Aaron Blood]

One adjustment, need to add a line of code to exit the subroutine if the user clicked the image by mistake and exits the file picker window without making a selection...

 Sub SwapPic()

  
    Dim PicFileName As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .Show
        On Error Resume Next
            PicFileName = .SelectedItems(1)
        On Error GoTo 0
    End With
    If PicFileName = "" Then Exit Sub

    With ActiveSheet.Shapes(Application.Caller)
        .TopLeftCell.Select
        .Delete
    End With
       
    With ActiveSheet.Pictures.Insert(PicFileName)
        .Name = "UserPic"
        .OnAction = "SwapPic"
    End With
    
End Sub

...and this assumes of course that you've already protected the worksheet and set the UserInterfaceOnly property to True.

 

[Sven62]

That is awesome! Is there any way to password protect that specific macro and not the others on the page?

 

[Sven62]

Nevermind. I figured it out. THANK YOU!