Introduction

Introducing LightboxIcon

LightboxIcon - display a Lightbox with easing and an Icon as a Shape or Animated Shape on the ActiveSheet or as a Picture on a UserForm to 'Lock out' user input whilst you complete some VBA Code task

LightboxIcon is a great way to 'Do Something' while a Task is running, providing the user with a visible process during which they may expect to pause to let Excel complete some Calculations or Task. Combine this with an update on the Excel Statusbar and you have a nice-looking, robust way of updating the end user of the progress. You can also animate your Shape for added effect

LightboxIcon is a single UserForm that allows you to use a nice easing effect to show and hide a Lightbox and display an Icon Shape which is a Lock for this Software. There are 2 files in the Zip archive. 1 that displays a Shape allowing you to run VBA Code on the ActiveSheet and another that displays a Picture of the same Icon on a UserForm allowing you to run VBA Code whilst selecting different Sheets. The Icon used is a 'Lock' Icon. You can download the Lock Icon and many more from IconFinder. They are all designed by Nick Roach: Circle Icons 1

From Version 1.2 I also include various Workbooks that use a 'CalculatedLightboxIcon' with a 'No Entry' style Circle Icon to display to the user if Excel Calculates everything or if a specific Range is modified and even where Cells are cleared, deleted or Columns and Rows are attempted to be deleted - check them out in the Usage Section!



If you like this Project you may also enjoy


LightboxAnimatedGif ·  Lightbox ·  Lightbox UserForm ·  Easing Class ·  ITween Class

Prerequisites

  • Excel 2016 (32bit or 64bit)
  • 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

  • 2 Files, 1 to use with a Shape on a Worksheet and another to use with a Picture on a UserForm
  • Align the Icon on the Worksheet within the Usable Window Pane
  • Examples of use with my own Code - all you need to do is replace this with your own
  • Use a Dark or Light Overlay - dark works best with a Worksheet Shape Icon and light works best with a UserForm Picture Icon
  • 24 easing Functions
  • Animate your Worksheet Shape to let the user know that you are still processing something in Excel using an 'Egg Timer' Icon
  • Version 1.2 includes additional files that utilise the LightboxIcon (Picture) method and through the use of a UDF can be used when Excel Calculates, Cell Entry on specific Ranges and Data Prevention / Protect the Integrity of a Worksheet






Installation

Right-click on the Zip archive and extract the files to your PC. Open all of the files, 'LightboxIcon (Shape).xlsm', 'LightboxIcon (Animated Shape).xlsm' and 'LightboxIcon (Picture).xlsm' (Please note: from version 1.2 additional files have been added to this Software - see the Usage Section for more information). Test out the Examples in each file to see how they work. In order to select any of the Shapes or Graphic Objects you must first Unprotect the Sheet. 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 Files
Before you start editing and trying to create your own LightboxIcons, take some time to look at all of the Examples and the Code behind them. Press the tiny Buttons with the numbers on them to run any examples




[LightboxIcon (Shape).xlsm] Editing the Lock Icon Shape for the 'LightboxIcon (Shape).xlsm' file (Selections and Formatting Panes)
Open the File. The 'Lock' Shape Icon 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'. 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:



Now you can use the Context Tab that pops up on the Ribbon 'Picture Format' to change the Picture, Image or Icon for the Shape to whatever you want. If you change the Shape Name then you will need to adjust this in the Code. Here I am ready to add a different Image to my Shape - you can download other Circle Icons, designed by Nick Roach here: Circle Icons 1



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





[LightboxIcon (Picture).xlsm] Editing the Lock Icon Shape for the 'LightboxIcon (Picture).xlsm' file
Open the File. Go into the VBA Code Editor ALT+F11 and click on the Lightbox Code Module. A 'Lock' Picture has been loaded into the UserForm that you can change yourself if you want. Insert an image into Excel from your own PC or Laptop. Select the image and press CTRL+C or Copy from the Ribbon. Switch back to the Lightbox UserForm and find the Picture Member (where is says Bitmap). Delete whatever is in the tiny box marked (Bitmap) and then just press CTRL+V or Paste the image using Paste from the Ribbon. Now the image will replace my image and you are good to go. Here I show you the UserForm Picture member ready to Paste my new image into:



Adding LightboxIcon (Shape) Code to your own Projects
To add this Software or rather incorporate this Software into your own Project, you should open both files, my LightboxIcon (Shape) 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 'mdLightboxIcon' Code Module and the 'Lightbox' UserForm Code Module into your own file. The 'Lock' Shape Icon 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. This type of Project is for running the Code from a single Sheet! Now you can change the image here if you want to one of your own images. Link up my test example Subroutine 'Example1' and then modify the Code to run your own Macro. You don't have to make the Shape invisible - once the Code has ran once it will set it to be invisible and hide it. That's it!

Adding LightboxIcon (Picture) Code to your own Projects
To add this Software or rather incorporate this Software into your own Project, you should open both files, my LightboxIcon (Picture) 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 'mdLightboxIcon' Code Module and the 'Lightbox' UserForm Code Module into your own file. Add another Sheet into your Project as you need 2 Sheets (the VBA references 'Sheet2' to make the demonstration. you can change the Code here if you like. Anyhow once done, just run or link up my test example Subroutine 'Example1' and then modify the Code to run your own Macro. That's it!

Where to modify the VBA Code
You modify the Code where I have marked ' your task goes here!' - see below for the SHAPE Code (I do the same for the PICTURE Code)
Public Sub Example1()
   ' with this Code I have decided to try to handle the Escape Button or CTRL+Break so noone can stop the Code running
   ' whilst the LightboxIcon is running.  you may wish to adopt a different strategy
   On Error GoTo Try:
   ' may or may not be required, depending on what you are doing
   Application.EnableEvents = False
   ' screen positioning
   ActiveWindow.ScrollRow = 1
   ActiveWindow.ScrollColumn = 1
   
   ' if required, disable the CTRL+Break Keypress combination
   Application.EnableCancelKey = xlErrorHandler
   
   ' 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 goes here!
   ' replace this Code with your own, whatever you are normally running.  your Code must not change Sheet otherwise the Shape will not be displayed
   ' standard practice when writing VBA is to Copy Ranges or perform Code without selecting every Sheet
   ' see my Screen Shots on on the Online Help for this Project to see what I do using this LightboxIcon technique
   ' ## DEMO CODE ONLY ##
   Dim i As Variant
   '@Ignore HungarianNotation
   Dim iVal As Variant
   Dim rngCell As Range
   Application.StatusBar = True
   '@Ignore ObsoleteWhileWendStatement
   While i < 250
      ' please note I do not normally use a DoEvents, I just run my VBA Code as normal, however sometimes the UserForm screen may go black
      ' as with this example, so I yield to the processor intermittently
      DoEvents
      Randomize
      i = i + Rnd(100) - 0.1
      'RandomEvent
      If i Mod 3 = 0 Or i Mod 5 = 0 Or i Mod 7 = 0 Then GoTo skip
      iVal = Format$(i / 250, "0%")
      Application.StatusBar = "Processing a task... " & iVal
      ' // borrowed from Ejaz Ahmed's Class Progressbar as I liked the technique
      '    modified to stay within a Blue/Pink spectrum of Colours I like ;)
      For Each rngCell In ActiveSheet.Range("B15:E19").Cells
         'On Error Resume Next
         rngCell.Interior.Color = RGB(255 * Rnd(i), 81, 181)
         'On Error GoTo 0
      Next rngCell

skip:
   Wend




   ' use this for any repositioning that you may require after running your code
   ' screen positioning
   ActiveWindow.ScrollRow = 1
   ActiveWindow.ScrollColumn = 1
   Application.ScreenUpdating = True
   ' 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
   Application.EnableEvents = True
   Exit Sub
Try:
   If Err.Number = 18 Then Resume
   Application.EnableEvents = True
   On Error GoTo 0
End Sub





[LightboxIcon (Animate Shape).xlsm] Animating the Icon Shape
So in Version 1.1 I added a new Workbook with an 'Egg Timer' Circle Icon and some VBA to animate the Shape. Open the File. Go into the VBA Code Editor ALT+F11 and click on the 'mdLightboxIcon' Code Module. The Code uses an EventProc, which is a low-level timer callback Subroutine allowing you to animate a Shape on a Worksheet in Milliseconds. The Code that animates the Shape (a simple 45 degree Rotation) is shown below and the EventProc() is entered every 0.1 (1/10 or 100 Milliseconds):
   Sheet1.Shapes("Lock").IncrementRotation 45
And here is the 'Egg Timer' Icon doing what it does best, Rotate! You can change the Icon and the Task that you run as per the previous instructions for the Worksheet Shape LightboxIcon example








[CalculationLightboxIcon.xlsm] Firing the LightboxIcon using a UDF (User Defined Function)
So in Version 1.2 I introduced my CalculationLightboxIcon which allows you to run a LightboxIcon on Excel Calculation using a UDF. Open the File. It uses a CLI() UDF that you add to a single Cell in a Sheet, for example, =CLI(TRUE) to fire on everything entered or if and when Excel Calculates the Sheet or =CLI(TRUE,"B19:B20,B23") to fire on specific Ranges, including selecting from drop-down lists. If set as =CLI() or =CLI(FALSE) then the LightboxIcon will not be triggered

Here is a Screen Shot of the UDF in action as I select from a Drop-down list for Age Range >=51. THe Lightbox is also triggered using the UDF for Cells "B19:B20,B23", so typing Text into Cell "B19" for example will fire the LightboxIcon








[CalculationLightboxIcon (decision switching).xlsm] Firing the LightboxIcon using a UDF (User Defined Function) for different Actions
As an addition to the UDF detailed above I also created a UDF that allows you to switch between a couple of states when deciding when to fire the Lightbox. The default UDF =CLI(TRUE) still remains and the Function is only changed minimally in the VBA

To use the UDF and allow Text to be entered into Cells without firing it, you can use the Code below:
Option Explicit
'Option Private Module

Public Running As Boolean

Public Function CLI(Optional ByRef Switch As Boolean = False, Optional ByRef DirtyRange As String = vbNullString) As Boolean
   Application.Volatile Switch
   ' if we want to use the Lightbox
   If Switch And Not Running Then
      ' if the Optional DirtyRange Parameter is not passed then use the Lightbox
      If DirtyRange = vbNullString Then
         
         ' allows typing in Cell and runs for Delete, Clear etc.
         If Selection.Cells.CountLarge = 1 And Selection.Text <> vbNullString Then
            ' do nothing
         Else
            Lightbox.FadeIn False, FitToExcel, easeInQuadratic, 180, , 16, 120
            ' otherwise if the Range passed as the Optional DirtyRange Parameter is within the Selection we will use the Lightbox
         End If
      
      Else
         ' show the Lightbox effect and display our Icon
         ' you have many options for the Lightbox effect in, using my easing functions included with the Software
         If Not Intersect(ActiveSheet.Range(DirtyRange), Selection) Is Nothing Then _
         Lightbox.FadeIn False, FitToExcel, easeInQuadratic, 180, , 16, 120
      End If
   End If
   Running = True
   CLI = Switch
End Function
To use the UDF and fire on typing into Cells but not for Delete Rows you can use the Code below:
Option Explicit
'Option Private Module

Public Running As Boolean

Public Function CLI(Optional ByRef Switch As Boolean = False, Optional ByRef DirtyRange As String = vbNullString) As Boolean
   Application.Volatile Switch
   ' if we want to use the Lightbox
   If Switch And Not Running Then
      ' if the Optional DirtyRange Parameter is not passed then use the Lightbox
      If DirtyRange = vbNullString Then
         
         ' runs on typing in Cell but not for Delete, Clear etc.
         If Selection.Cells.CountLarge <> 1 And Selection.Text = vbNullString Then
            ' do nothing
         Else
            Lightbox.FadeIn False, FitToExcel, easeInQuadratic, 180, , 16, 120
            ' otherwise if the Range passed as the Optional DirtyRange Parameter is within the Selection we will use the Lightbox
         End If
      
      Else
         ' show the Lightbox effect and display our Icon
         ' you have many options for the Lightbox effect in, using my easing functions included with the Software
         If Not Intersect(ActiveSheet.Range(DirtyRange), Selection) Is Nothing Then _
         Lightbox.FadeIn False, FitToExcel, easeInQuadratic, 180, , 16, 120
      End If
   End If
   Running = True
   CLI = Switch
End Function





[CalculationLightboxIcon (worksheet protection via VBA).xlsm] Firing the LightboxIcon using a UDF (User Defined Function) and Protect Data Integrity
To use the LightboxIcon as well as protecting data integrity, you can lock out Ranges for example I locked out an entire Column which will protect anything entered within the Column and also from removal of the Column itself or any Rows that intersect the Column, which is actually all of them! The Code also prevents Double-click of the Cell into Edit Mode within the Protected Range and allows you to use Data Validation to further restrict the Cell entries. It also uses a 'No Entry' Circle Icon

To Protect a Range in the 'Private Sub Worksheet_Change(ByVal Target As Range)' Event Handler in the Sheet Code Module, change the String here from "B:B" to "A1:B10,C:C" etc. to protect Cells "A1:B10" and Column "C":
   ' change this
   If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
   ' to this
   If Not Intersect(Target, Me.Range("A1:B10,C:C")) Is Nothing Then
Here is a Screen Shot of the UDF in action Protecting Column "B" from being Deleted, allowing you to type in Text but not Clear it again or Delete it and with Cell "B14" having an additional 'Custom' Data Validation on it like this:
   =B14 <> B14









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

LightboxIcon for Excel demonstrating using a Lock Shape on a Worksheet (Dark)

LightboxIcon for Excel demonstrating using a Lock Picture on a UserForm (Light)

LightboxIcon for Excel demonstrating using an Animated Egg Timer Icon on a Worksheet (Dark)

CalculatedLightboxIcon for Excel demonstrating using a 'No Entry' Icon / Picture on a UserForm used to Protect the Integrity of a Worksheet

FAQ

* There are no frequently asked questions for LightboxIcon


Videos

This is a video of the 2 Files in action - Please note: on the Picture File the Screen goes white at the end, this is due to the recording Software, not the LightboxIcon Software as this doe not do this normally! Music is 'Breatha - josh pan' · view the LightboxIcon video on YouTube

This is a video of the Animated Icon in action for version 1.1. Music is 'Breatha - josh pan'

This is a video of the CalculationLightboxIcon in action for version 1.2. I attempt to Delete the Column "B" and Clear or Delete the Cell contents. I also attempt to Delete Rows on the Sheet. I type Text into a Cell in Column "B" and demonstrate the additional Data Validation on Cell "B14". Music is '6 Shots - NEFFEX' - warning, may contain explicit lyrics! · 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

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

14.12.2022 - (Version 1.2)

Added in some additional files that utilise the LightboxIcon (Picture) method and through the use of a UDF can be used when Excel Calculates, Cell Entry on specific Ranges and Data Prevention. I have named these files CalculatedLightboxIcon (a branch that was to become another Project but is now included within the LigthbpxIcon Software)


08.12.2022 - (Version 1.1)

Modified the SetAlignment() Subroutine. The Width was not configured and the Subroutine did not work for say Left, Center or Right, Center. Added in the additional Optional Parameters of Width and Height so that you can configure these settings on the fly as well giving more flexibility over the position

Added another Workbook demonstrating how to animate a Shape while running a Task. This uses a robust low-level TimerProc or EventProc that can be set to Rotate the Shape on the Worksheet at various intervals


12.10.2022 - (Version 1)

General release of LightboxIcon