Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with this code by protect sheet
There was a debug problem while I protect sheet with this event code. Please help me to solve this problem.
Thank you. On Tuesday, September 25, 2007 4:44 PM David wrote: Hey, I have a row with a series of merged cells in it, (b43:m43, makes up the merged cell). I need to autofit text in that merged cell. Every time I use AutoFit it shrinks the entire row to one line (12.50). How do I get it to autofit based on what is in the merged cells b43:m43? This has been driving me crasy for a week now. Thanx On Tuesday, September 25, 2007 7:01 PM Gord Dibben wrote: Long audible sigh here................. One more victim of "merged cells". It may be better to use the "Center Across Selection" from CellsFormatAlignment. Wrap Text works fine on merged cells, but Autofit does not work. You need VBA event 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 This is event code. Right-click on the sheet tab and "View Code". Copy/paste the code into that sheet module. Gord Dibben MS Excel MVP On Tue, 25 Sep 2007 13:44:01 -0700, David A. wrote: On Tuesday, September 25, 2007 7:29 PM Bill Renaud wrote: I believe that merged cells to not participate in AutoFit operations. You might have to write a macro to copy the contents to a new, temporary worksheet, AutoFit the cell there, measure the width of that cell, then set the width of the cell on your original worksheet to this value. Does this range have Wrap Text turned on? Do you want the row height to be a multiple of the normal row height? Then you have an even more complex problem at hand. -- Regards, Bill Renaud Submitted via EggHeadCafe - Software Developer Portal of Choice Assemblies in Folder Debug Build Checker http://www.eggheadcafe.com/tutorials...d-checker.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protect sheet problem | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Code to protect sheet | Excel Worksheet Functions | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming | |||
VBA code - protect and unprotect a sheet | Excel Programming |