+ Reply to Thread
Results 1 to 10 of 10

VBA to allow users to delete specific rows with locked cells on protected sheets

  1. #1
    Registered User
    Join Date
    11-05-2014
    Location
    Atlanta, GA
    MS-Off Ver
    365
    Posts
    6

    VBA to allow users to delete specific rows with locked cells on protected sheets

    Hello,

    I have a workbook with multiple worksheets and I need to allow the users to delete rows 39-53 but the issue is several of the cells in these rows are locked thus cannot be deleted. I have tried a couple of different codes but to no avail. Is there a code that would allow this?

    Thank you!
    JT

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: VBA to allow users to delete specific rows with locked cells on protected sheets

    Hi there,

    You can temporarily remove the worksheet protection while the required rows are being deleted by using the following code:

    Please Login or Register  to view this content.
    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: VBA to allow users to delete specific rows with locked cells on protected sheets

    Hi again Jill,

    Thanks for the Private Message you sent me.

    I've attached a workbook which contains the following code:

    Please Login or Register  to view this content.
    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-05-2014
    Location
    Atlanta, GA
    MS-Off Ver
    365
    Posts
    6

    Re: VBA to allow users to delete specific rows with locked cells on protected sheets

    Quote Originally Posted by Greg M View Post
    Hi again Jill,

    Thanks for the Private Message you sent me.

    I've attached a workbook which contains the following code:

    Please Login or Register  to view this content.
    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    THIS WORKED!! THANK YOU SO MUCH, GREG!!

    One last question, would it be possible to have one form button that will delete rows 39-53 in all worksheets in the workbook and not just the active worksheet? I will send you a PM about this and mark original post as solved.

    Thanks so much!!
    JT

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: VBA to allow users to delete specific rows with locked cells on protected sheets

    Hi again Jill,

    Thanks for your Private Message re using a single button to perform the above actions on each worksheet within the workbook.

    This can certainly be done, but how you assign worksheet passwords will determine how complicated the code will be. Your PM suggests that you allow Users to select their own passwords for the worksheets. If this is the case, then either the Users will need to enter their password each time the CommandButton is pressed (probably not what you have in mind), or else the password initially entered by the User will need to be stored (and hidden!) somewhere within the workbook.

    If you intend to have an overall "Master Password" (assigned by an Administrator) for the worksheets, the process becomes simpler.

    Please let me know which of the above alternatives you would prefer, or suggest a different approach if appropriate.

    To allow selection of Locked as well as Unlocked cells, and to allow formatting of Rows, Columns and individual Cells you should modify your code as follows:

    Please Login or Register  to view this content.
    Regards,

    Greg M

  6. #6
    Registered User
    Join Date
    11-05-2014
    Location
    Atlanta, GA
    MS-Off Ver
    365
    Posts
    6

    Re: VBA to allow users to delete specific rows with locked cells on protected sheets

    Hi Greg,

    I apologize for the delay, I had to put this project aside and work on another one but now I am back at it! The above code works perfectly! Now for the general button that will delete the specific rows in all worksheets when clicked not just the active sheet. I have a master password so hopefully this makes it easier. Please let me know if you can assist. I appreciate your help and again sorry for the delay!

    Thanks!

  7. #7
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: VBA to allow users to delete specific rows with locked cells on protected sheets

    Hi again Jill,

    Glad you made it back here!

    Just read your post - it's 1:15am at this end so I won't be able to send you anything tonight, but I should be able to do something for you tomorrow.

    Hope Christmas & New Year went well for you

    Regards,

    Greg M

  8. #8
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: VBA to allow users to delete specific rows with locked cells on protected sheets

    Hi Jill,

    I think the following code should delete the specified rows on each worksheet in the workbook:

    Please Login or Register  to view this content.
    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Last edited by Greg M; 01-07-2015 at 06:09 AM. Reason: Minor change

  9. #9
    Registered User
    Join Date
    11-05-2014
    Location
    Atlanta, GA
    MS-Off Ver
    365
    Posts
    6

    Re: VBA to allow users to delete specific rows with locked cells on protected sheets

    That worked!! You are the best!! Thank you so much!!

  10. #10
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: VBA to allow users to delete specific rows with locked cells on protected sheets

    Hi again Jill,

    Many thanks for your feedback - I'm glad I was able to help.

    Best regards,

    Greg M

+ 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] In a protected worksheet allow users to delete rows
    By Ian in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 07:05 AM
  2. [SOLVED] In a protected worksheet allow users to delete rows
    By Jason Trivett in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  3. [SOLVED] In a protected worksheet allow users to delete rows
    By Jason Trivett in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  4. [SOLVED] In a protected worksheet allow users to delete rows
    By Jason Trivett in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  5. [SOLVED] In a protected worksheet allow users to delete rows
    By Jason Trivett in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-12-2005, 05:05 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