+ Reply to Thread
Results 1 to 7 of 7

Locking only certain cells on worksheet

  1. #1
    Registered User
    Join Date
    02-01-2014
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    10

    Locking only certain cells on worksheet

    I have a workbok where I added 3 comboboxes that are positioned over 3 merged cells that are C6, C7, & C8. The comboboxes populate C6-C8 and C9 is just entered normally. I have a button on another page that toggles the visibility of the three comboboxes and locks the range C3:C9. It is working but I'm not getting it to unlock the range if the button is pressed again. code I'm using. I still want to be able to edit the rest of the sheet which seems to be working just not my range after the second button push.

    Please Login or Register  to view this content.
    I get the error "Unable to set the Locked Property of the Range Class." on the second button push and Debug highlights the "Selection.Locked = False" after the ElseIf.

    Can anyone point me to something to read or suggest something to address this?

    Thanks for any help,

    Mum-O-Killowe
    Last edited by Mum-O-Killowe; 02-03-2014 at 01:59 PM.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,293

    Re: Locking only certain cells on worksheet

    Hi,

    Is your sheet protected?

    If so then add an instruction to unprotect it. You can also simplify your code since there's no need to use .Select and the consequential 'Selection'

    Please Login or Register  to view this content.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    02-01-2014
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Locking only certain cells on worksheet

    Wow thanks for the fast reply! By protecting that sheet and using that code it hangs on r.locked = false with the same error. Also I failed to mention that the button is on sheet 3 and the cells are sheet 1. Would that make a difference?

    Thanks,

    Mum-O-Killowe

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,293

    Re: Locking only certain cells on worksheet

    Hi,

    It's always better if you tell us in advance particular factors like that!

    Try instead

    Please Login or Register  to view this content.
    If that doesn't work with your workbook I suggest you upload the actual workbook.

  5. #5
    Registered User
    Join Date
    02-01-2014
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Locking only certain cells on worksheet

    Sorry, in my crazy head that was common knowledge. Not much help to you of course. Using the new code you suggest it still hangs on r.Locked = False and does't protect the cells in question in either state. I have attached my project.

    Thanks so much for all your help,

    Mum-O-Killowe
    Attached Files Attached Files

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,293

    Re: Locking only certain cells on worksheet

    Ah! The dreaded 'Merged Cells' problem synndrome.

    Unless there's some essential reason to use merged cells avoid them like the plague. They are just more trouble than they are worth and cause numerous problems. And for horizontal merged cells the much more use friendly 'Center across selection' format is to be preferred.

    If you really must keep them merged then change the range definition in the macro. i.e.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    02-01-2014
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: [SOLVED] Locking only certain cells on worksheet

    Bingo! Thanks again for the help. So many little things that can be set wrong it's amazing you gurus can ever pinpoint things like that!

    I really appreciate your time!

    Mum-O-Killowe

+ 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. Locking specific cells eventually changing to locking all cells
    By davidingilbert in forum Excel General
    Replies: 1
    Last Post: 03-19-2013, 06:50 PM
  2. Creating New Worksheet from another Worksheet while locking formulas
    By DataVanMan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-12-2012, 04:15 PM
  3. Help with macro, automatical locking cells when excel is closed but only locking 1 sh
    By snoopy1461 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-21-2011, 11:29 AM
  4. Macro for locking cells after worksheet is protected
    By Hugo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-17-2006, 04:05 PM
  5. Replies: 1
    Last Post: 07-21-2006, 11:05 AM

Tags for this Thread

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