Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically resize columns based on new formula result
I have a sheet that uses a bunch of vlookup formulas that are referencing a hidden tab. Users select a value from a dropdown and all data related to that value is pulled from the hidden tab. I am trying to figure out a way to have excel automatically resize the columns based on the width of the formula result. I am trying to avoid making the end users resize everything each time they use it. I think I could figure out a macro to do this, but then users with certain security settings may not be able to use it. It is a very crammed sheet as it is, so I am trying to make it as space efficient as possible. Any ideas? -- Kevin Ward ------------------------------------------------------------------------ Kevin Ward's Profile: http://www.excelforum.com/member.php...o&userid=31417 View this thread: http://www.excelforum.com/showthread...hreadid=511122 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically resize columns based on new formula result
You could use a worksheet event.
If you want to try... Right click on the worksheet tab that should have this behavior. Select view code and paste this into the code window that you see: Option Explicit Private Sub Worksheet_Calculate() Me.Range("a1,e1,j1").EntireColumn.AutoFit 'or get them all: me.usedrange.columns.autofit End Sub Then back to excel and force a recalculation. Kevin Ward wrote: I have a sheet that uses a bunch of vlookup formulas that are referencing a hidden tab. Users select a value from a dropdown and all data related to that value is pulled from the hidden tab. I am trying to figure out a way to have excel automatically resize the columns based on the width of the formula result. I am trying to avoid making the end users resize everything each time they use it. I think I could figure out a macro to do this, but then users with certain security settings may not be able to use it. It is a very crammed sheet as it is, so I am trying to make it as space efficient as possible. Any ideas? -- Kevin Ward ------------------------------------------------------------------------ Kevin Ward's Profile: http://www.excelforum.com/member.php...o&userid=31417 View this thread: http://www.excelforum.com/showthread...hreadid=511122 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically resize columns based on new formula result
Thanks for the help Dave. It works perfect. Now if I could just get our IT department to not have the security settings so high I wouldn't have to explain to everyone how to change them in order to have macros work. thanks again for your help! -- Kevin Ward ------------------------------------------------------------------------ Kevin Ward's Profile: http://www.excelforum.com/member.php...o&userid=31417 View this thread: http://www.excelforum.com/showthread...hreadid=511122 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
EXCEL:NUMBER TO GREEK WORDS | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Cell shows formula and not the result of the formula. | Excel Worksheet Functions | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions |