Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word wrapping merged cells
I have the following code in my worksheet that word wraps merged cells. It
wraps the cell contents fine after a user types in the cells and clicks enter. The problem is that it then protects the cells, so then a user cannot edit the contents in the cells because they are protected. Can I change this code so that it doesn't protect the cells after word wrapping? Also, I need to wrap cells F12:F15 as well. How can I include those merged cells in the code below. Thanks for your help. Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) ActiveSheet.Unprotect Password:="abcde" Dim RowHt As Single, MergeWidth As Single Dim C As Range, AutoFitRng As Range Dim CWidth As Single, NewRowHt As Single Static OldRng As Range ActiveSheet.Unprotect Password:="abcde" On Error Resume Next If OldRng Is Nothing Then _ Set OldRng = Range("B31").MergeArea Set AutoFitRng = Range("B31:I33") If Not Intersect(OldRng, AutoFitRng) Is Nothing Then Application.ScreenUpdating = False With OldRng RowHt = .RowHeight CWidth = .Cells(1).ColumnWidth For Each C In OldRng MergeWidth = C.ColumnWidth + MergeWidth Next .MergeCells = False .Cells(1).ColumnWidth = MergeWidth .EntireRow.AutoFit NewRowHt = .RowHeight .Cells(1).ColumnWidth = CWidth .MergeCells = True .RowHeight = NewRowHt End With Application.ScreenUpdating = True End If Set OldRng = Target ActiveSheet.Protect Password:="abcde" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word wrapping merged cells
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' ActiveSheet.Unprotect Password:="abcde" Dim RowHt As Single, MergeWidth As Single Dim C As Range, AutoFitRng As Range Dim CWidth As Single, NewRowHt As Single Static OldRng As Range On Error Resume Next If OldRng Is Nothing Then _ Set OldRng = Range("B31").MergeArea Set AutoFitRng = Range("B31:I33") If Not Intersect(OldRng, AutoFitRng) Is Nothing Then if Activesheet.ProtectContents then ActiveSheet.Unprotect Password:="abcde" end if Application.ScreenUpdating = False With OldRng RowHt = .RowHeight CWidth = .Cells(1).ColumnWidth For Each C In OldRng MergeWidth = C.ColumnWidth + MergeWidth Next .MergeCells = False .Cells(1).ColumnWidth = MergeWidth .EntireRow.AutoFit NewRowHt = .RowHeight .Cells(1).ColumnWidth = CWidth .MergeCells = True .RowHeight = NewRowHt End With Application.ScreenUpdating = True End If Set OldRng = Target ' ActiveSheet.Protect Password:="abcde" End Sub Why not make a copy of the code and adjust it for F12:15 and run it once. I don't see that you would need to include that in the change event. -- Regards, Tom Ogilvy "Alex" wrote: I have the following code in my worksheet that word wraps merged cells. It wraps the cell contents fine after a user types in the cells and clicks enter. The problem is that it then protects the cells, so then a user cannot edit the contents in the cells because they are protected. Can I change this code so that it doesn't protect the cells after word wrapping? Also, I need to wrap cells F12:F15 as well. How can I include those merged cells in the code below. Thanks for your help. Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) ActiveSheet.Unprotect Password:="abcde" Dim RowHt As Single, MergeWidth As Single Dim C As Range, AutoFitRng As Range Dim CWidth As Single, NewRowHt As Single Static OldRng As Range ActiveSheet.Unprotect Password:="abcde" On Error Resume Next If OldRng Is Nothing Then _ Set OldRng = Range("B31").MergeArea Set AutoFitRng = Range("B31:I33") If Not Intersect(OldRng, AutoFitRng) Is Nothing Then Application.ScreenUpdating = False With OldRng RowHt = .RowHeight CWidth = .Cells(1).ColumnWidth For Each C In OldRng MergeWidth = C.ColumnWidth + MergeWidth Next .MergeCells = False .Cells(1).ColumnWidth = MergeWidth .EntireRow.AutoFit NewRowHt = .RowHeight .Cells(1).ColumnWidth = CWidth .MergeCells = True .RowHeight = NewRowHt End With Application.ScreenUpdating = True End If Set OldRng = Target ActiveSheet.Protect Password:="abcde" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wrapping Text in Merged Cells | Excel Worksheet Functions | |||
Excel 2007: Word wrapping in merged cells | Excel Discussion (Misc queries) | |||
wrapping text in merged cells | Excel Discussion (Misc queries) | |||
Wrapping text in merged cells | Excel Discussion (Misc queries) | |||
merged cells and wrapping text | Excel Programming |