Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Dynamically changing column width (97)

    I am writing some templates for use by others. I turn protection on to keep the formulas from being changed. However, this keeps the user from being able to widen a column if a number is too large to be displayed. Is there a way, without a macro, to format a cell so the column width increases to display any number returned in that cell?

    Ronny
    Ronny Richardson

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically changing column width (97)

    I'm afraid not. Column widths in protected worksheets can't be changed in Excel 97, period. The only way around it is to write a macro that temporily unprotects the worksheet, autofits columns and then reportects the worksheet.

    Note: in Excel 2002, you have more control over what a user can and can't do in a protected worksheet.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically changing column width (97)

    > Note: in Excel 2002, you have more control over what a user can and can't do in a protected worksheet.
    Hans, would you care to elaborate?
    In Excel2003 one can't (in a macro) autofit within a protected sheet, as far as I can see.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically changing column width (97)

    First reply: see screenshot below. Excel 2002 and later have more options than Excel 2000 and before in the Protect Sheet dialog.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically changing column width (97)

    Second reply: if you allow "Format columns" as in the screenshot in my previous reply, something like

    Range("F1").EntireColumn.AutoFit

    will work in a protected sheet.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically changing column width (97)

    > First reply: see screenshot below.
    Thanks for these two responses.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Dynamically changing column width (97)

    Additionally, if you protect the sheet in code, you can specify the <code>userinterfaceonly:=true</code> option and then your code will run unhindered.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically changing column width (97)

    > specify the userinterfaceonly:=true
    Thanks, Rory. This has led me into a new wilderness, which I will explore today!

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Dynamically changing column width (97)

    Have fun! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    (I just had to check that it was available back then, as I hadn't noticed you were talking about Excel 97.)
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically changing column width (97)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Chris was talking about Excel 2003 (see <post:=662,044>post 662,044</post:>). The original post, which dates from four years ago, was about Excel 97.

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically changing column width (97)

    >Have fun!
    Grrrrrr!

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Dynamically changing column width (97)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>but that would involve me reading a whole thread before jumping in with both feet... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •