Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofit cells in column width
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: http://www.excelforum.com/member.php...o&userid=30497 View this thread: http://www.excelforum.com/showthread...hreadid=510713 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofit cells in column width
You 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:="password" Me.Range("d1,F1").EntireColumn.AutoFit 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 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: http://www.excelforum.com/member.php...o&userid=30497 View this thread: http://www.excelforum.com/showthread...hreadid=510713 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofit cells in column width
Thanks very much, that works great!!!!!! ----------------------------:) -- Boethius1 ------------------------------------------------------------------------ Boethius1's Profile: http://www.excelforum.com/member.php...o&userid=30497 View this thread: http://www.excelforum.com/showthread...hreadid=510713 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofit cells in column width
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 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:="password" Me.Range("d1,F1").EntireColumn.AutoFit 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 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: http://www.excelforum.com/member.php...o&userid=30497 View this thread: http://www.excelforum.com/showthread...hreadid=510713 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofit cells in column width
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: 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 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:="password" Me.Range("d1,F1").EntireColumn.AutoFit 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 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: http://www.excelforum.com/member.php...o&userid=30497 View this thread: http://www.excelforum.com/showthread...hreadid=510713 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 Me.UsedRange.Columns.AutoFit 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 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:="password" Me.Range("d1,F1").EntireColumn.AutoFit 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 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: http://www.excelforum.com/member.php...o&userid=30497 View this thread: http://www.excelforum.com/showthread...hreadid=510713 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
hOW DO i CHANGE COLUMN WIDTH FOR ONLY A SELECT RANGE OF CELLS? | Excel Discussion (Misc queries) | |||
Comparing Cells and Displaying Data | Excel Worksheet Functions | |||
Change the width of a single column in a column chart | Charts and Charting in Excel |