hello all,
new to using excel on a higher level, but i have what i feel is a simple request. what i need is when i fill cell “a” with info that cell “b” populates with that data; ok thats easy. problem is that cell “b” needs to be able to auto adjust the height if more than one row of data is typed into cell “a”. i’ve found some VBA that should do it, but im in 2008 and have found the big issue with macros and VBA. will this work?
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
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
thanks all…
Kevin
No, that won’t work in 2008. As I understand it, there is no way for 2008 to run a VBA macro.
As I understand the situation,
you have “abc” & CHAR(13) & “xyz” in cell A1
in B2, you have the formula =A1
and the text in B2 is shown all on one line.
Have you tried Selecting B2, Format Cell > Alignment > Wrap Text
If that doesn’t work, I’ll take a swing at converting that VBA to AppleScript.
thanks for the response…yes the problem I’ve encountered is that i need the cells to be merged; which does not work. Does the VBA look to be correct? been on this all day, and is very frustrating to accomplish such a simple task.
basically we have various “1-10 for example” project scopes; these will then populate a group of merged cells below our “project description” sheet. so, i need these cells that get populated to “auto format” the correct height to make room for more than one line “scope of info”…make sense?
thank you so much…
Kevin
I’m not completely sure what the purpose of the VBA routine is, but it looks OK.
I’m a little confused by
CurrentRowHeight = .RowHeight
Since that runs only when 1 row is in question, why not use the .Height property.
But this talk of VBA is a bit futile if you are using Excel 2008.
I’m not sure what problem you are trying to solve. The code posted looks like; given a merged area you want to unmerge the area and increase the size of upper left cell of that area to the size of the original merged area.
(I have to comment that Merged Cells cause a ton of other headaches. To mention just two, sorting and filtering are FUBAR. Merged Cells should be avoided when ever possible. The horizonatal alignment Center Across Selection is a common alternative.)
If you are looking for an apple script to do the formatting that you want, could you post an example of what you want and what isn’t working.
If you are looking for VBA help with that project, I’d recommend asking at http://www.vbaexpress.com/portal.php. (Again with an example)
If you are looking for help with both VBA and Apple Script…this feels like an attached workbook would be helpful, but this site doesn’t seem to have the capability. BTW, posting a link to any cross-post is considered courtious on all help boards.
(I just noticed something about the code, rather than looping/adding .ColumnWidth propeties, the .Width property of a multi-column range could be used, eliminating a loop.)
mike,
sorry if i posted anything that was not courtious in any way. with 2008 my only option is to use a apple script. i have yet to find any apple scripts that can do this, but tons of info on using a macro or VBA on this topic. here is a link on a few examples on what im trying to accomplish. once again thank you for your help and detailed info.
http://www.source-code.biz/snippets/vbasic/11.htm
hope my post did not break any known rules; new to the forums…
Kevin
No, you have not been discourteous in any fashion. Since I’m a mod at a differerent (unmentionded in this thread) forum, I’m always reminding folks to post links and the habit slipped out.
Have you downloaded the Apple Script object model from the MicroSoft site?
http://www.microsoft.com/mac/developers/default.mspx?CTT=PageView&clr=99-21-0&target=4acff5ca-5863-4bb6-9a3b-09d2bc0d1dc71033&srcid=e1dbbe49-e45b-4606-bc00-dc5d3bd2d4601033&ep=7
Microsoft wrote its Applescript to be very similar to VBA.
This is my translation of the linked routine into AppleScript. It declares the range explicitly rather than passing it as an argument.
-- Merges a cell range, wraps text and auto-fits the row height.
tell application "Microsoft Excel"
-- Because the Row.AutoFit method ignores merged cells, we temporarily expand the first column of
-- the cell range to the width of the whole cell range and call AutoFit with the un-merged cell.
set RangeToFit to get range "C3:D3"
set RangeToFit to (get merge area of cells of cell 1 of RangeToFit)
set topRow to cells of row 1 of RangeToFit
set leftCol to cells of column 1 of RangeToFit
set rangeWidth to width of RangeToFit
set oldColumnWidth to width of leftCol
repeat with i from 1 to 3
set column width of leftCol to ((column width of leftCol) * (rangeWidth / (width of leftCol)))
end repeat
set oldWrap to wrap text of RangeToFit
set wrap text of RangeToFit to true
set merge cells of RangeToFit to false
set oldRowHeight to height of topRow
autofit row 1 of topRow
set fitRowHeight to height of topRow
set merge cells of RangeToFit to true
set column width of leftCol to oldColumnWidth
set row height of topRow to fitRowHeight
end tell