software

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
spreadsheet.

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------------------------------------------------------------------------
Boethius1's Profile: www.excelforum.com/member.php...oamp;userid=30497
View this thread: www.excelforum.com/showthread...hreadid=510713You could use a worksheet event.

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

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:=quot;passwordquot;
Me.Range(quot;d1,F1quot;).EntireColumn.AutoFit
Me.Protect Password:=quot;passwordquot;
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:
gt;
gt; i have a spreadsheet that i am sharing with someone, i want to leave two
gt; blank columns for them to use, but i want to protect the rest of the
gt; spreadsheet.
gt;
gt; I do not know how wide they need the columns to be as this can change,
gt; but if i protect the sheet they cannot widen the columns themselves.
gt;
gt; Is there a way to set the columns to autofit?
gt; ----------------------------------------------------------
gt;
gt; --
gt; Boethius1
gt;
gt; ------------------------------------------------------------------------
gt; Boethius1's Profile: www.excelforum.com/member.php...oamp;userid=30497
gt; View this thread: www.excelforum.com/showthread...hreadid=510713

--

Dave Peterson


Thanks very much, that works great!!!!!!
------------------------------
Boethius1------------------------------------------------------------------------
Boethius1's Profile: www.excelforum.com/member.php...oamp;userid=30497
View this thread: www.excelforum.com/showthread...hreadid=510713That 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?

quot;Dave Petersonquot; wrote:

gt; You could use a worksheet event.
gt;
gt; The code unprotects the worksheet, autofits the columns and reprotects the
gt; worksheet.
gt;
gt; Rightclick on the worksheet tab that should have this behavior. Select view
gt; code and paste this into the code window:
gt;
gt; Option Explicit
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; Me.Unprotect Password:=quot;passwordquot;
gt; Me.Range(quot;d1,F1quot;).EntireColumn.AutoFit
gt; Me.Protect Password:=quot;passwordquot;
gt; End Sub
gt;
gt; (I used column D and F--you could do more columns (or all of them).)
gt;
gt; Change the password to what you need.
gt;
gt; Boethius1 wrote:
gt; gt;
gt; gt; i have a spreadsheet that i am sharing with someone, i want to leave two
gt; gt; blank columns for them to use, but i want to protect the rest of the
gt; gt; spreadsheet.
gt; gt;
gt; gt; I do not know how wide they need the columns to be as this can change,
gt; gt; but if i protect the sheet they cannot widen the columns themselves.
gt; gt;
gt; gt; Is there a way to set the columns to autofit?
gt; gt; ----------------------------------------------------------
gt; gt;
gt; gt; --
gt; gt; Boethius1
gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; Boethius1's Profile: www.excelforum.com/member.php...oamp;userid=30497
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=510713
gt;
gt; --
gt;
gt; Dave Peterson
gt;

You could use the worksheet_calculate event.

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Me.UsedRange.Columns.AutoFit
Application.EnableEvents = True
End Sub
David wrote:
gt;
gt; That works great for the cell where data is actually entered, but it does not
gt; work for cells with formulas. Is there anything extra I can add to also auto
gt; adjust cells with formulas?
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; You could use a worksheet event.
gt; gt;
gt; gt; The code unprotects the worksheet, autofits the columns and reprotects the
gt; gt; worksheet.
gt; gt;
gt; gt; Rightclick on the worksheet tab that should have this behavior. Select view
gt; gt; code and paste this into the code window:
gt; gt;
gt; gt; Option Explicit
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; Me.Unprotect Password:=quot;passwordquot;
gt; gt; Me.Range(quot;d1,F1quot;).EntireColumn.AutoFit
gt; gt; Me.Protect Password:=quot;passwordquot;
gt; gt; End Sub
gt; gt;
gt; gt; (I used column D and F--you could do more columns (or all of them).)
gt; gt;
gt; gt; Change the password to what you need.
gt; gt;
gt; gt; Boethius1 wrote:
gt; gt; gt;
gt; gt; gt; i have a spreadsheet that i am sharing with someone, i want to leave two
gt; gt; gt; blank columns for them to use, but i want to protect the rest of the
gt; gt; gt; spreadsheet.
gt; gt; gt;
gt; gt; gt; I do not know how wide they need the columns to be as this can change,
gt; gt; gt; but if i protect the sheet they cannot widen the columns themselves.
gt; gt; gt;
gt; gt; gt; Is there a way to set the columns to autofit?
gt; gt; gt; ----------------------------------------------------------
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Boethius1
gt; gt; gt;
gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; Boethius1's Profile: www.excelforum.com/member.php...oamp;userid=30497
gt; gt; gt; View this thread: www.excelforum.com/showthread...hreadid=510713
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

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!!

quot;Dave Petersonquot; wrote:

gt; You could use the worksheet_calculate event.
gt;
gt; Option Explicit
gt; Private Sub Worksheet_Calculate()
gt; Application.EnableEvents = False
gt; Me.UsedRange.Columns.AutoFit
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt;
gt;
gt; David wrote:
gt; gt;
gt; gt; That works great for the cell where data is actually entered, but it does not
gt; gt; work for cells with formulas. Is there anything extra I can add to also auto
gt; gt; adjust cells with formulas?
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; You could use a worksheet event.
gt; gt; gt;
gt; gt; gt; The code unprotects the worksheet, autofits the columns and reprotects the
gt; gt; gt; worksheet.
gt; gt; gt;
gt; gt; gt; Rightclick on the worksheet tab that should have this behavior. Select view
gt; gt; gt; code and paste this into the code window:
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; gt; Me.Unprotect Password:=quot;passwordquot;
gt; gt; gt; Me.Range(quot;d1,F1quot;).EntireColumn.AutoFit
gt; gt; gt; Me.Protect Password:=quot;passwordquot;
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; (I used column D and F--you could do more columns (or all of them).)
gt; gt; gt;
gt; gt; gt; Change the password to what you need.
gt; gt; gt;
gt; gt; gt; Boethius1 wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; i have a spreadsheet that i am sharing with someone, i want to leave two
gt; gt; gt; gt; blank columns for them to use, but i want to protect the rest of the
gt; gt; gt; gt; spreadsheet.
gt; gt; gt; gt;
gt; gt; gt; gt; I do not know how wide they need the columns to be as this can change,
gt; gt; gt; gt; but if i protect the sheet they cannot widen the columns themselves.
gt; gt; gt; gt;
gt; gt; gt; gt; Is there a way to set the columns to autofit?
gt; gt; gt; gt; ----------------------------------------------------------
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; Boethius1
gt; gt; gt; gt;
gt; gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; Boethius1's Profile: www.excelforum.com/member.php...oamp;userid=30497
gt; gt; gt; gt; View this thread: www.excelforum.com/showthread...hreadid=510713
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;