Introduction

Introducing LightboxAnimatedGif

LightboxAnimatedGif - display a Lightbox with easing and an Animated Gif as a Shape on the ActiveSheet to 'Lock out' user input whilst you complete some VBA Code task OR:
when Excel performs standard Formula Calculation on a Worksheet

LightboxAnimatedGif is the only true method I have found that allows Excel to perform its Calculations whilst displaying some type of Progress Indicator. Other methods that I have written and used over the years work well with VBA Tasks and indeed a couple pause during Calculations and then resume nicely, but always have some discernable jerk (or complete pause) when Excel goes into its non-stoppable Calculation Mode. This Software addresses the issue (where standard animations, threading, worker threads, HTML Web Browser animations etc. do not work) and finds a solution that you can use in your own Projects very easily to display to the end user that Excel is busy calculating. Combine this with an update on the Excel Statusbar or the standard Excel Calculations message on the Taskbar and you have a nice-looking, robust way of updating the end user of any progress. Check out the second image below where it is displaying the Calculation Percentage and the number of running Threads via the Excel Statusbar Messaging as the Loader is displayed and Animated without any pauses or stutters (Obviously check out the Videos to watch the entire animation process)

The Animated Gif as a Facebook Loader Style created using the loading.io website

LightboxAnimatedGif uses a single Shape on a Worksheet, together with a single UserForm that allows you to use a nice easing effect to show and hide a Lightbox overlay. The Software uses a colourful Facebook style Loader that can be modified and downloaded from LoadingIO: Create and / or Download Animated GIF's Want a different Loader or Spinner - no problems just download your chosen animated gif (use Transparent = ON when downloading from loading.io) and then replace the one that you get when you purchase the Software

LightboxAnimatedGif allows you to align the Animated GIF in any number of positions on the Excel Window using the 'MsoAlignCmd' parameter ie. msoAlignTops, msoAlignMiddles, msoAlignCenters, msoAlignBottoms, msoAlignLefts, msoAlignCenters and msoAlignRights

LightboxAnimatedGif for Excel demonstrating using a Lock Shape on a Worksheet to run an Animated Gif during a normal VBA Task

If you like this Project you may also enjoy

LightboxIcon · Lightbox · Lightbox UserForm · Easing Class · ITween Class

Prerequisites

Excel 2016 (32bit or 64bit version 16.0) with an Office 365 Subscription or 365 Apps for Enterprise
Basic to moderate Excel Skills and some VBA skills to copy a Shape and insert a Picture into a UserForm, should you want to change the Icon
Windows PC · NOT a Mac!
No support is provided for customization of this Software

Features

1 File with an Animated Gif included as a Shape already on a Worksheet ready to run
Align the Icon on the Worksheet within the Usable Window Pane. You can change the position of the Shape within the Usable Window Pane via the Code by setting the Vertical and Horizontal Arguments
Examples of use with my own Code - all you need to do is replace this with your own animated GIF Loader or Preloader
Use a Dark or Light Lightbox Overlay
24 easing Functions to display the Lightbox effect
Use with a VBA Task and Excel Calculation (where standard animations, threading, worker threads, HTML Web Browser animations etc. do not work)

Credits

Preloaders generated and downloaded from loading.io
Alternatively, you can download Preloaders from PixelBuddha http://pixelbuddha.net/

Different Preloaders that you can download

The same Code but replaced with a dfferent Preloader. LightboxAnimatedGif for Excel demonstrating an alternative Loader as a Animated GIF Shape on a Worksheet with a dark lightbox effect used alongside Excel's Formula Calculation

The same Code but replaced with yet another Preloader

Installation

Open the file, 'LightboxAnimatedGif.xlsm'. Test out all of the Examples to see how they work. Please read all of the info on the individual Sheets and in this online documentation before you begin to edit and create your own LighboxIcons or before turning to Support

Usage

Using the LightboxIcon Software

Test the Examples in the File
Before you start editing and trying to create your own LightboxIcons, take some time to look at the Examples in the file and the Code behind them. Press the tiny Buttons with the numbers on them to run any examples

Editing the Animated Gif Shape for the 'LightboxAnimatedGif.xlsm' file (Selections and Formatting Panes)
Open the File. The 'Lock' Shape containing the Animated Gif, is, by default set to be Invisible, so you will have to make it visible to change it or format it. In order to select the Shape you can either bring up a context Menu on the Ribbon by right-clicking on the tiny Icon that shows '1' on the Example Sheet and locating it on the Tab that pops up or you can find it on the 'Editing' Group of the 'Home' Tab of the Ribbon - choose 'Find & Select', then choose 'Selection Pane'. To format a Shape you can hover near the Selection and use right-click Format and then change the formatting using the formatting Pane. I like to toggle on a Shape between the Format Pane and the Selection Pane:

Editing the Shape or formatting it is easy - use the 'Editing' Group of the 'Home' Tab of the Ribbon to make it visible

Editing the Shape or formatting it is easy - use the 'Editing' Group of the 'Home' Tab of the Ribbon to make it visible

Now you can use the Context Tab that pops up on the Ribbon 'Picture Format' to format and / or change the Animated Gif to whatever you want. If you change the Shape Name then you will need to adjust this in the Code. Here I am changing the Animated Gif to a dancing Cookie Monster one I found by searching Online:

You can generate and downloaded free Preloaders (Animated GIF's) from loading.io
Or, PixelBuddha http://pixelbuddha.net/

You can easily change the Animated Gif to whatever you want

You can easily change the Animated Gif to whatever you want

You can make the Lightbox Darker by adjusting the Opacity like this:

' change the first Parameter from 180 to 200
Lightbox.FadeIn False, FitToExcel, easeInQuadratic, 200, 0, 16, 120

You can change the position of the Shape within the Usable Window Pane via the Code by setting the Vertical and Horizontal Arguments like this (Left, Center):

Lightbox.SetShapeAlignment ShapeToAlign:=Sheet1.Shapes("Lock"), VerticalPosition:=msoAlignCenters, HorizontalPosition:=msoAlignLefts

You can also combine the positioning with adjusting the Shape Width and Height on the fly like this:

' Left, Center, Slightly Smaller Icon 120px
Lightbox.SetShapeAlignment ShapeToAlign:=Sheet1.Shapes("Lock"), _
                           VerticalPosition:=msoAlignMiddles, _
                           HorizontalPosition:=msoAlignCenters, _
                           ShapeWidth:=120, _
                           ShapeHeight:=120

' Right, Center, Smaller Icon 64px
Lightbox.SetShapeAlignment ShapeToAlign:=Sheet1.Shapes("Lock"), _
                           VerticalPosition:=msoAlignCenters, _
                           HorizontalPosition:=msoAlignRights, _
                           ShapeWidth:=64, _
                           ShapeHeight:=64

' Bottom, Right, Large Icon 256px
Lightbox.SetShapeAlignment ShapeToAlign:=Sheet1.Shapes("Lock"), _
                           VerticalPosition:=msoAlignBottoms, _
                           HorizontalPosition:=msoAlignRights, _
                           ShapeWidth:=256, _
                           ShapeHeight:=256

Adding LightboxAnimatedGif Code to your own Projects
To add this Software or rather incorporate this Software into your own Project, you should open both files, my 'LightboxAnimatedGif.xlsm' file and your own Excel file which should be Macro-enabled ie. *.XLSM or *.XLSB. Then you can go into the VBA Editor and drag the 'mdLightboxAnimatedGif' Code Module and the 'Lightbox' UserForm Code Module into your own file. The 'Lock' Shape Icon used by the Animated Gif by default is set to be Invisible, so you will have to make it visible to change it or format it. In order to select the Shape Lock Icon you can either bring up a context Menu on the Ribbon by right-clicking on the tiny Icon that shows '1' on the Example Sheet and locating it on the Tab that pops up or you can find it on the 'Editing' Group of the 'Home' Tab of the Ribbon - choose 'Find & Select', then choose 'Selection Pane'. Copy the 'Lock' image into your own Project into the Sheet where you will run the Code from. Now you can change the Animated Gif here if you want to one of your own Gif's (see the section above on how to do this). Link up my test example Subroutine 'Example1' or 'Example2' and then modify the Code to run your own Macro, VBA Code or Calculations for Formula. You don't have to make the Animated Gif Shape invisible - once the Code has ran once it will set it to be invisible and hide it. That's it!

Where to modify the VBA Code
You modify the Code where I have marked ' your task goes here!'

' Example2, demonstrates using a Lightbox and an Animated Gif as a Shape on the ActiveSheet whilst Excel is calculating
Public Sub Example2()

   ' align the Icon, display our Icon and show the Lightbox effect
   ' we use the Sheet1 CodeName here but you could use the Sheet Name
   Lightbox.SetShapeAlignment ShapeToAlign:=Sheet1.Shapes("Lock"), _
   VerticalPosition:=msoAlignMiddles, _
   HorizontalPosition:=msoAlignCenters, _
   ShapeWidth:=120, _
   ShapeHeight:=120
   
   Sheet1.Shapes("Lock").Visible = True
   ' you have many options for the Lightbox effect in, using my easing functions included with the Software
   Lightbox.FadeIn False, FitToExcel, easeInQuadratic, 180, 0, 16, 120    ' make the Lightbox Darker by setting the 180 to 200

   ' your task goes here!'
   
   ' this was the task i did in Excel using VBA before I added in the LightboxAnimatedGif Code!
   ' it simply copies some formula down a 'large' Range and then changes it to Special Values and the Font Colour to Automatic
   ' now, when Excel calculates the formula, the Facebook Style Loader will continue to animate even whilst the Application Threads are all tied up doing the recalcs
   Dim LastRow As Long
   With Sheet1
      .Select
      .Range("C27:D1048576").ClearContents
      LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
      .Range("C25:D25").Select
      Selection.Copy
      .Range("C27").Select
      ActiveSheet.Paste
      Application.CutCopyMode = False
      Selection.AutoFill Destination:=.Range("C27:D" & LastRow)
      .Range("C27:D" & LastRow).Select
      Selection.Copy
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Application.CutCopyMode = False
      .Range("C27:D" & LastRow).Select
      .Range(Selection, Selection.End(xlDown)).Select
      With Selection.Font
         .ColorIndex = xlAutomatic
         .TintAndShade = 0
      End With
      .Range("C25").Select
   End With

   ' notify the user
   Application.DisplayStatusBar = True
   Application.StatusBar = "Task complete OK"
      
   ' hide our Icon and remove the Lightbox effect
   ' we use the Sheet1 CodeName here but you could use the Sheet Name
   Sheet1.Shapes("Lock").Visible = False
   ' you have many options for the Lightbox effect out, using my easing functions included with the Software
   Lightbox.FadeOut easeInQuadratic, 180, 0

End Sub

Misc Notes
So in my Code example I have decided to try to handle the Escape Button or CTRL+Break so noone can stop the Code running. You don't have to do this if you don't want to, just comment out the line. I aslo add in some extra Screen positioning Code to tidy up the user experience as the LightboxIcon begins. So for the SHAPE file in reality you only need a few lines of Code to align the Shape and to turn it on and off like this:

' align the Icon, display our Icon and show the Lightbox effect
' we use the Sheet1 CodeName here but you could use the Sheet Name
Lightbox.SetShapeAlignment Sheet1.Shapes("Lock"), msoAlignMiddles, msoAlignCenters
Sheet1.Shapes("Lock").Visible = True
' you have many options for the Lightbox effect in, using my easing functions included with the Software
Lightbox.FadeIn False, FitToExcel, easeInQuadratic, 180, 0, 16, 120

YOUR TASK HERE


' hide our Icon and remove the Lightbox effect
' we use the Sheet1 CodeName here but you could use the Sheet Name
Sheet1.Shapes("Lock").Visible = False
' you have many options for the Lightbox effect out, using my easing functions included with the Software
Lightbox.FadeOut easeInQuadratic, 180, 0

Screen Shots

LightboxAnimatedGif for Excel demonstrating using a Lock Shape on a Worksheet to run an Animated Gif during a normal VBA Task and a 'Light' Lightbox

LightboxAnimatedGif for Excel demonstrating using a Lock Shape on a Worksheet to run an Animated Gif and a 'Light' Lightbox whilst Excel Calculates the Worksheet as it Copies Data. You can see it is also displaying the Calculation Percentage and the number of running Threads via the Excel Statusbar Messaging

LightboxAnimatedGif for Excel demonstrating using a Lock Shape on a Worksheet to run an Animated Gif whilst Excel Calculates the Worksheet as it Copies Data. You can see it is also displaying the Calculation Percentage and the number of running Threads via the Excel Statusbar Messaging

LightboxAnimatedGif for Excel demonstrating an alternative Loader as a Animated GIF Shape on a Worksheet with a dark lightbox effect used alongside Excel's Formula Calculation

LightboxAnimatedGif for Excel demonstrating yet another Preloader as a Animated GIF Shape on a Worksheet with a dark lightbox effect processing a VBA task

FAQ

Q. When I resize my Excel Window, scroll down and press Example 2, the Loader is not visible in the Window?
A. This is due to the couple lines of Positioning Code I added below. Simple comment out this Code or remove it completely. It may be in both examples and at the top and bottom of the Code

' screen positioning
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1

Q. Can I use this technique in the Private Sub Worksheet_Calculate() Event?
A. Unfortunately not, no, because this Event is raised AFTER Excel Calculation has finished! You can however use VBA to begin your process and anything that Excel then Calculates will not effect the Loader or Preloader that is displayed and it will remain as smooth as a custard tart

Videos

Caution: Music in Videos 1 and 3 may contain Explicit Lyrics, please listen at your own risk!

1. This is a video of the LightboxAnimatedGif in action. Music is '6 Shots - NEFFEX' · view the LightboxIcon video on YouTube

You will note, that the Loader continues to animate smoothly even during Example2 when Excel takes full control of the Calculations - check out the Statusbar if you don't believe me as it displays the Calculation in percentage using the internal messaging system. The Screen Recorder does not do the Software justice - see Video 2. for an alternative recording without sound that does below --v

2. This is a video of the LightboxAnimatedGif using an alternative Free Preloader (not in the File, you need to download this and change the Animated Gif yourself!). I recorded this in alternative recording Software so that you can see there is NO slowing dwon of the Loader in normal use (unfortunately I could not record my Sound!). View the LightboxIcon video on YouTube

3. This is a video of the LightboxAnimatedGif using a Nicolas.gif Preloader Gif from PixelBuddha http://pixelbuddha.net/ (not in the File, you need to download this and change the Animated Gif yourself!). Any glitches in the Video is down to the recording Software whilst Excel is Calculating as it runs seemlessly on a PC or Laptop during normal use. View the LightboxIcon video on YouTube

4. This is a video of the LightboxAnimatedGif using a 'Disk-1s-200px.gif' Preloader Gif (not in the File, you need to download this and change the Animated Gif yourself!). Any glitches in the Video is down to the recording Software whilst Excel is Calculating as it runs seemlessly on a PC or Laptop during normal use. Music is 'No Filter - NEFFEX' · View the LightboxIcon video on YouTube

Support

Please remember you have purchased very affordable Software and you have not paid for a full-time Software design agency - I am but one man. Occasionally I may help with small tweaks, but these requests will be put on a much lower priority due to their nature. You have not PAID for Support, Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful

Support (limited) for my Software includes

Responding to questions or problems regarding the Software and its features
Fixing valid (replicated) bugs and reported issues for the VERSION I HAVE WRITTEN

Software support does not include

Customization and installation services
Support for third party software or ANY kind of development whatsoever

Before seeking support

Make sure your question is a valid Software Issue and not a customization request
Make sure you have read through the FAQ's, online documentation and any related video guides before asking support on how to accomplish a task
Ensure that you access to the VBOM is allowed and that Macros can run in Excel
Make sure to provide 'proof of purchase' and state the name / version of the Software that you are having issues with when requesting support by Email or via Facebook

How to get Support

Contact Mark Kubiszyn on the Email address provided when you purchased the Software, including the Order Number
...or Contact Mark Kubiszyn via our Facebook Page - remember to be patient, if there has been an issue with your download, Mark will always respond within 48 hours and will Email you the File directly if neccessary or via Messenger. For other issues the response time may be considerably longer and I may choose to respond to specific questions only (as is my right), depending on what has been asked

Future Builds

* No ideas as yet!

Bug Fixes

* There are currently no bugs identified

Credits

Preloaders

Preloaders generated and downloaded from loading.io
Alternatively, you can download Preloaders from PixelBuddha http://pixelbuddha.net/

Changelog

You can find the version history in the Code Module for any Macro-enabled Software or read more information on the status of each release here
- the latest Version including a description of any changes made is always shown first


12.12.2022 - (Version 1)

General release of LightboxAnimatedGif