Macro protected worksheet with objects

[Gambit12]

Hey all,
I am looking to understand how I can protect a worksheet while still having the option for the end user to click embedded objects such as PDFs within a given worksheet.
I do not want the objects to be moved or deleted, just clickable to open.

Appreciate the help!

 

[6StringJazzer]

Is your embedded object an icon? Right-click on the icon, select Format Object, Protection, and uncheck the Locked checkbox. The protect the sheet. You will be able to click on the icon to open the object.

 

[Gambit12]

Is your embedded object an icon? Right-click on the icon, select Format Object, Protection, and uncheck the Locked checkbox. The protect the sheet. You will be able to click on the icon to open the object.

View attachment 123989

Hey Jazzer,
Thanks for the reply! Yes, my object is an Icon. That does work for letting it be opened when protected. Unfortunately, the object can still be moved/deleted. Any thoughts on how to prevent the object from being deleted or moved?

 

[6StringJazzer]

I don't think that granularity is there, but I could be wrong. I could not find a way to allow clicking on the icon to open the file, yet prevent moving or deleting it. It seems that lock/unlock covers everything.

Is the file an external file, or embedded in the Excel file? Another possibility might be to use a hyperlink instead of an icon, but I have not tested that idea.

 

[Gambit12]

I don't think that granularity is there, but I could be wrong. I could not find a way to allow clicking on the icon to open the file, yet prevent moving or deleting it. It seems that lock/unlock covers everything.

Is the file an external file, or embedded in the Excel file? Another possibility might be to use a hyperlink instead of an icon, but I have not tested that idea.

The file is an embedded file in the excel file. A link with a macro might work but that would have to be setup per attachment and wont be useable for the end user in my case.
The sub user will be uploading the files using a macro button, I am trying to set it up so the sub user doesn't have to do anything complex. The end user would be the one just clicking the object.

 

[6StringJazzer]

Looks to me like the locking is an all-or-nothing proposition. My only idea is a button that uses VBA to open the file. But if you are using VBA to load the files in the first place, this would have to be dynamic. I have not done this before.

 

[Gambit12]

Here is the file upload code I found on the internet if it helps.

Sub InsertFilesAsIcons()
'lets user browse for a file to insert into the
'current active worksheet.
'all are inserted as icons, not "visible" objects, so
'to view they will need an appropriate viewer/reader
'at the recipient end.
'
'Always uses same 'generic' IE icon
'could fail if that icon file is not on source/destination system
' I have set up constant to point to the icon file, so you could
' record macros while inserting various objects and view code
' generated to choose an icon you prefer.
' The iconToUse I chose came out of the x86 folder on a
' Windows 7 x64 system
'
  Const iconToUse = "C:\Program Files (x86)\Internet Explorer\iexplore.exe"
  Dim fullFileName As String

  fullFileName = Application.GetOpenFilename("*.*, All Files", , , , False)
 
  If fullFileName = "False" Then
    Exit Sub ' user cancelled
  End If

  ActiveSheet.OLEObjects.Add(Filename:=fullFileName, Link:= _
    False, DisplayAsIcon:=True, IconFileName:= _
    iconToUse, IconIndex:=0, IconLabel:=fullFileName).Select
End Sub