Preloader is a beautiful CSS animating fade in/out spinner written in VBA for Excel that runs in a WebBrowser control on a non-modal transparent Userform with rounded corners, framed Window or a thick bordered Window with Caption. It can be ran as a simple Transition with a fade-in duration, display duration and a fade-out duration or it can be used as a wait animation while running some task in your Code. Please read the Preloader Documentation on the link above and use the MyDoEvents alternative DoEvents when using Preloader - see the Code at the bottom of this article or check out one of my download links below for demonstrations of using Preloader in the real world
The Preloader Subroutines are extremely versatile and powerful. You can run a Preloader using minimal Code calling just the Preloader.Initialise() and Preloader.Transition() Subroutines without passing any values. This works because the Subroutines are initialised each time the Code is ran with default values but you also have the ability to set any parameter by prefixing a Named Argument with the word "Control" to set that argument regardless of whether you pass it or not in the Subroutine itself. Here is an example of running a simple transition setting the Preloader Window to a Dialog Window and the Preloader background colour to rgbPumice
Private Sub Example() ' set the preloader window type Preloader.ControlWindow = DialogWindow ' initialise the preloader Preloader.Initialise ' set the preloader colour Preloader.ControlColour = rgbPumice ' run the preloader transition Preloader.Transition End Sub
Of course in the real world you will be running a Preloader Transition while some Code or task is running. To allow you to do this correctly there are now two preferred methods of running the Preloader Transition. The first is to disable interaction entirely although you are still free to move the Preloader if you display it in a Dialog Window. This is a good method if you do not want anyone messing about while your Code or task is running - you may want to switch Workbooks, Worksheets and write out some data without any user interaction with Excel. The downside is that all open Workbooks in the current Excel Instance will be locked up until your Code or task is complete. The Code for method 1 uses the Preloader.Initialise(), Preloader.Start() and Preloader.Halt() Subroutines. If you handle your own errors you should make a call to Preloader.ErrorHandler before exiting or ensure that events and Interaction are enabled.
Private Sub Method1() ' setup external error handling Preloader.ControlDebug = Screen On Error GoTo MyErrorHandler ' exit if a preloader is already running If Preloader.ControlRunning = True Then Exit Sub ' turn off ctrl+break, alt+f4 (disabled by default) & Interaction Preloader.ControlDisable = CTRLBreak + Interactive ' initialise the preloader Preloader.Initialise Width:=148, _ Height:=148, _ WindowCornerRoundness:=6, _ ScreenPosition:=ApplicationCenter, _ Window:=DialogWindow, _ WindowMessage:="processing, please wait..." ' start the preloader Preloader.Start FadeinDuration:=1500, _ Colour:=rgbPumice, _ Lines:=9, _ LineLength:=24, _ LineWidth:=5, _ LineColour:=rgbWhite, _ InnerCircleRadius:=16, _ CornerRoundness:=1, _ Speed:=1, _ Trail:=86 ' run a dummy task, we will generate an index & some random numbers on another Worksheet Dim i As Long, cell As Range Sheets("task").Columns("a:b").ClearContents Sheets("task").Select For Each cell In Sheets("task").Range("a1:a10000").Cells DoEvents i = i + 1 cell.Activate cell.Value = i: cell.Offset(0, 1).Value = Int((2 ^ 16 - 2 ^ 1) * Rnd() + 2 ^ 1) Next cell ' stop the preloader, pass optional fadeout interval Preloader.Halt FadeoutDuration:=1500 Exit Sub MyErrorHandler: ' external error handler, this will reset the running var, turn on cell drag & drop ' enable events & turn on interaction + dump any errors out to the user (Screen) Preloader.ErrorHandler End Sub
The second method of using the Preloader Transition to allow full interaction is to skip any errors by using
"On Error Resume Next" before a Call to "DoEvents".
Please Note: there is still a way to crash Excel when using the second preferred method of running the Preloader if you don't turn off Cell Drag & Drop (Excel will allow Cell drag, but not a "Move"). If you actually pick up a Cell's Contents and then "Move" them while the Preloader is running Excel may crash unexpectedly due to DoEvents - I do not have a workaround for this issue other than to say it appears to be an Excel issue and has been documented to Microsoft. The issue is that should Excel crash for any other reason while a Preloader is running with Cell Drag & Drop turned off, Excel will not automatically reset this setting leaving your users without the ability to perform a Cell Drag & Drop when they open a new Workbook; so you need to bear this in mind.
Private Sub Method2() ' turn off ctrl+break & alt+f4 (disabled by default) Preloader.ControlDisable = CTRLBreak 'disable drag & drop if you want to prevent excel crashing Application.CellDragAndDrop = False ' initialise the preloader, pass any required parameters Preloader.Initialise 148, 148, , ApplicationCenter, DialogWindow, "please wait..." ' // setup the preloader colour & fadein duration Preloader.ControlColour = rgbPumice Preloader.ControlFadeinDuration = 1500 ' start the preloader, pass any required parameters Preloader.Start , , 9, 24, 5, rgbWhite, 16, 1, 1, 86 ' dummy task, your code would go here... Dim task_a As Long For task_a = 1 To 400000 ' skip any errors On Error Resume Next DoEvents Next task_a ' stop the preloader Preloader.Halt 1500 ' enable drag & drop Application.CellDragAndDrop = True ' clean up, user may leave cell in edit mode Preloader.ErrorHandler End Sub
This alternative DoEvents routine written by Nir Sofer allows you to give focus back to Excel and refresh the Preloader more often than the regular DoEvents routine - it is now my favourite method of running the Preloader. Don't be afraid to add lots of Calls to MyDoEvents in your own Code and remember to add them into any Subroutines or Functions that you may also use.
Add the Code below into any Code Module including the API declarations. You can call this routine any time by entering:
Private Type POINTAPI x As Long y As Long End Type Private Type MSG hwnd As Long message As Long wParam As Long lParam As Long time As Long pt As POINTAPI End Type ' // defines API's used by the alternative DoEvents process Private Declare Function PeekMessage Lib "user32" Alias "PeekMessageA" (lpMsg As MSG, ByVal hwnd As Long, ByVal wMsgFilterMin As Long, ByVal wMsgFilterMax As Long, ByVal wRemoveMsg As Long) As Long Private Declare Function TranslateMessage Lib "user32" (lpMsg As MSG) As Long Private Declare Function DispatchMessage Lib "user32" Alias "DispatchMessageA" (lpMsg As MSG) As Long Private Const PM_REMOVE = &H1 ' ============================================================================================================================================================= ' ## MyDoEvents ' an alternative DoEvents process to use with Preloader & Code ' written by Nir Sofer, http://nirsoft.mirrorz.com ' ============================================================================================================================================================= Private Sub MyDoEvents() Dim CurrMsg As MSG ' // the following loop extract all messages from the queue and dispatch them ' to the appropriate window Do While PeekMessage(CurrMsg, 0, 0, 0, PM_REMOVE) <> 0 TranslateMessage CurrMsg DispatchMessage CurrMsg Loop End Sub
Filterlist is a UserForm written in VBA for Excel. It retrieves Key/Item data from a 2 Column contiguous Worksheet Range providing the end user with a fast, flexible, searchable list control with ''Fuzzy'' and case sensitive search capability. Filterlist displays the values in a list as either Keys, Items, Keys & Items or Items & Keys. It also allows you to sort (and persist the sort) in the list either ascending or descending. You can do this with a full list or any part of a filtered search list
Filterlist can be linked to a specific Cell in your Worksheet so that when an end user presses enter or makes a Selection in the list the Cell reflects either the Key or Item value. You can also link the Cell to the search term entered in the search Edit box - choosing the search term option allows you to capture the Key or Item if an end user makes a Selection in the list or the value in the search Edit box, whichever comes first. This is particularly useful when you want to use the search term to filter an Autofiltered Range
Delicious is a Drop-down Menu that highlights a list of Menu Items when you hover over a Menu Header or an individual Menu Item. It does this by Conditional Formatting using my modified version of the Rollover technique by Jordan Goldmeier. Delicious has been designed for Excel 2010 but works in newer versions of Excel too
Delicious has an expandable, movable and configurable Menu system
featuring Menu Headers and Menu Items that exhibit a Rollover as the Mouse is moved across them. When they are in a Rollover
state, they become Hyperlinks for jumping to other locations or for using in VBA to run Code. As the Mouse
is moved away the Menu Headers and Menu Items all lose focus and the Drop-down Menu is reset
Delicious also features a theme changer that can apply an entire theme to the Drop-down Menu quickly. You can change the Fill, Font Colour, Font Size, Font Style (Bold), Font Face and add Thin Borders to create some pretty delicious effects. For Juiicy fans there is also a Juiicy style theme
Please Note: Delicious has been designed and tested in Excel 2010 for Windows 7. Conditional Formatting that link to other Worksheets is not allowed in earlier versions of Excel. Delicious uses a link in Conditional Formatting that links to the setup Worksheet. Unfortunately using a Defined Name is not an option that works either. As a workaround you will find a file called Delicious-2007-version.xlsm included in the Delicious Project Files
Here is a little Trial Dialog I made in 2012. The Dialog cannot be closed until the countdown has expired. Only then will the 'OK' Button be enabled. The small Red Cross is hidden but a workaround is given should you want to dislpay it. CTRL+Break is disabled by the Code. You can pass in a Message when you call the UserForm like this:
Public Sub TrialDialogDemo() TrialDialog.Tag = "(ANNOYING NAG MESSAGE):" TrialDialog.Show End Sub
The guys at Spreadsheet1 have taken the Spinner Project and improved the data storage, added additional Spinners and created a visual demo of Flat Animated Loaders for Excel.
You can view it at:
Creating a Spinner is easy. You can choose from 5 different Spinners in two sizes 64x64 pixels and 128x128 pixels. The Code below demonstrates how to select a Spinner and fade it in and out while running a simple task. You can use DoEvents or the alternative Spinner.MyDoEvents to pass messaging back to the main Excel Application whilst running your task
' // fade the Spinner in Spinner.FadeIn PixelBuddhaSpinner:=NewtonsCradle128x128, _ Duration:=1500, _ Disable:=CTRLBreak + MouseKeyboard, _ Position:=ApplicationCenter, _ WaitForDuration:=3000 ' // complete some task ' make sure that your task is long enough to allow the Spinner to Fade in before you start fading it out ' you can adjust the Fade in & Fade out timings accordingly or increase the WaitForDuration to match ' the Fade in time ' use the alternative DoEvents Function which allows better processing control Spinner.MyDoEvents ' // or the regular DoEvents DoEvents ' // fade the Spinner out If Spinner.Running Then Spinner.FadeOut Duration:=1500
VBA Progressbar is a nanoscopic Progressbar (on a transparent UserForm) with Spinner (Base64 embedded animated GIF) and Pause, Resume & Abort ControlsThe Code in this VBA Project demonstrates the following:
You can create a Progressbar very easily by importing the UserForm Code Modules (see the links above) into a New or existing Project and adding the Code below (make sure that you don't call your Subroutine 'Progressbar' or you will recieve an error)
Sub CreateProgressbar() Dim Status As StatusType ' // initialise Status = Progressbar.Initialise(Disable:=CTRLBreak + Screen, _ Control:=PauseResume + Abort, _ Spinner:=True, _ WindowBorderSize:=1, _ WindowBorderColour:=rgbDefault, _ BarSize:=1, _ BarColour:=rgbDefaultBlue, _ BarBackgoundColour:=rgbDefaultGrey) If Status <> Initialised Then Exit Sub ' // display Status = Progressbar.Display(ApplicationCenter) If Status <> Running Then Exit Sub ' // update Dim task As Long For task = 1 To 5000 Progressbar.MyDoEvents Progressbar.Update task, 5000, "processing... " Next task ' // reset Progressbar.Reset End Sub