Filled shapes becomes transparent when changing sheet through VBA

Santaflare

New Member
Joined
Jan 4, 2017
Messages
15
Hi,

I've tried to utilize VBA to enable smooth transitions between sheets. When activated a square shape is moved into cell A1 and gradually fills the screen before the sheet is changed. On the next sheet a similar shape is gradually becoming transparent (and later moved out of sight) to present the user with a new "page".

My problem is that even though I've disabled Screen Updating, the filled shapes are briefly transparent as the sheets changes (about a second), which kind of ruin the nice transition effect that I've tried to achieve.

Below is the code for the sheet change, followed by the two I've tried to use to enable the fade. Any ideas as to what might be interfering would be greatly appreciated!

VBA Code:
Sub Change_Sheet()
    Call FadeOut
    Application.ScreenUpdating = False
    Sheets("2").Visible = True
    Sheets("2").Activate
    Sheets("Main Menu").Visible = xlSheetVeryHidden
    Application.ScreenUpdating = True
    Call FadeIN 

End Sub

VBA Code:
Sub FadeOut()
    Dim r As Range

    With ActiveSheet.Shapes("Fade")
    .Top = Range("A1").Top
    .Left = Range("A1").Left
    End With
    
    ActiveSheet.Shapes("Fade").Select
    Selection.ShapeRange.ZOrder msoBringToFront
    Selection.ShapeRange.Fill.Transparency = 1

    For i = 1 To 100
        Selection.ShapeRange.Fill.Transparency = 1 - i / 100
        DoEvents
    Next

End Sub

VBA Code:
Sub FadeIN()
    Dim r As Range

    ActiveSheet.Shapes("Fade").Select
    Selection.ShapeRange.ZOrder msoBringToFront
    Selection.ShapeRange.Fill.Transparency = 1

    For i = 1 To 100
        Selection.ShapeRange.Fill.Transparency = i / 100
        DoEvents
    Next
    
    With ActiveSheet.Shapes("Fade")
    .Top = Range("A500").Top
    .Left = Range("A500").Left
    End With

End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
712
Office Version
  1. 365
Platform
  1. Windows
Hi. As it happens, I've recently used the a similar fading technique when changing sheets; though mine didn't fade out (it would just fade in). It would be triggered by the WorksheetChange event.

When I tried your subroutines, they seemed fine - is the problem that it's too quick? VBA will move through the fading loop quicker if you turn the ScreenUpdating property off, so I'd leave it on if thats the problem. There are other things you could do to slow it down, but I just wanted to first check that I wasn't misunderstanding you.
 

Forum statistics

Threads
1,147,816
Messages
5,743,376
Members
423,790
Latest member
kevinlee_5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top