Featured

Read more & download some of my Featured Free Stuff

#back



Preloader Transition in VBA for Excel by Mark Kubiszyn using spin.js library by Felix Gnass

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


Download Preloader:

Preloader.xlsm
Preloader.zip
VBA Projects - Preloader.xls - Blank Preloader Workbook
Preloader UserForm.zip


Preloader Transition in VBA for Excel by Mark Kubiszyn using spin.js library by Felix Gnass


The Preloader Subroutine

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

Using the Preloader in the Real World - Turning Off all Interaction Including Mouse & Keyboard (Method 1)

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

Using the Preloader in the Real World - Switching Workbooks, Worksheets, Cell Drag & Drop (with limitations) and Allowing Data Entry (Method 2)

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

Using An Alternative DoEvents Whilst Running the Preloader

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:

MyDoEvents

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, Fast, Flexible, Searchable List Control with ''Fuzzy'' and Case Sensitive Search Capability

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


Download Filterlist:

Filterlist.zip


Filterlist, Fast, Flexible, Searchable List Control with ''Fuzzy'' and Case Sensitive Search Capability by Mark Kubiszyn

Frostee Theme for Filterlist


Delicious, a very Delicious Drop-down Menu with Theme Changer for Excel 2010 by Mark Kubiszyn using a modified version of the Rollover technique by Jordan Goldmeier

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


Download Delicious:

Delicious.zip


Delicious, a very Delicious Drop-down Menu with Theme Changer for Excel 2010 by Mark Kubiszyn




Timer Dialog

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

Download Trial Dialog:

Trial Dialog.xls


Trial Dialog by Mark Kubiszyn




Spinner

Spinner is a beautiful set of 5 PixelBuddha Preloaders for Excel 2007 to 2016 in 2 sizes that display the status of an indeterminate process. The Spinners are embedded Base64 encoded data displayed in a WebBrowser Control upon a transparent UserForm so that they appear to be floating on the main Excel Application Window. They are faded in and out when displayed using a VBA call to Javascript Functions written into the HTML Markup. You can use them when you are completing some task in VBA

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:
http://www.spreadsheet1.com/flat-animated-preloaders-spinners-for-excel.html


Download Spinner:

Spinner.xlsm
Single Spinner.xlsm
Spinner UserForm.zip
Spinner fade in and out.html


Spinner by Mark Kubiszyn

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




Nanoscopic Progressbar

VBA Progressbar is a nanoscopic Progressbar (on a transparent UserForm) with Spinner (Base64 embedded animated GIF) and Pause, Resume & Abort Controls

The Code in this VBA Project demonstrates the following:
1. How to remove the 3D Border from the WebBrowser Control
2. How to make a UserForm Transparent
3. How to embed a Base64 encoded animated GIF image as HTML Markup and write the data out to a WebBrowser Control
4. How to toggle images ON/Off on a UserForm

Download Nanoscopic Progressbar:

Progressbar.xlsm
Progressbar.xls
Progressbar UserForm.zip


Nanoscopic Progressbar by Mark Kubiszyn

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