+ Reply to Thread
Results 1 to 10 of 10

Protect Worksheet to prevent objects to be edited but allow users to insert objects?

  1. #1
    Registered User
    Join Date
    09-12-2018
    Location
    Leicester
    MS-Off Ver
    2007onwards
    Posts
    32

    Protect Worksheet to prevent objects to be edited but allow users to insert objects?

    Hi All,

    I hope you are well.

    I have a spreadsheet that I require the user to insert their photo in. However, I also have other images on the sheet that need to be protected.

    Is there anyway to have the worksheet protected to prevent edit/deletion of objects (i.e. the other images) but at the same time allow users to insert an image (i.e. their photo)?

    The problem I am having is that with worksheet protection and edit object ticked, the file lets users insert image, but also allows them to delete other images which I don't want. The apposite problem occurs (i.e. user can't edit /delete but can't also insert) when the edit object is unticked.


    Is there anyway around this?

    Thanks in advance for all your help guys.

  2. #2
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Protect Worksheet to prevent objects to be edited but allow users to insert objects?

    Good morning f41yaz

    Only thing I can think of is, protect the sheet, and build the code that allows users to insert their photo.

    You would have a button which would open a file selection dialog to choose the image, then behind the scenes, unprotect the file, insert the image, then re-protect the file.

    Bit fiddly, but pretty much the only way I can think of.

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  3. #3
    Registered User
    Join Date
    09-12-2018
    Location
    Leicester
    MS-Off Ver
    2007onwards
    Posts
    32
    Thanks Dominic for responding.

    The only problem with that is I will be exposing the password to the users; simply clicking on VBA code.
    Last edited by AliGW; 10-15-2018 at 09:32 AM. Reason: Unnecessary quotation removed.

  4. #4
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Protect Worksheet to prevent objects to be edited but allow users to insert objects?

    Hi f41yaz

    Granted, the solution's not ideal, but you don't have too many options here.

    Also, bear in mind that the VBE can be locked so that users cannot access it, hiding the code and the password (Tools > VBAProject Properties, Protection).

    HTH

    DominicB

  5. #5
    Registered User
    Join Date
    09-12-2018
    Location
    Leicester
    MS-Off Ver
    2007onwards
    Posts
    32

    Re: Protect Worksheet to prevent objects to be edited but allow users to insert objects?

    Ahhh...thanks I didn't know you could do that. I now just need to find the code to include in the following code to unlock the sheet with the password and lock it again after picture has been submitted.

    Please Login or Register  to view this content.
    Thanks
    Last edited by AliGW; 10-15-2018 at 09:32 AM. Reason: Unnecessary quotation removed.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,359

    Re: Protect Worksheet to prevent objects to be edited but allow users to insert objects?

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Protect Worksheet to prevent objects to be edited but allow users to insert objects?

    Hi f41yaz

    Something like this (just beware with the blue lines to change the sheetname / password) :

    Please Login or Register  to view this content.
    HTH

    DominicB

  8. #8
    Registered User
    Join Date
    09-12-2018
    Location
    Leicester
    MS-Off Ver
    2007onwards
    Posts
    32

    Re: Protect Worksheet to prevent objects to be edited but allow users to insert objects?

    Thanks that worked after some tweaking (I had to swap the lines to unprotect first then proctect)

    The only is that when they click on the button to insert the image, a box pops up for them to select the image file, however if they press cancel not to insert a image file then the code does not protect the sheet

  9. #9
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Protect Worksheet to prevent objects to be edited but allow users to insert objects?

    Hi f41yaz

    Move the line beginning "Sheets("Sheet1").Unprotect..." from its current place and put it directly after the line beginning "If strFileName = "False"..."
    That should sort it.

    HTH

    DominicB

  10. #10
    Registered User
    Join Date
    09-12-2018
    Location
    Leicester
    MS-Off Ver
    2007onwards
    Posts
    32

    Re: Protect Worksheet to prevent objects to be edited but allow users to insert objects?

    I only just seen your post now...I tried to edit it myself and it seems to be working. The following is the code:
    Please Login or Register  to view this content.
    However I noticed another problem ...Certain images seem to be inserted elsewhere not in the desired cells. This seems to only happen with portrait images. Not sure why.

    Thanks for all your help, much appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA to protect specfic objects on a worksheet
    By MissDB in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-06-2015, 06:45 AM
  2. [SOLVED] VBA to Protect certain shapes/objects
    By MissDB in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-09-2015, 06:44 AM
  3. How to hide a selection of objects but show objects automatically when opening wkbk
    By slowjo1414 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-05-2013, 05:24 PM
  4. Can I protect activex-objects?
    By Crüe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-01-2011, 01:09 AM
  5. Protect Sheet Allowing Users to Format Rows/Edit Objects?
    By Kristine T. in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-16-2009, 04:38 PM
  6. [SOLVED] Allow users to edit ranges for VBA objects
    By Jeff in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-29-2006, 06:10 PM
  7. Allow Insert of objects onto Protected Worksheet
    By ShaneBell in forum Excel General
    Replies: 1
    Last Post: 06-29-2006, 10:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1