+ Reply to Thread
Results 1 to 15 of 15

Excel 2013 - Protect Range of Cells without using Protect Sheet Button

  1. #1
    Registered User
    Join Date
    02-15-2012
    Location
    London, United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    69

    Thumbs up Excel 2013 - Protect Range of Cells without using Protect Sheet Button

    Hi All,

    I'm curious if anyone can assist with helping me.

    I have a worksheet that has a range of cells merged within it. When using the protect sheet button under the review tab, I can protect the whole sheet but can't deactivate certain cells for individuals to edit, due to merged cells. The lock cell button isn't selectable either. If anyone could help with creating a macro to complete this, that would be excellent.

    Under sheet 1 - Called Tom

    I require protecting all columns except F,H,I,J,K,L,N up to row 2681

    Under sheet 2 - Called John

    I require protecting all columns except F,H,I,J,K,L,N up to row 3392

    Under sheet 3 - Called Peter

    I require protecting all columns except F,H,I,J,K,L,N up to row 365

    Can anyone provide any support in me achieving this? I would upload the workbook but it is a quiet large file.

    Kind Regards,

    Caf20012

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Excel 2013 - Protect Range of Cells without using Protect Sheet Button

    You can use the worksheet selection change, but it will require enabling macros, so not sure how locked they will be.

    Ive just password protect sheet with a merged cell, and left the merged cell unlocked and it was fine.
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Registered User
    Join Date
    02-15-2012
    Location
    London, United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Excel 2013 - Protect Range of Cells without using Protect Sheet Button

    Hi,

    I just require protecting the all the sheets based on the information above, excluding those cells listed above.

    The sheet itself has over 30,000 calculations, so i want to avoid others manually changing it.

  4. #4
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Excel 2013 - Protect Range of Cells without using Protect Sheet Button

    Well use the inbuilt security, protect sheet, i used protect sheet and it was fine.

  5. #5
    Registered User
    Join Date
    02-15-2012
    Location
    London, United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Excel 2013 - Protect Range of Cells without using Protect Sheet Button

    Anyone else who can approach this from a different perceptive ?

  6. #6
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Excel 2013 - Protect Range of Cells without using Protect Sheet Button

    I dont understand why you say you cant lock merged cells?

  7. #7
    Registered User
    Join Date
    02-15-2012
    Location
    London, United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Excel 2013 - Protect Range of Cells without using Protect Sheet Button

    Hi Nathan,

    It just doesn't allow me due to either the amount of merged cells in various locations, or the 30,000 formula's or the large amount of content within the spreadsheet.

  8. #8
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Excel 2013 - Protect Range of Cells without using Protect Sheet Button

    Can you attach a sample? Just done 30K+ formula and merged cells.
    Last edited by nathansav; 09-17-2014 at 06:15 AM.

  9. #9
    Registered User
    Join Date
    02-15-2012
    Location
    London, United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Excel 2013 - Protect Range of Cells without using Protect Sheet Button

    Hi,

    Please find a sample attached. Costed Roster.zip

  10. #10
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Excel 2013 - Protect Range of Cells without using Protect Sheet Button

    Hi

    I see what you mean, but there is a solution. The merged cells in column A & B are ok, however the ones in column E arent. I copied the merged cell from A over and it allowed me to do it. Cant see why that would be, i've unmerged mergedback, it just doesnt like column E

    Try copying A to C, then do CTRL G F1:N2681 and turning locking off, then protecting sheet.

    I'll do some more digging on whats happening in column E

  11. #11
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Excel 2013 - Protect Range of Cells without using Protect Sheet Button

    Hi

    I see what you mean, but there is a solution. The merged cells in column A & B are ok, however the ones in column E arent. I copied the merged cell from A over and it allowed me to do it. Cant see why that would be, i've unmerged mergedback, it just doesnt like column E

    Try copying A to C, then do CTRL G F1:N2681 and turning locking off, then protecting sheet.

    I'll do some more digging on whats happening in column E

  12. #12
    Registered User
    Join Date
    02-15-2012
    Location
    London, United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Excel 2013 - Protect Range of Cells without using Protect Sheet Button

    Morning Nathansav,

    Any progression in your findings?

  13. #13
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Excel 2013 - Protect Range of Cells without using Protect Sheet Button

    Morning, not as yet. Bit of a nightmare at work to sort

  14. #14
    Registered User
    Join Date
    02-15-2012
    Location
    London, United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Excel 2013 - Protect Range of Cells without using Protect Sheet Button

    Anyone else able to assist?

    Kind Regards,

    Caf20012

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Excel 2013 - Protect Range of Cells without using Protect Sheet Button


    Hi,

    Merged cells are a pain !

    Better is not to merge cell but align cell Centered on multiple columns

+ 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. Edit existing marco to unlock protect sheet, remove set criteria and re protect sheet
    By summer2010 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2013, 09:29 AM
  2. Excel 2013 issues with Worksheet Protect in VBA
    By Kraglin1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-07-2013, 04:57 PM
  3. [SOLVED] Protect a range of cells within a sheet
    By SueMac in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-21-2012, 05:45 PM
  4. Protect single cells in one excel sheet
    By Lausa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-11-2012, 11:19 AM
  5. Replies: 3
    Last Post: 07-13-2006, 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