Screen flicker

[gustaf111]

Hello all,

I got the code below, are there any way to disable screen update during the function ? The code make my sheet flicker The code adjust the row height of merged cells. (The area Name1 are meraged cells)

Gustaf



Private Sub CommandButton1_Click()
WrapText Range("Name1").Offset(0), 15.75, False
End Sub

Private Function WrapText(ByVal Target As Range, ByVal Max As Variant, lockCell As Boolean) As Variant
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
Application.ScreenUpdating = False
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 * 1.18
Next
On Error Resume Next
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = WorksheetFunction.Max(NewRwHt, Max)
If (lockCell = True) Then
ma.Locked = True
Else
ma.Locked = False
End If
cWdth = 0: MrgeWdth = 0
On Error GoTo 0
End If
End With
WrapText = NewRwHt
Application.ScreenUpdating = True
End Function

 

[Gavona]

Application.screenupdating=False

Gavin

 

[Gavona]

Sorry, pressed send by mistake.
Application.screenupdating should do it. If you step through the code with the Excel window visible where does the screen refresh?

Gavin

 

[gustaf111]

Hello Gavin,

At the row c.ColumnWidth = MrgeWdth

 

[Gavona]

Sorry, I get the same thing. I used the watch widow to confirm that nothing was changing the value of Screenupdating.

Maybe your code can be re-written to minimse the effect?

Gavin

 

[UnsolvedCoding]

I would suggest replacing c.ColumnWidth = MrgeWdth with autosizing the column.

 

[gustaf111]

Ohh, autozizing the column ?

 

[UnsolvedCoding]

I mean auto fit, but this would size the columns on the entire sheet -

Cells.EntireColumn.AutoFit



Or you can specify the columns like this -

Range("E:N").EntireColumn.AutoFit

 

[UnsolvedCoding]

Autofit also works for row height.

 

[UnsolvedCoding]

Ok, I need to stop getting interrupeted on my end.

My point to using autofit is that autofit doesn't need to have the sheet selected and can be triggered from anywhere. Some of the apps I have require autosizing but because of the amount of worksheet manipulation you feel like your looking straight at a strobe light if the screen updating isn't false.

To get around this sort of flicker I simply have a specific page in the workbook that the user sees and then set everything else to happen by specifying what worksheet without using select or activate and doing things like auotfit to the column.

So far that has worked great.

For example this code is tripped from the worksheet called Cover where the buttons are located. This is how my typical code looks

'Auto size column width
Sheets("2-Recon").Range("E:N").EntireColumn.AutoFit

' Adjust column F
Sheets("2-Recon").Columns("F:F").ColumnWidth = 8

' Adjust column J
Sheets("2-Recon").Columns("J:J").ColumnWidth = 2