Re: autofit cells in column width

Now you're talking!!
I left the password lines from your previous code in and just replaced the
middle line (for the specific columns) with the three lines for the whole
worksheet (and turning off/on display).

Thanks much! Exactly what I was looking for!!

"Dave Peterson" wrote:

You could use the worksheet_calculate event.

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Application.EnableEvents = True
End Sub

David wrote:

That works great for the cell where data is actually entered, but it does not
work for cells with formulas. Is there anything extra I can add to also auto
adjust cells with formulas?

"Dave Peterson" wrote:

You could use a worksheet event.

The code unprotects the worksheet, autofits the columns and reprotects the

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect Password:="password"
Me.Protect Password:="password"
End Sub

(I used column D and F--you could do more columns (or all of them).)

Change the password to what you need.

Boethius1 wrote:

i have a spreadsheet that i am sharing with someone, i want to leave two
blank columns for them to use, but i want to protect the rest of the

I do not know how wide they need the columns to be as this can change,
but if i protect the sheet they cannot widen the columns themselves.

Is there a way to set the columns to autofit?


Boethius1's Profile:
View this thread:


Dave Peterson


Dave Peterson


Relevant Pages

  • Re: Locking cells automatically after enter
    ... After the data is entered into the unlocked cells I select all of the cells ... Right click on the worksheet tab that contains that 25x25 table. ... Private Sub Worksheet_Change ... Dim myCell As Range ...
  • Re: Need help with MultiSelect List Box
    ... It doesn't use any worksheet cells at all. ... Dave Peterson wrote: ... Dim iCtr As Long ...
  • RE: Advance to predetermined cell
    ... insert the day's date into the renaming of a worksheet ... if the cells you've listed in your code are the only ones you need to ... Private Sub Worksheet_Change ... dealing with just a few cells on a sheet this should work ok without being ...
  • Re: multiple column dynamic range
    ... But you didn't really answer my question about any typos in the worksheet name. ... I would like the cells to extend equally for as long as is appropriate ... "Dave Peterson" wrote: ... Cells C ARE words andCells D are abbreviations. ...
  • Re: Can you specify an Array as a Target??
    ... "Dave Peterson" wrote: ... Private Sub Worksheet_Change ... set myrngtocheck = intersect,target) ... it will clear the contents of those cells. ...