Reduce Flickering using Application.ScreenUpdating

[Xiazer]

I am trying to mimic a "popup" in excel by setting a group of
buttons/labels visible to true or false. My problem comes when I click
on a button and that button runs a function to show/hide a the specific
group. here's an example

Private Sub togShowgrp_Click()
If togShowgrp.value = true then
showgrp(TRUE)
else
showgrp(FALSE)
End If

end sub

Function showgrp(Show as Boolean)
Application.ScreenUpdating = False
lblBackgrd.visible = show
cmdPlus1.visible = show
cmdMinus1.visible = show
Application.ScreenUpdating = True

end Function


These buttons/labels are on top of a large label already and whenever
the button is clicked the screen flickers (all the pictures and
labels/buttons on the spreadsheet disappear for a second) My Question
is, Is there another way to show the group without the screen flicker
using something like Application.ScreenUpdating but on a smaller level,
or use the ScreenUpdating a bit better to avoid the annoying flicker?
Thanks in Advance.

 

[NickH]

Xiazer,

My first thought is 'why is showgrp a function?'. Without actually
testing the code I'm surprised it works at all.

The purpose of a function is to return a value based on arguments
passed to it. It is not normally capable of making changes to the GUI.

Try simply changing the Function keyword to Sub and see if that fixes
the problem.

HTH,
NickH.

 

[NickHK]

Xiazer,
I would guess it is because Excel has to redraw all the object on the
graphics layer when any of them are hidden/shown.
If there are many objects, then this redraw becomes noticeable.

Could you use a modeless userform that floats over the sheet instead ?

NickHK

 

[Ivan Raiminius]

Hi Xiazer,

you may get better result if you don't use application.screenupdating
at all.

Also speeding up your code could avoid screen flickering (you can speed
up your code by showing/hiding directly in Private Sub
togShowgrp_Click(), not by calling Function showgrp. Anyway, you don't
use its return value).

Regards,
Ivan