Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set a cell to auto expand when it has a formula?
I have a formula in a cell (destination) that derives a text field from
another Excel Sheet (Source). When I enter new text in the Source sheet, as expected it populates in teh destination dield. However, the destination field needs to be adjusted to display the entire contents each time the the text lenght increases. Setting "Wrap Text" option does not resize the field each time the length increases. I need this feature as part of my work, and currently I do this manually. Thanks your suggestion |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set a cell to auto expand when it has a formula?
How about pasting this into the sheet object in the VB Editor. Regardless of
what is placed into the cell, the column will resize. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Cells.EntireColumn.AutoFit Application.EnableEvents = True End Sub -- Hope this helps, MarkN "SriGog" wrote: I have a formula in a cell (destination) that derives a text field from another Excel Sheet (Source). When I enter new text in the Source sheet, as expected it populates in teh destination dield. However, the destination field needs to be adjusted to display the entire contents each time the the text lenght increases. Setting "Wrap Text" option does not resize the field each time the length increases. I need this feature as part of my work, and currently I do this manually. Thanks your suggestion |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set a cell to auto expand when it has a formula?
Thanks for responding. However, that did not resize the cell automatically.
SriGog "SriGog" wrote: I have a formula in a cell (destination) that derives a text field from another Excel Sheet (Source). When I enter new text in the Source sheet, as expected it populates in teh destination dield. However, the destination field needs to be adjusted to display the entire contents each time the the text lenght increases. Setting "Wrap Text" option does not resize the field each time the length increases. I need this feature as part of my work, and currently I do this manually. Thanks your suggestion |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set a cell to auto expand when it has a formula?
Also, I forgot to add that the cell where I need to display the data is a
merged cell. Is that adding to the issue? "SriGog" wrote: I have a formula in a cell (destination) that derives a text field from another Excel Sheet (Source). When I enter new text in the Source sheet, as expected it populates in teh destination dield. However, the destination field needs to be adjusted to display the entire contents each time the the text lenght increases. Setting "Wrap Text" option does not resize the field each time the length increases. I need this feature as part of my work, and currently I do this manually. Thanks your suggestion |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set a cell to auto expand when it has a formula?
The merged cell is not adding to the issue......it is the cause of the issue.
Wrap Text works fine on merged cells, but Autofit does not work. You need VBA code to do that. Here is code from Greg Wilson. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText Then Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 Application.ScreenUpdating = True End If End With End Sub Gord Dibben MS Excel MVP On Tue, 15 Aug 2006 09:34:02 -0700, SriGog wrote: Also, I forgot to add that the cell where I need to display the data is a merged cell. Is that adding to the issue? "SriGog" wrote: I have a formula in a cell (destination) that derives a text field from another Excel Sheet (Source). When I enter new text in the Source sheet, as expected it populates in teh destination dield. However, the destination field needs to be adjusted to display the entire contents each time the the text lenght increases. Setting "Wrap Text" option does not resize the field each time the length increases. I need this feature as part of my work, and currently I do this manually. Thanks your suggestion Gord Dibben MS Excel MVP |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set a cell to auto expand when it has a formula?
Debbie:
Thank you. I was able put this code in the Worksheet_selectionChange and can manage the auto sizing. However,this requires me to click on the cells that I want to auto-size (which is the trigger for the resize). I looked to see if there is any way to make it a worksheet level macro (That is select the worksheet and it resizes all the fields that need to ne resized). I could not find a way. I wish there is a mechanism for that. If you know, I will be grateful to you. This is needed on a work book with 60 worksheets. Thank you. "SriGog" wrote: I have a formula in a cell (destination) that derives a text field from another Excel Sheet (Source). When I enter new text in the Source sheet, as expected it populates in teh destination dield. However, the destination field needs to be adjusted to display the entire contents each time the the text lenght increases. Setting "Wrap Text" option does not resize the field each time the length increases. I need this feature as part of my work, and currently I do this manually. Thanks your suggestion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell formula not updating, auto calc on, over 100 sheets | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel |