Remove screen 'flicker' on executing code

P

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
 
M

Mbt6

"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

try adding this to the top of your macro

Application.ScreenUpdating = False
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top