Kubiszyn.co.uk
Freebies

All of my Freebies are licensed under an MIT License (MIT)

Nifty Loader Shape Animation for Excel by Mark Kubiszyn

Just one Code Module, 'Loader' is all that is required to create the Loader. The Loader consists of 2 Shapes that are overlaid and then 3 Animations are made. The first is a Rotation of the Foreground Rectangle using an Easing Function. The second is the Fill up of the Background Rectangle. The third is another Rotation of the Foreground Rectangle with a different Easing Function. Put together it's a beautiful thing!


Download Nifty Loader:
Nifty-Loader.xlsm


Nifty Loader Shape Animation for Excel by Mark Kubiszyn




Create a T.O.C. Treelist for Excel - Free Example

This is a lovely example of creating a small T.O.C. (Table of Contents) using my Treelist Software. You can download this Example File for Free to see how the Code works. Then you can either tweak this one if you have the time and inclination or you can purchase Treelist for £2.99 at FastSpring


Download T.O.C. Treelist Example:
TOC-Treelist-Example.xlsm
Treelist Demos.zip (Legacy Version Example Code)


T.O.C. Treelist Example for Excel by Mark Kubiszyn


New! Professional Light Theme for Treelist

Professional Light Theme for Treelist for Excel by Mark Kubiszyn




Filter & Sort a Multi-Series Combo Chart with Stacked Axis for Excel

This is a lovely example of setting up a Chart to track multi-series data whilst allowing it to be filtered and / or sorted


Download Filter & Sort a Multi-Series Combo Chart:
Filter & Sort a Multi-Series Combo Chart.xlsx


Multi-series Chart for Excel by Mark Kubiszyn




Filter, Link & Align & Sort Excel Tables on Seperate Worksheets using Slicers or Autofilters

We can all Filter multiple Pivot Tables using 1 Slicer or multiple Pivot Tables using multiple Slicers - it's easy, right? But what about Filtering, Linking and Sorting Excel Tables NOT Pivot Tables? Well here is an example of doing just that. With my Code you can Filter Excel Tables on different Sheets using Slicers or Table Filters. When you Filter any Table on a Worksheet all of the other Linked Tables will be Filtered exactly the same. You can apply Sorts to different Columns and they will also align in both Tables respectively

Features: use Table Filters or Slicers to align your Tables on different Sheets
Caveats: a Formula should be added to all Sheets to force Calculations. only 1 Table per Worksheet. Only shared Table Fields can be aligned (Filtering different Fields will break the alignment). The Sort can be Ascending/Descending and on multiple Columns, cool eh?


Download Filter Multiple Tables:
Filter Multiple Tables.xlsm
Filter Multiple Tables w Sort.xlsm


Link Multiple Tables (Sheet1) in Excel by Mark Kubiszyn

Link Multiple Tables (Sheet2) in Excel by Mark Kubiszyn




Maintain a Rolling Window of different Time Series Data in a Master Table as a single line in a Table for Combinations Weekly or Monthly

A further rationale is that we want to maintain an Excel Table that has the ability to Slice data, include adjustments but will also keep all Records in the order that they have been updated or added to the Master Table

- consider 3 lists of varying combinations that are updated at any time with any brand new combinations added to the bottom - this means that we can leave adjustments correctly in our Excel Table for many Columns and they will always snyc nicely with all of our Tables
The Window should also be able to roll itself forward ie. At the end of the first Year we would then Roll to the next Year
Data can be repaced or inserted into the Master Table for specific Time Periods
Use the Update Tab to update data to the Master Table on the Data Tab
This Table is then linked to a proper Excel Table on the Table Tab so that we can Slice and compare the data accordingly. Adjustment Cells can be maintained in this dynamic Table as any new combinations will automatically populate at the END of the Table

TO USE - decide on your Time Series data and add/adjust the File accordingly. Then copy a mass load for your Sales History into the Data Tab. After that data can be Copied and Paste into the Update Tab, a Time Series and Dates can be selected and then pressing the 'Update' Button will either INSERT or UPDATE your records using ODBC SQL Batch processes

When creating your Excel Table change the Custom Format for Number Values to #,##0;-#,##0;"—", for Text or Unique Combinations use General;General;"—"


Download Maintain a Rolling Window of different Time Series Data in a Master Table as a single line or combination Weekly:
Rolling Time Series (Week Template).xlsb

Download Maintain a Rolling Window of different Time Series Data in a Master Table as a single line or combination Monthly:
Rolling Time Series (Month Template).xlsb




Simple Alerts for Excel

Simple Alert Messages on a Lightbox for Excel. Works in a variety of Excel Versions. You can choose from a Success, Info, Warning or Danger Alert. You can choose to not display a Close Button or use HTML Formatting on the Message. You can even allow Text to be Copied from a Message. Download the Freebie Workbook below


Download Alerts:
Alerts.xlsm


Alert Messages for Excel by Mark Kubiszyn




Badass! Email Template for Excel

Simple Excel Template in a variety of Colours with Custom Cell Formatting that you can populate and Copy & Paste into an Email that looks great and preserves formatting and Cell Contents. Let your users Copy Excel data as it was designed from Outlook - check it out below


Download Badass:
Badass_v1.xlsx


Badass Email Template for Excel by Mark Kubiszyn




Snatch Extreme Data Pulling for Excel

Simple but powerful Data pulls from Closed and / or Open Workbooks regardless of whether or not the current data has been Saved. You can choose whether to return zeros for Blank Cells or not. You can cancel out any Formula Errors. It's ultra-fast and easy-peasy. Download my Examples and start pulling data now! Tip: for CSV Files use my ODBC Examples detailed below

Pros:
Fast pull, Excel File can be Closed, Open and does not need to be Saved for the data pulls to work
Use in VBA Code with a variable lby wrapping in quotes ie. " & Variable & "
Depending on what you are pulling, you can just use .Formula or even omit this member and it will use the default Value
It doesn't matter what Sheet has focus in the Closed Workbook
Apostrophe's are not brought back from a Closed Workbook
The zero (0) return for Blank Cells issue can be corrected with a small Formula modification - see Example6


Cons:
Cannot be used directly with =Cell or with a dynamic Defined Name
Will return zero (0) for Blank Cells in the Range being pulled (you can use Replace to correct this issue as the data is pulled)
ArrayFormula has a 255 Character limit
Not as flexible or versatile as ODBC where you can pull and join data from multiple Worksheets


Download Snatch:
Snatch_v1.zip






Snatch ODBC Versatile Data Pulling for Excel

Simple or complex, powerful Data pulls using ODBC from Closed and / or Open Workbooks. You can choose whether to preserve Formatting, Auto-fit Columns and Overwrite the data. You can bring back Column Headers as new Named Headers (or Fileds). You can perform INNER JOINS on multiple Worksheets and then Sort the results Ascending or Descending. My Examples let you pick from various different SQL Statements to see the effects of the data pulls immediately. You can also simply add the connection and SQL and then just refresh the data going forward - it's up to you! Download my Examples and check out the powerful, flexibility using ODBC on Excel Workbook Files


Download SnatchODBC:
SnatchODBC_v1.zip






Snatch DAO for Access (Including an Access Table & Query Example)

Simple or complex, powerful Data pulls using DAO from a Closed Access Database. A simple Table data pull and a Query pull are included - download the Examples below


Download SnatchDAO for Access:
SnatchDAO_Access_v1.zip






Aggregate Data at lightening speed by Product, by Product by Location and by Product by Customer using ODBC & SQL

Fast SQL to Aggregate data from a Product, Location and Customer level up to Product, Product by Location and Product by Customer level - download the Example below


Download SnatchDAO for Access:
Aggregate.xlsm






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 - check out one of my download links below for demonstrations of using Preloader in the real world


Download Preloader:
Preloader64bit.xlsm
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




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 Timer Diaolg:
Trial Dialog.xls



Trial Dialog by Mark Kubiszyn




5 PixelBuddha Spinners

Spinnera re 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

Petros Chatzipantazis, Elvis Zem, 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 or download it from the links below


Download Spinners:
Spinner.xlsm
Single Spinner.xlsm
Spinner UserForm.zip
Spinner fade in and out.html
Spinner-v21.xlsm
Spinner World Cup 2018.xlsm



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 Progressbars:
Progressbar.xlsm
Progressbar.xls
Progressbar UserForm.zip



Nanoscopic Progressbar by Mark Kubiszyn



Facebook Loader

This is an old Legacy Loader brought up-to-date a little to run on Excel 2016/2019. It has embedded HTML Markup and demonstrates how to style Transparent UserForm and a WebBrowser Control to hide the Scrollbars

Download Facebook Loader:
Facebook Loader.xls



Facebook Loader by Mark Kubiszyn



Kiosk Full Screen Excel

I can't believe I am now on Version 9 of this Software - I have written so many different variants as standalone Files and AddIns for many Versions of Excel. Here are a couple of my favourites fully working with Excel 2013/2016 64 bit. Kiosk_v2.0.0.3.xlsm or Kiosk_v2.0.0.3.xlam (AddIn) and Version, Kiosk v8.2.xlsm, which is a standalone File. Both allow you to go Full Screen with Excel, hiding everything if required. The older version uses different methodolgy to Hide the Taskbar and Start Button. The newer Version allows you to create Manual Excel Windows of any size - have fun and if you are looking for something even better, check out my new Kiosk v9.xlam AddIn

Download Kiosk Full Screen Excel:
Kiosk_v2.0.0.3.xlsm (run a_main.kiosk, {ESC} to exit Full Screen mode)
Kiosk_v2.0.0.3.xlam
Kiosk_v8.2.xlsm (F4 to exit Full Screen modes)

Older Versions of Kiosk (legacy versions have not been upgraded to 64bit):
Kiosk.xls
Kiosk.xlsm
Kiosk v6.xlam
Kiosk v6.xlsm
Automation Example for Kiosk.xlsm
Kiosk v6 Dev.xls
Kiosk4.1.xlam
Kiosk4.1.xlsm
Kiosk4.1.zip
Kiosk 4.1 AddIn.zip

Kiosk v9 by Mark Kubiszyn




Create a Ribbon Radiobuttons Group with PNG Images. Method 1: GDI Method

There isn't a Radiobutton Group available for the Ribbon, nor is there an easy way of getting transparent images on the Ribbon (you can use BMP images which can be stored and retrived using UserForm Images, however you cannot use the Alpha value to make them transparent when loaded into an Image and .CUR or .ICO can be loaded but not rendered onto the Ribbon). In fact there are a couple of methods that do work. The image retrival method using the GDI is well known and I implement the GDI method here. With my XML and Callback Code you can have unlimited numbers of Radiobuttons that update their respective settings in a Worksheet - ideal if you are planning an AddIn which is what this should be used for. I store my images internally, compressing them within the Worksheet itself using Custom Properties, extract them to the users Temp Folder and then read them back in using GDI Functions with API's. I use purchased transparent, PNG images, however you can substitute these for your own if preferred. All of the Code is given free to load in your own two images


Download the GDI Method:
Ribbon_Radiobuttons_v1.xlsm


Create a Ribbon Radiobuttons Group with PNG Images. Method 2: Activesheet Shapes & Clipboard Method

The second method is to use the Clipboard to Copy the Shape images from a Worksheet and then create the IPictures required for the Ribbon and toggle them in real-time accordingly. The beauty about this method is that you can mess about with the PNG or SVG Icons for Colours etc. and just click on the Ribbon Radiobuttons to update your new Colours - cool eh? The downside is a little loss of quality I think


Download the Activesheet Shapes & Clipboard Method:
Ribbon_Radiobuttons_v1(clipboard).xlsm






Rollover Technique, lots of Rollover examples

The Rollover technique was first demonstrated by Jordan Goldmier, where he found that you could add the =HYPERLINK() function to a Cell and run a VBA Function that would be triggered whenever a Mouse is 'Rolled over' the Cell. My Rollover technique differes only in the fact that I use various methods to prevent the Rollover constantly firing in the background and triggering the Function. I also use the Excel COLUMN() and ROW() Functions for some examples

I demonstrate how you can use Rollovers in Merged Cells, capture Mouse Clicks, use a Custom Cell Format to allow different Rollover Text to be displayed in the Cell, use a Rollover in a Table to highlight each Row, how to reset a Rollover when the Mouse has moved away from the Cells, how to Clear the Rollover effect when the Mouse is moved away from the Cells, a 2-Color Option technique, using the Rollover as both a Rollover and a Hyperlink to a Cell or Webpage and using a Rollover in a Grid. There is an example of a Rollover Toolbar using an embedded 'Icon-Works' Font and an example of Sheet Navigation using an embedded 'Font-Awesome' Font. I include 2 Full Menu System Rollover examples using the 'Glyphyx One NF' Font built for and with the help of Emanuelle Lacroix. I include my Juiicy Template and themes with Typography and numerous Menu Rollover examples. You can find my original Metro Menu Boilerplate Rollover example demonstrating linking to other Cells and using different Text for the Rollovers in a colourful Grid format. I have also included a very quick and dirty Rollover which uses inverse Range intersects to perform the Rollovers - see Very Simple Rollover Tests w Click.xlsm

Check them all out below

Download Rollover Examples:
Rollover Example.xlsm (2 Sheets of Rollover examples)
Rollover.xlsm (alternative technique)
Very Simple Rollover Tests w Click.xlsm (very simple Rollover Range examples that can just copied down)
Rollover Toolbar.xlsbm (embedded Icon-Works Font)
Rollover Toolbar Sheet Navigator using Font Awesome.xlsb (embedded Font-Awesome Font)
Menu.xlsm (embedded Glyphyx One NF Font)
Menu (fullscreen).xlsm (embedded Glyphyx One NF Font, Full Screen version)
Mint & Chocolate Rollover Buttons
Widget Rollover Buttons
UI Element Rollover Buttons
Metro Menu boilerplate.xlsm
Standard Report Schema (Rollover-enabled).xlsm
Business Report Schema (Rollover-enabled).xlsm
Colour Banding with Hyperlink Rollover.xlsm
juiicy.xlsx
Juiicy.xlsm
Juiicy.zip
Juiicy-Themes.zip



Metro Menu Boilerplate by Mark Kubiszyn

A Rollover Menu using an embedded Glyphyx One NF Font




Create a Rollover Button

Why use the Rollover technique? Well because it's lighteningly fast, and I have found it as quick, if not quicker than using a Label MouseOver event - remember Shapes do not have such an event, so this combination of Hyperlink Rollover and Shape produces a lovely Button action. Here are all of my Examples (previously sold, but now totally free to download and use):


Download Rollover Button Examples:
Create a Rollover Button Part 1.xlsm
Create a Rollover Button Part 2.xlsm
Create a Rollover Button Part 3.xlsm
Create a Rollover Button Part 4.xlsm
Create a Rollover Button Part 5.xlsm
Create-a-Rollover-Button.zip






Charts

Here are some Charts that I have designed or created following articles over the Years, that are different from most Charts that you see. The first Chart is a Chart that I created. It is a Sales Combination Chart that uses repeated data series to allow lines and fill to overlap each other in order of precedent (Green takes precedent over Red and Blue takes precedent over Green) and the lighter Colours only show if the lines have precedent. The second Chart was designed by Jon Peltier and I followed his article in order to replicate it (link included in download to the article). I built both 2 x 2 and 3 x 3 Grid Scatter Charts for a Project at work and I include demonstrations of use and how to normalise data. The third Chart is a dynamic Category Chart. It has series that you can toggle on/off using Checkboxes and a Spin Button to reduce the number of visble Weeks


Download Chart Examples:
Custom-Combination-Chart.xlsx
XY Scatter Chart with 2x2 and 3x3 Shaded Grid.xlsx
Category Charts.xlsb



Sales Combination Chart by Mark Kubiszyn

2x2 Grid Scatter Chart Chart by Mark Kubiszyn built from an article by Jon Peltier

3x3 Grid Scatter Chart Chart by Mark Kubiszyn built from an article by Jon Peltier






Demand Planning - Zero Forecast Report

Here is a really simple, but effective Zero Forecast Report with Sortable Formula to show you the 'Demand w Zero F/cast this wk' in first place, then 'Demand w Zero F/cast last 4 wks' next, then 'Demand w Zero F/cast last 3 wks' and finally 'Demand w Zero F/cast last 2 wks'. When the data is refreshed with your own data you can then simply sort the 'Demand v Zero Fcast' Header Largest to Smallest. I made it like this so as to look at the Products without Forecast for Demand in the current Week first when sorted, then to start looking at different Products according to how long a Forecast may have been missing by the longest length. The Formula can exclude any Products with the Text 'pump' in their Product Description (you can change this to anything you may want to exclude from your Product Range). I use this with BEx/BW/BI data every week to see what is selling without a Forecast and for how long this has been happening


Download Zero Forecast Report Example:
Zero Forecast Report.xlsx



Zero Forecast Report for Demand Planning by Mark Kubiszyn