Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofit and merge
I am sure there is a simple answer out there...
I am using Excel to create a kind of workbook for my users to fill in...because I have a few lists with checkboxes, there are columns of varying width - after all the basic info such as name, date, project, etc is filled in and the various boxes checked, there are some areas for comments. To create a more appealing layout, I merged a number of cells so text would cross the page. The problem is when I tried to use Autofit to allow the merged cells to expand as text was entered - it didn't work... If I unmerge the cells,it works fine... If I enter text text and use autofit first, then merge the cells, it also seems to work.. But neither are acceptable solutions... I should be able to create a nice layout and my users should be able to just enter text and have the rows adjust as they type... Help.... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofit and merge
Long audible sigh here.................
One more victim of "merged cells". 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 Wed, 23 Aug 2006 12:51:04 -0700, Nimbus55 wrote: I am sure there is a simple answer out there... I am using Excel to create a kind of workbook for my users to fill in...because I have a few lists with checkboxes, there are columns of varying width - after all the basic info such as name, date, project, etc is filled in and the various boxes checked, there are some areas for comments. To create a more appealing layout, I merged a number of cells so text would cross the page. The problem is when I tried to use Autofit to allow the merged cells to expand as text was entered - it didn't work... If I unmerge the cells,it works fine... If I enter text text and use autofit first, then merge the cells, it also seems to work.. But neither are acceptable solutions... I should be able to create a nice layout and my users should be able to just enter text and have the rows adjust as they type... Help.... Gord Dibben MS Excel MVP |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofit and merge
Ok..this worked! Now I hope it doesn't flip SharePoint out and fail utterly
like so many others have...lol. So far, VB code in any workbook I open from SharePoint is autmatcialy disabled by "security" and I have to add yet more freakin' code to get around it...grrrr. How is it that Sharepoint, a MS product is NOT compatible with Excel..another MS product??? Anyway...Gord, thanks for yoru help; Greg ,thanks for the code. "Gord Dibben" wrote: Long audible sigh here................. One more victim of "merged cells". 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 Wed, 23 Aug 2006 12:51:04 -0700, Nimbus55 wrote: I am sure there is a simple answer out there... I am using Excel to create a kind of workbook for my users to fill in...because I have a few lists with checkboxes, there are columns of varying width - after all the basic info such as name, date, project, etc is filled in and the various boxes checked, there are some areas for comments. To create a more appealing layout, I merged a number of cells so text would cross the page. The problem is when I tried to use Autofit to allow the merged cells to expand as text was entered - it didn't work... If I unmerge the cells,it works fine... If I enter text text and use autofit first, then merge the cells, it also seems to work.. But neither are acceptable solutions... I should be able to create a nice layout and my users should be able to just enter text and have the rows adjust as they type... Help.... Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to make (XL) Merge Cells and Word Wrap work concurrently? | Excel Discussion (Misc queries) | |||
Autofit doesn't work with merged cells | Excel Discussion (Misc queries) | |||
Autofit row height in merged cells | Excel Discussion (Misc queries) |