Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Jim Rech's macro won't work (autofitting merged cells)
I cut and pasted Jim Rech's macro to autofit rows containing merged
cells into the worksheet code and it has no effect whatsoever. Neither does putting it in the workbook code. This is Excel 2004 for the Mac, running OS X. I REALLY need to be able to do this; can anybody help? Thanks, Ron M. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Jim Rech's macro won't work (autofitting merged cells)
First, there are several versions of "Jim Rech's macro"...
Is the macro an event macro? Or, if not, did you call it from the Worksheet_Change() event macro? Simply putting a macro in a worksheet or workbook code module doesn't make it work automatically. Some versions of Jim's macros operate on the active cell. If you have your preferences set to move the active cell when you hit Return, then the macro will attempt to operate on the incorrect cell. You can change the macro to refer to a particular cell, or you can modify it to refer to the changed cell if it's an event macro. In article .com, "Ron M." wrote: I cut and pasted Jim Rech's macro to autofit rows containing merged cells into the worksheet code and it has no effect whatsoever. Neither does putting it in the workbook code. This is Excel 2004 for the Mac, running OS X. I REALLY need to be able to do this; can anybody help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Jim Rech's macro won't work (autofitting merged cells)
Thanks JE. Well, I stuck it in the "This Workbook" code like I do
everything else. I'm not clear on what your instructions mean, in any case. In this spread sheet, there are about 20 different places where there are merged cells, and the user needs to be able to enter multiple lines of text while the row autofits. So I need some kind of "generic" code that will run regardless of where the text is being entered. Interesting that some of you whizzes can do this in a couple dozen lines of code, but Microsoft can't... (-; Ron M. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Jim Rech's macro won't work (autofitting merged cells)
My rule of thumb is to use the ThisWorkbook module only for
Workbook-level event macros, and worksheet modules only for worksheet-level event macros. Everything else goes in regular code modules (Insert/Module in the VBE). This is a very common convention. You may want to look at http://www.mcgimpsey.com/excel/modules.html and http://cpearson.com/excel/codemods.htm It's not so much that MS *can't* do the autofit as it is that changing the behavior risks breaking thousands of existing apps. You might consider a worksheet-level event macro something like: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target.Cells, Range("A1,B2,C3,D4:F6")) Is Nothing Then AutoFitMergedCellRowHeight Target End Sub Then use a version of Jim's macro like (untested): Sub AutoFitMergedCellRowHeight(Optional rTarget As Range) Dim CurrentRowHeight As Double Dim MergedCellRgWidth As Double Dim CurrCell As Range Dim TargetCellWidth As Double Dim PossNewRowHeight As Double If rTarget Is Nothing Then Set rTarget = ActiveCell If rTarget.MergeCells Then With rTarget.MergeArea If .Rows.Count = 1 And .WrapText = True Then Application.ScreenUpdating = False CurrentRowHeight = .RowHeight TargetCellWidth = rTarget.ColumnWidth For Each CurrCell In rTarget MergedCellRgWidth = CurrCell.ColumnWidth + _ MergedCellRgWidth Next .MergeCells = False .Cells(1).ColumnWidth = MergedCellRgWidth .EntireRow.AutoFit PossNewRowHeight = .RowHeight .Cells(1).ColumnWidth = ActiveCellWidth .MergeCells = True .RowHeight = IIf(CurrentRowHeight _ PossNewRowHeight, CurrentRowHeight, _ PossNewRowHeight) End If End With End If End Sub In article . com, "Ron M." wrote: Thanks JE. Well, I stuck it in the "This Workbook" code like I do everything else. I'm not clear on what your instructions mean, in any case. In this spread sheet, there are about 20 different places where there are merged cells, and the user needs to be able to enter multiple lines of text while the row autofits. So I need some kind of "generic" code that will run regardless of where the text is being entered. Interesting that some of you whizzes can do this in a couple dozen lines of code, but Microsoft can't... (-; Ron M. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Jim Rech's macro won't work (autofitting merged cells)
JE, would you mind telling me just how to install that? I put it in the
worksheet's code, and it had no effect. How do I change those cells up in that first section? Ron M. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Selected cells down a row with macro | Excel Discussion (Misc queries) | |||
Macro to highlight cells based on content | Excel Worksheet Functions | |||
I moved a file from work to home and now my color macro does not w | Excel Worksheet Functions | |||
Record Macro Relative does not work? | Excel Discussion (Misc queries) | |||
Record Macro Relative does not work? | Excel Discussion (Misc queries) |