+ Reply to Thread
Results 1 to 10 of 10

Lock The Borders In A Cell

  1. #1
    Registered User
    Join Date
    06-26-2015
    Location
    london
    MS-Off Ver
    2007
    Posts
    33

    Lock The Borders In A Cell

    Hi all

    I need to know if ther may be a way to lock the borders in a cell? We uses excel to do our scheduling and from week to week we just cut and past the name from one cell to another but all cell do not have the same border (we have some hidden cells). So i would like to move only the name in the cell and not the format. Can this be Done?

    Problem at the moment is if i copy & paste a cell, it copies the border too and then i have to unhide the rows/coloums and make a border all the way around. The catch is i cannot protect the formatting of the sheet as we regularly highlight different names. Only thing that does not change is the location of the border.

    Thanks

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Lock The Borders In A Cell

    Are you trying to find a way to do this for yourself, or restrict what other users can do?

    The easiest thing is to use Paste Values instead of Paste.

    There is no built-in method of locking formatting while allowing content to be pasted/updated. A more comprehensive solution would require macros.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    06-26-2015
    Location
    london
    MS-Off Ver
    2007
    Posts
    33

    Re: Lock The Borders In A Cell

    i would like to restrict myself and others from changing borders through copy and paste.

    yes i understand a little more about excel formatting and pasting values only, however my collegues are a little dumb founded in trying to remember a new paste button when they always use Ctrl V

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Lock The Borders In A Cell

    Select each cell in turn around the cell that you want to paste into and create the border that you want to have showing around the "target cell". Select the "target cell" and in the Format Cells, Protection, unlock the cell. Select another cell and unlock it.

    Protect the worksheet not allowing the selection locked cells.

    Now, copy something from another worksheet and paste into the unlocked cell surrounded by the border that you created. It will seem that the border has been destroyed. Click in another unprotected cell and the border will reappear.

    If there are a lot of edits to be made on the worksheet, select the worksheet and in Format Cells, Protection, unlock all the cells and only lock the cells surrounding the "target cells" that will have borders that you want to protect.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    06-26-2015
    Location
    london
    MS-Off Ver
    2007
    Posts
    33

    Re: Lock The Borders In A Cell

    like the sound of your steps, however i have 5 cells accors and 6 cells down, in each bordered box. I have 1000+ boxes, are their any shortcuts to your steps or do i do it manually?

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Lock The Borders In A Cell

    Someone who knows VBA could likely create code to select the cells surrounding the target cells and give them borders that border the target cells and lock them at the same time....unfortunately, I don't do VBA.

  7. #7
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Lock The Borders In A Cell

    Ron doesn't do VBA, and I usually don't do formulas.

    See the attached sample file for a VBA implementation for two predefined sample ranges. This implements borders, border color, and cell color only. For font attributes, either additional items must be predefined, or a MASTER cell must exist that contains the desired attributes. The MASTER cell can be hidden using custom formatting using 3 semi-colons and no brackets [;;;].

    The following code goes in the Sheet module for the sheet that contains the data such as Sheet1:
    Please Login or Register  to view this content.
    Lewis
    Last edited by LJMetzger; 07-25-2015 at 02:43 PM.

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Lock The Borders In A Cell

    @LJMetzger

    Ron

  9. #9
    Registered User
    Join Date
    10-14-2021
    Location
    Brevard County, FL
    MS-Off Ver
    365
    Posts
    1

    Thumbs up Re: Lock The Borders In A Cell

    SOLUTION (found elsewhere):

    https://www.excelhow.net/how-to-prot...-in-excel.html

    Basically, it requires you unlock specific cells that you want to change, lock the sheet, and don't allow formatting to be changed.

    Worked for me!
    Last edited by 6StringJazzer; 10-14-2021 at 10:42 AM. Reason: Edited link to be live link

  10. #10
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Lock The Borders In A Cell

    Welcome to the Forum tjgibbs89! Normally we would be suspicious of a member who posts their first post in a thread that's over six years old, but in this case it looks like your reference directly addresses the question so we'll let it stand.

+ 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. Lock the borders in a cell
    By kygwalt in forum Excel General
    Replies: 6
    Last Post: 12-24-2014, 09:47 AM
  2. VBA Find Dynamic Range based on Cell Borders then combine to one cell
    By hemiceni in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-03-2014, 12:38 AM
  3. [SOLVED] Auto change borders to thick borders
    By Pierce Quality in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-22-2013, 11:17 PM
  4. [SOLVED] Adjust Multiple Cell Borders Based on Cell Contents
    By USFishin in forum Excel General
    Replies: 16
    Last Post: 04-10-2012, 12:56 AM
  5. Replies: 3
    Last Post: 02-20-2012, 05:09 PM
  6. Cell Borders
    By bstubbs in forum Excel General
    Replies: 3
    Last Post: 01-16-2010, 10:00 PM
  7. Replies: 0
    Last Post: 10-03-2005, 03:05 PM

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