Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto row height for merged cells
Jim, I'm not sure why that is not working for me. Since I'm new to VBA, can you provide the whole routine I can cut and paste into my worksheet code? I tried: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Rows.autofit End Sub I really don't understand what the 1st line actually does yet. I assume it looks at a current selection when the enter key is pressed or you navigate away from it. I'll be getting a book on VBA if I don't get one for Christmas. For now I am learning as I go. On Dec 11, 10:38 am, Jim Jackson wrote: 'The line: Rows.autofit ' should give you the proper row height. You can always specify the row number if desired: Rows(12).Autofit -- Best wishes, Jim "RealmSteel" wrote: I've got a sheet that has a bunch of user inputted information in the top portion. The lower portion is for a message. A lot like a Fax cover letter. For each row in the lower portion with the meassge, I have columns B thru L merged. I don't expect the sheet to be more than 3 or 4 pages long max when printed. I am looking to create some code that will automatically adjust the row height for the merged cells if the user types a sentance too long. Another problem I have is if I merge a bunch of rows (say 50), it will print these rows even though no data is in them. I need to prevent that from happening. I only want ot print as many pages as needs with out waste. I'm very new to VBA, but found this link during a search. http://groups.google.com/group/micro...rogramming/bro... I haven't tried it yet, but I know I would need to modify it to search for all cells below say 16 and adjust row height as needed. Is there any newer code out there for doing this? Also, is there a way to do this automatically so the row is adjusted right when the user hits enter or navigates to a different cell? Is there a way to automatically adjust the print area without having to create a button? I created a button to print the log for these sheets, but was hoping to be able to just allow the user to use the normal printer icon. Thanks, Rich- Hide quoted text -- Show quoted text - |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto row height for merged cells
Jim,
I'm obviously doing something wrong. I just created a new workbook and inserted the following code into worksheet: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Rows.AutoFit End Sub I then merged 3 columns (B, C & D) in row 3, formatted them for left justification and Wrap text. I copy and pasted these cells for another 10 rows. I verified the formatting for all the merged cells is Wrap Text. I typed into one of the cells a line longer than the merged cell width and nothing happened. My version os Excell 2003 if that matters. I did notice with the code from the link I provided in my first post, the cell will auto adjust if I navigate back to it and then away. All I have to do is select it and then select another cell. The row will automatically adjust. I'd like it to auto adjust as a soon as I navigate away. Here is what the code looks like right now: Private Sub Worksheet_SelectionChange(ByVal Target As Range) 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, Rich On Dec 11, 1:48 pm, Jim Jackson wrote: When I formatted some cells as "Wrap Text" and typed a sentence in one, made sure only one line of text was showing and clicked "Enter", the cells resized to show the complete text. Are your cells already formatted to allow Wrapping of Text? -- Best wishes, Jim text -- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Row Height with Wrapped Text in Merged Cells Problem | Excel Discussion (Misc queries) | |||
How to enable auto height with merged cells? | Excel Worksheet Functions | |||
Auto Row Height in Merged Cells with pre exisiting text | Excel Worksheet Functions | |||
Solution To Auto Height For Merged Cells | Excel Programming | |||
Auto Row Height for Merged Cells | Excel Programming |