+ Reply to Thread
Results 1 to 5 of 5

Locking column widths in Excel 2007

  1. #1
    Registered User
    Join Date
    03-08-2011
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    9

    Locking column widths in Excel 2007

    Hi,
    I've created a spreadsheet that several people will be using. Is there a way to lock the column width for some of the columns to maintain the format? Seems like it should be very simple to do, but I can't find anything on how to do it. I'm using Excel 2007 btw...
    Thanks!
    Jen
    Last edited by porpitax2; 03-20-2011 at 02:24 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Locking column widths in Excel 2007

    Hi Jen

    You can do it if you Protect the sheet.
    Right click on sheet tab>Protect sheet.

    If you do not give it a password though, users will still be able to Unprotect the sheet.

    Also, be aware that by default, all cells on a sheet are Protected by Default, so when Sheet Protection is applied, you cannot enter or modify cell contents.

    You would need to select all of the cells where you want Data entry to occur, and remove the
    locked attribute >right click >Format>Protection before you apply sheet Protection.
    --
    Regards
    Roger Govier
    Microsoft Excel MVP

  3. #3
    Registered User
    Join Date
    03-08-2011
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Locking column widths in Excel 2007

    Hi Roger,
    Thanks for the help. I highlighted the columns that I don't want to lock and unlocked them. Then I did the Protect Sheet and in the dialogue box I checked everything except for the Format columns option and gave it a password. That seems to have worked for the most part... there are just two problems: it seems to have locked all the columns from formatting changes (i can't change the width of the ones I unlocked either) and I can't input data into the locked cells. Since I just left the Format columns option unchecked, shouldn't the users still be able to do data entry? Or is that really just an overall locking of the columns? Also, any idea why it won't let me resize the columns that aren't locked?
    Thanks!
    Jen

  4. #4
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Locking column widths in Excel 2007

    Hi Jen

    As I said, Cells are locked by DEFAULT so when Sheet Protection is applied, they are Locked.
    You have to take the locked attribute off in the cells where you want to enter data, before protecting the sheet.

    Also, Locking Columns (or Rows) is a sheet Feature, not an individual column feature. So you either lock all of them or none - that's the design I'm afraid.

    The only way you could get around it would be to write some VBA event code to alter the width back to what you wanted, if the user has altered it.

  5. #5
    Registered User
    Join Date
    03-08-2011
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Locking column widths in Excel 2007

    Hi Roger,
    Ah, okay... I was thinking that locking the column formatting would just lock the format (width or text/number/general/etc) but allow for data entry. And I was afraid you were going to say that the locking columns is a sheet feature rather than individual column feature. I'll figure out a way to design the worksheet with this info in mind.
    Thanks so much!
    Jen

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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