Remove screen 'flicker' on executing code

[peter.thompson]

I have a worksheet that displays lines of cashflows by type of cash flow

On worksheet activation, the sheet resets and updates the input and then removes any blank rows for better presentation and print output.
Am using the following code in the worksheet (not elegant but it works - I'm very new to VBA -first project). While this works, there is significant 'flicker' on the screen while the worksheet goes about its business.

Is there a way to accomplish the same without having to endure the flicker e.g. could i set up a 'beforeclose' routine in another sheet & if so, would appreciate a few pointers on how to go about doing this.

Any help much appreciated
Here's the code I'm using


Public Sub worksheet_activate()
Sheet2.Button102_Click
Password = "djdog"
ActiveSheet.Unprotect Password
Dim tableEnd As Double
Dim m As Double

' tableEnd is set to the last row in the spreadsheet.
' work backwards from the last row upwards and hide the row if it is empty.

tableEnd = Range("a7:k111").SpecialCells(xlCellTypeLastCell).Row
For m = tableEnd To 1 Step -1
If RowIsEmpty(m) Then Cells(m, 1).EntireRow.Hidden = True
Next m

Password = "djdog"
ActiveSheet.Protect Password, True, True, True

End Sub

Public Sub Button102_Click()

Password = "djdog"
ActiveSheet.Unprotect Password
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Password = "djdog"
ActiveSheet.Protect Password, True, True, True
End Sub

Cheers

Peter

[Mbt6]

"peter.thompson"
<[email protected]> wrote in
message news:[email protected]...
>
> I have a worksheet that displays lines of cashflows by type of cash
> flow
>
> On worksheet activation, the sheet resets and updates the input and
> then removes any blank rows for better presentation and print output.
> Am using the following code in the worksheet (not elegant but it works
> - I'm very new to VBA -first project). While this works, there is
> significant 'flicker' on the screen while the worksheet goes about its
> business.
>
> Is there a way to accomplish the same without having to endure the
> flicker e.g. could i set up a 'beforeclose' routine in another sheet &
> if so, would appreciate a few pointers on how to go about doing this.
>
> Any help much appreciated
> Here's the code I'm using
>
>
> Public Sub worksheet_activate()
> Sheet2.Button102_Click
> Password = "djdog"
> ActiveSheet.Unprotect Password
> Dim tableEnd As Double
> Dim m As Double
>
> ' tableEnd is set to the last row in the spreadsheet.
> ' work backwards from the last row upwards and hide the row if it is
> empty.
>
> tableEnd = Range("a7:k111").SpecialCells(xlCellTypeLastCell).Row
> For m = tableEnd To 1 Step -1
> If RowIsEmpty(m) Then Cells(m, 1).EntireRow.Hidden = True
> Next m
>
> Password = "djdog"
> ActiveSheet.Protect Password, True, True, True
>
> End Sub
>
> Public Sub Button102_Click()
>
> Password = "djdog"
> ActiveSheet.Unprotect Password
> Cells.Select
> Selection.EntireRow.Hidden = False
> Range("A1").Select
> Password = "djdog"
> ActiveSheet.Protect Password, True, True, True
> End Sub
>
> Cheers
>
> Peter
>
>
> --
> peter.thompson
> ------------------------------------------------------------------------
> peter.thompson's Profile:
> http://www.excelforum.com/member.php...o&userid=29686
> View this thread: http://www.excelforum.com/showthread...hreadid=498942
>

try adding this to the top of your macro

Application.ScreenUpdating = False