All of my free Software, legacy work, VBA Projects, Excel Workbooks, Tools and Code downloads are now released and licensed under an MIT License (MIT). Please Note: all of the work found here is not supported. You can read about any new releases or follow my work on Facebook. If you have used any of my other work and wish to donate, you can do so on my Donate Page

Use the Quick Search to find a piece of Software or Workbook Project to download. You can Sort the list either ascending or descending by Name, Date or Category:







Conjunction Tool to Identify Word Unions in Comparable Data Lists

[Back to Search]

A Conjunction Tool to Identify Word Unions in Comparable Data Lists. The Code features a Progressbar and allows you to rezize the Excel Window whilst running. You can download the Tool from the link above - please note this Code was written before 2012 and as such needs revision to speed up the process. You can stop the process using CTRL+Break and then press End
Conjunction Tool.xlsm (408 KB)





Display, List and Unhide Hidden Named Ranges

[Back to Search]

Display, list and delete hidden Named Ranges. Download the Workbook, copy any of the DisplayHiddenNames(), ListHiddenNames() and DeleteHiddenNames() Subroutines into your own Workbook and run to see the effects:
Display, List & Unhide Named Ranges.xlsm (16 KB)





Facebook Loader

[Back to Search]

This version of my Facebook Loader is built with HTML Markup including the Base64 image stored in an Array() in the UserForm Code Module. It displays an animated image that can be used to wait for a defined period of time:

Facebook Loader

Facebook Loader.xls (91 KB)

Base64 Encoded HTML Files:
Ajax Orange Loader.htm
Orange Spinner.htm
Blue Bubble Loader.htm
Spinning Disc.htm
Super Spinner.htm
Large Indicator.htm
Facebook Style.htm
Facebook Loader.htm
Roller.htm

Example Loaders with Base64 Encoded HTML Files:
Orange Ajax Loader.xls (101 KB)
Orange Spinner Loader.xls (146 KB)
Blue Bubble Loader.xls (108 KB)





Tile Worksheet

[Back to Search]

Download my Background Tiles from the link below. Extract them to your PC from the ZIP archive. Open Excel. Select the Page Layout Tab on the Ribbon. In the Page Setup Group Click the Background Button. In the Sheet Background Dialog Browse to locate one of my 15px by 15px Tiles. Click the Insert Button to add the Image as a Worksheet background. Select the View Tab on the Ribbon. On the Show Group uncheck the Gridlines Checkbox to reveal your Image as a seamless background. You can also uncheck the Headings Checkbox to hide the Row and Column Headings if you like

Tile Worksheet.xlsx (17.1 KB)
Background Tiles.zip (449 KB)

How to Remove the Image
To remove the Image select the Page Layout Tab on the Ribbon. In the Page Setup Group Click the Delete Background Button





Minimal Excel Workbooks

[Back to Search]

There are 3 different Workbooks to download, a Clean Single Sheet Minimalist Excel Workbook, a Minimalist Survey Workbook with Single Ribbon Tab, Group & Button and a Clean Reading View Workbook:

Clean Single Sheet Minimalist Excel Workbook.xlsm (15.5 KB)
Minimalist Survey Workbook with Single Ribbon Tab, Group & Button.xlsm (17.9 KB)
Clean Reading View.xlsm (23.7 KB)





Colour Pickers

[Back to Search]

This Colour Picker is very simple to use and implement. It does not require any API calls and all of the Code can be inserted into existing Subroutines quickly. You can use it to Select a Colour for the ActiveCell in a Worksheet or to Colour a TextBox on a UserForm. The Code has been put together from a few good examples that are out there in the Public Domain. 3 other legacy Colour Pickers are also available for download:

Colour Picker.xlsm (22.8 KB)
Color Picker 2012.xls (19 KB)
Colour Picker 2012.xls (58 KB)
Colour Picker 2012.xlsm (20.8 KB)





Hide Pivot Table Columns

[Back to Search]

This Code allows you to Hide Columns in a Pivot Table when Slicing a Pivot Table using VBA and the 'Evaluate' Function. When you Filter or Slice the Pivot Table any Columns Ranges that are not evaluated to be zero will be Hidden in the Worksheet. This can be quite useful. For example, you may have a lot of data across your Columns in weeks and only want to view the actual weeks with real data - in effect, a Column Suppression for a Pivot Table

Hide Columns in Pivot Table Data Range.xlsm (29.6 KB)





Multiple Slicers

[Back to Search]

This Code allows you to control Multiple Pivot Tables using Multiple Slicers. In other words it allows you to slice and view 2 or more Pivot Tables of Data using similar Slicer Fields:

Using Multiple Slicers with Multiple Pivot Tables.xlsm (31.2 KB)





Adaptive XY Rollover Technique

[Back to Search]

This is a demonstration of an adaptive XY Rollover technique for Excel 2007-2013. This technique will ONLY fire the UDF once to ensure a silent Rollover. It is a single Cell Formula that can be dragged across and down any Range:

XYRollover.xlsm (15.7 KB)





Rollovers

[Back to Search]

These Workbook examples demonstrate how to create and use Rollovers in a Worksheet using my adaptive XY Rollover technique and a single UDF for Excel 2007, 2010 & 2013 based upon the original Rollover technique by Jordan Goldmeier. Lots of Rollovers are shown with different styles ie. Rollover and click a Cell, Rollover and leave 1 Cell Highlighted, Clear Rollovers, Reset Rollovers on exit, Option Select, Use as a Hyperlink, disable Cursor movement in a Menu, a Rollover Menu Grid etc:

Rollovers

Videos
Watch a Video on Rollovers for Excel

How the Technique Works
The technique works by setting a Defined Name to the Rollover Cell Co-ordinates. The values are passed as a String to the UDF using a Formula like this: COLUMN(A1)&0&ROW(A1). This actually is really "101" with the 0 (zero) being required to identify the difference between Column 1, Row 11 or Column 11, Row 1 which would otherwise always equal "111" and highlight two Cells at once. Instead we get "1011" and "1101" which is adequate to distinguish the different Co-ordinates. In case you are wondering, this technique will work for position "XFD1048576" which equals "1638401048576" being 1.6384E+12

4-Step Adaptive Rollover Technique
Multiple Rollovers on multiple Worksheets; Rollovers can be both explicitly linked and any number of batches of Rollovers may be highlighted at the same time; a Rollover UDF can be used to switch Rollovers on/off or toggle highlighting. Add the Rollover Cell Formula to "A1" and then Copy & Paste or move

' // Step 1 - add the UDF
' an adaptive XY Rollover by Mark Kubiszyn, based upon the original Rollover technique by Jordan Goldmeier
Public Function Rollover(MyDefinedName As Range, MyIndex As String): [MyDefinedName] = MyIndex: End Function
 
' // Step 2 - create a Defined Name
MouseoverXY
 
' // Step 3 - addd a Cell Formula (I also show you a Formula to clear any Rollovers for MouseoverXY)
=IF(MouseoverXY=INT(COLUMN(A1)&0&ROW(A1)),"",IFERROR(HYPERLINK(Rollover(MouseoverXY,COLUMN(A1)&0&ROW(A1))),"Rollover"))
=IF(MouseoverXY=INT(0),"",IFERROR(HYPERLINK(Rollover(MouseoverXY,"0")),""))
 
' // Step 4 - add a Conditional Format
=MouseoverXY=INT(COLUMN(A1)&0&ROW(A1))

Rollover Example.xlsm (32.9 KB)
Standard Report Schema (Rollover-enabled).xlsm (22.2 KB)
Business Report Schema (Rollover-enabled).xlsm (22.2 KB)
Disable Cursor Movement in Menu.xlsm (21.7 KB)





Juiicy Rollover Themes

[Back to Search]

These styles are clean, with soft tones, Fonts and Pallets (derived from a Microsoft template for Excel 2013), all wrapped up with a rather Juiicy Menu using my modified version of the Rollover technique by Jordan Goldmeier. You can download these Themes to build new VBA Projects and Reports and see another Rollover method in action:

Juiicy-Themes.zip (201 KB)





Data Validation Using Regular Expressions

[Back to Search]

I use Regular Expressions (or RegEx) for data validation in Excel. It is extremely fast, very flexible and powerful in the sense that its logic will only ALLOW the exact data you want to be processed - everything else will be validated as invalid

Using Regular Expressions negate the use of multiple IF, Then, Else or Case statements. The File below demonstrates using Regular Expressions to check data and check an Email Address

Regular Expressions.xlsm (18 KB)

Regular Expression Patterns


    '    REGEX PATTERN: for Alphanumeric Matching with Anchors
    '    (start tag)
    '    ^
    '    (any one of the upper/lower case letters)
    '    [A-Za-z]
    '    (any one of the upper/lower case letters or digits, repeated zero or more times)
    '    [A-Za-z0-9]*
    '    (end tag)
    '    $
    RegExp.Pattern = "^[A-Za-z][A-Za-z0-9]*$"


    '    REGEX PATTERN: for Numeric Matching with Anchors
    '    (start tag)
    '    ^
    '    (0 or a digit from 1 to 9)
    '    0|[1-9]
    '    (0 or a digit from 1 to 9, repeated zero or more times)
    '    [0-9]*
    '    (end tag)
    '    $
    RegExp.Pattern = "^(0|[1-9][0-9]*)$"


    '    Marks REGEX PATTERN: digits 1-9, then digits 0-9, length allowed is single digit to a maximum of 6 digits
    '                  ^-? allows negative numbers
    RegExp.Pattern = "^-?[1-9][0-9]{0,5}$"


    '    Marks REGEX PATTERN: b01-b09, then b10-b59 & lastly b60
    RegExp.Pattern = "^(b0[1-9])$|^(b[1-5][0-9])$|^(b6[0])$"
    ' you could also use ^b01|b02|b03$ etc. but the RegEx above is more efficient for a wider Range


    '    Marks REGEX PATTERN: b010-b027
     RegExp.Pattern = "^(b01[0-9])$|^(b02[0-7])$"


    '    REGEX PATTERN: Hexadecimal Colour Code
    RegExp.Pattern = "^(#([a-f0-9]{6}|[a-f0-9]{3}))$"


    '    REGEX PATTERN: IP Address (method 1)
    RegExp.Pattern = "\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b"
    '    REGEX PATTERN: IP Address (method 2)
    RegExp.Pattern = "^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$"


    '    REGEX PATTERN: Email Address
    RegExp.Pattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"


    '    REGEX PATTERN: Username
    RegExp.Pattern = "^[a-z0-9_-]{3,16}$"


    '    REGEX PATTERN: Password
    RegExp.Pattern = "^[a-z0-9_-]{6,18}$"


    '    REGEX PATTERN: URL
    RegExp.Pattern = "^(https?:\/\/)?([\da-z\.-]+)\.([a-z\.]{2,6})([\/\w \.-]*)*\/?$"


    '    REGEX PATTERN: HTML Tag
    RegExp.Pattern = "^<([a-z]+)([^<]+)*(?:>(.*)<\/\1>|\s+\/>)$"


    '    REGEX PATTERN: 2 Step Filename & File Extension validation i.e. Test.csv (see below for the routine)
    RegEx.Pattern = "[\\/:\*\?""<>\|]"
    RegEx.Pattern = "^.+\.(([cC][sS][vV]))$"


    More Misc RegEx Patterns that I have used from time to time

    '    REGEX PATTERN: allow numbers of length 2 and blank
    "^([0-9]{2})$|^$"


    '    REGEX PATTERN: allow numbers 1-99 and blank
    "^(\d?[1-9]|[1-9]0)$|^$"


    '    REGEX PATTERN: allow numbers 5-500 and blank
    "^(?:[5-9]|(?:[1-9][0-9])|(?:[1-4][0-9][0-9])|(?:500))$|^$"


    '    REGEX PATTERN: allow numbers 1-409 and blank, ie. use to validate allowed Font Size in Excel
    "^(?:[1-9]|(?:[1-9][0-9])|(?:[1-3][0-9][0-9])|(?:[4][0][0-9]))$|^$"


    '    REGEX PATTERN: allow whole numbers -90 to 90
    "^(?:-[1-9]|(?:-[1-8[0-9]|-90|0)|(?:[1-9]|(?:[1-8][0-9]|90|0)))$"


    '    REGEX PATTERN: allow whole numbers 5 to 999
    "^(?:[5-9]|(?:[1-9][0-9])|(?:[1-9][0-9])[0-9])$"

    '    REGEX PATTERN: allow numbers to 2 significant places from 7.50 to 8.50
    "^(([7]\.[5-9][0-9])|([8]\.[0-4][0-9])|([8]\.[5]))$"
 





Protecting Cell Data Integrity

[Back to Search]

Want to protect Cell content to preserve the data integrity in Excel 2010? Can't stop users removing the Cell data or simply pressing the Back arrow or Spacebar unless you use the in-built Worksheet protection? I will show you a combination method that I use that will prevent deletion of data while allowing you to specify the underlying criteria that can be entered into a Cell without any of those annoying Worksheet protection messages. It will even prevent users being able to insert or delete any Columns or Rows that may effect the Cell data that you are trying to preserve

These methods offers more freedom while still allowing you a tight reign on your Cell content data. It is my preferred choice over in-built Worksheet protection

Protecting Cell Data Integrity.xlsm (19.7 KB)

Sheet1 (prevent blank Cell or the removal of data using backspace, spacebar or Column/Row Insert & Delete)
No Cell change (Data Validation)
No Delete
No Backspace
No Delete Rows or Columns - try it!
No Cell over-write (Data Validation)
No Cell Double-click (user can still use F2 to enter the Cell in Edit mode)

Sheet2 (will apply UNDO to a change to a cell in range A1)
Cell A1 is protected
No Cell change
No Delete
No Backspace
No Delete Rows or Columns - try it!
No Cell over-write





Jagged Array Data En Masse

[Back to Search]

One of the tasks I use VBA for a lot in Excel is to pick up data en masse (in one group or body; all together) into a jagged array or Worksheet array to work with in memory as opposed to looping through Worksheet Ranges. Afterwards, I will need to know the dimensions of my array ie. how many Columns across by Rows down the array is in order to work with the data in memory. To try to make the process better and more uniform, I have written a couple of Functions called GetCurrentRegion() and GetJaggedArraySize():

Pick up Data En Masse.xlsm (18.9 KB)

Pros:
The function is lightening fast even for mass data. You can move the data anywhere within the Worksheet. It's easy to configure with Worksheet and CurrentRegion validation included in the functionality. Being able to derive the Columns across by the Rows down means you can work safely with the array data as if iterating the Worksheet. You can also do some pretty cool stuff once you have the correct dimensions like indexing the data, indexing the Columns for ultra fast data retrieval and using RegEx for validation of the data

Cons:
The Column Headers for the data Range must be contiguous (touching) and a blank Row will end the CurrentRegion





Typography

[Back to Search]

Typography Workbooks are XLSX files for Excel 2007/2010 containing Typography used for Text, Colour Banding, Fills and Hyperlinks. Typography Workbooks provide simple, themed layouts for reporting in Excel. Download the Typography Files as a zip archive from the link below:

Typography.zip (196 KB)





Create Worksheets En Masse By List

[Back to Search]

Here is a small Macro to create and save Worksheets en masse from a list. Setup the required parameters and press a Button to create all of your Worksheets containing only the Filtered data you require for a single Worksheet. The Code will automatically save all of the Files to a Folder Name of your choice relative to where you save the Macro. If you have any linked Formula it must be within the Worksheet otherwise you will need to add in the extra Worksheets with linked Formula and amend the Code slightly to take into account the extra Worksheets containing the Formula

This is useful if you have a list of data where you want to create Worksheets containing the Rows of data that equal 'some value'. Think about it, you may have a massive list of Suppliers where the Supplier Name is linked to hundreds of disparate Rows of data and you want to create Worksheets for each Supplier containing only their data - this File will do all this work for you

Create Worksheets by List Macro.xlsm (110 KB)





Sort Jagged Worksheet Array Data

[Back to Search]

Here is a quick sort Subroutine for Excel to sort Worksheet Array Data. This Subroutine will sort data in Sections ie. from Row 10 to Row 200, ascending or descending and by a particular Column

The JaggedSort() Subroutine is a routine to fast sort by a specific Column for a Worksheet Array. The routine works by picking up the data en masse, sorting it and then replacing the existing data. Please download the example Workbook to see how it works on 50,000 Rows of data both ascending and descending

Jagged Sort.xlsm (3.43 MB)





Transpose Horizontal Data Into Vertical Data

[Back to Search]

Ever wanted to Tranpsose Horizontal Data into Vertical Data and not supress any data? Have you found that the Subtotal routine or Pivot Table does not quite do want you want it to? Well look no further - Transpose will pivot your data seamlessly via the built-in Setup Sheet or by passing Args into the Pivot Function. It's not fancy to look at but it is fast, flexible and available to download as an .XLS File. I hope you find Transpose as useful as I have

Features
1. Lightening Fast Pivot Routine - pick Columns before and after your Pivot Range Data, swap Columns around and add additional Columns & Headers
2. Pivot mass data with ease & with no VBA Knowledge
3. Copy the Setup Worksheet into any Workbook & press the Run Transpose Button

Transpose can be used to Pivot Horizontal data stored in an Excel Spreadsheet vertically. It does not supress information so will retain your Pivot Range Data Column Headers as it transposes the data beneath them. For VBA developers, to pass Args into the Pivot Function download the Transpose Horizontal Data with Function Args.xls File below

Transpose Horizontal Data.xls (132 KB)
Transpose Horizontal Data with Function Args.xls (120 KB)





Five Year Rolling Calendar

[Back to Search]

Need a rolling 5 Year Calendar for Excel to use to lookup ISO Weeks or re-formatted Week Numbers and Dates? This Worksheet will enable you to have a hidden Calendar that you can refer to for looking up your Weeks without having to change Formula. As the Years go by, simply change the Year in the Worksheet to roll the Calendar on. If the Year has 53 Weeks in it then it will return a Week 53 otherwise it will return a Week 52

Using the Calendar
Download the Calendar from the link above and open the Workbook. Add it into a New or Existing Project. Change the start Year in Cell "A4" to the first Year that you want to view. Now change the 2 Drop-down Combo's if you want to see how you can lookup formatted data ie. you can store any kind of formatted data next to the ISO Week data and then look it up based upon a Monday or Sunday Week Day start date - to change from a Monday start Day to a Sunday start Day change the Formula in Cell "D4" by suffixing a -1 to the end of the Formula

Rolling ISO 5 Year Calendar for Week Lookups.xlsx (33.2 KB)





Calendar

[Back to Search]

Need a perpetual Worksheet Calendar that allows you to browse by Month or Year with the ability to capture the selected Date or set a Date in the Calendar itself? Look no further. I have taken a standard Microsoft Calendar and tweaked it with a Calendar Class to make a lovely little Worksheetand VBA Calendar

Using the Calendar
Download the Calendar from the link above and open the Workbook. The Calendar will appear and set itself to the current Date. Click on a Cell within the Calendar. The Calendar will display the selected Date in large Font and the Date will change underneath the Title to reflect the change. Click in the Cell "B3". You will see a Formula that referes to the Defined Name "booking_system_uiddate". You can use this Defined Name to capture any change in the Calendar. The Defined Name resides in the "Setup" Worksheet

You can set the Calendar to display the start of the Week as another day. Change the setting below on the "Setup" Worksheet. The Weekends will automatically change on the Calendar itself. If you want to change or add more Years you can also do this on the "Setup" Worksheet. You can also capture a Mouse-click in the Calendar using the Private Sub Worksheet_SelectionChange(ByVal Target As Range) Event Handler in the "Menu" Worksheet - see the Code

Calendar

Calendar.xls (71 KB)
Calendar.xlsm (38 KB)





Hamilton Largest Remainder Method

[Back to Search]

The largest remainder method (also known as Hare-Niemeyer method or as Vinton's method) is one way of allocating seats proportionally for representative assemblies with party list voting systems. The Hamilton method of apportionment is actually a largest-remainder method which uses the Hare Quota. It is named after Alexander Hamilton, who invented the largest-remainder method in 1792. Largest Remainder Methods are also known as Quota Methods. The most commonly used are: Hare, Droop and Imperiali. In some literature, the quota Q is also called the "Standard Divisor"

Using the Method for Demand Planning
So from a Demand Planning perspective we can use the largest remainder method as a way of allocating values proportionally based upon share. For example consider that we have a "Value to Split" of 25. You can call it a Promotional value or some value that we need to apportion without loss through rounding. We have Sales or "Shares" for Products by Location of 1500, 1500, 900, 500, 500 & 200. The SUM of these Shares or "S Shares" is 5100. We can now work out the percentage (%) by dividing the Share by the SUM of all of the Shares or "Share / S Shares" which must always add back to 100%. Now that we have the percentage (%) we can work out the "Quota" (or "Hare Quota") for each Share by multiplying the percentage (%) by the Value to Split of 25. From this we only have to Round the Quota to derive the final Split albeit possibly with errors due to loss of values following Rounding

The standard Split using this example induces an Error of 2 - we can only apportion a Total value of 23 (we lost 2 through Rounding). The standard Split gives us final values of 7, 7, 4, 2, 2, 7 & 1. Hamiltons Largest Remainder Method will apportion back missing values following a Round-down by giving them to the Quota with the largest value. So Rounding-down we see that we have actually induced an Error of 3 (1 more than merely Rounding) - we can only apportion a Total value of 22. The Rounded Split gives us final values of 7, 7, 4, 2, 2, 0. Hamilton says that we should now give back or "Add Back" the missing values to the Remainder part of the Quota (the part following the Radix point or Decimal Place)

Adding back the missing values to the Remainders 0.980, 0.451 & 0.451 gives us final values of 7, 7, 4, 3, 3 & 1 which adds up to our Value to Split of 25 (no loss due to Rounding). Hamilton is unbiased, never breaches quota, but risks an occasional tiny-party problem

Hamiltons Largest Remainder Method using the Hare & Droop Quota.xlsx (15 KB)





Lotto Hotpick Analyser

[Back to Search]

The Lotto HotPicks Games allow you to pick and match fewer numbers to win a prize. There are five different Lotto HotPicks Games - 'Pick 1', 'Pick 2', 'Pick 3', 'Pick 4' and 'Pick 5'. Lotto HotPicks uses the Lotto Draws on Wednesdays and Saturdays. Lotto HotPicks gives you the chance to win bigger prizes for picking and matching fewer Lotto numbers. Still only 1 a game!

Lotto Hotpick Analyser helps track all of the draws in which you can play the hotpick games from 19 November 1994 until the present day. It will update its Draw Data and winning ball combinations by downloading a rolling 180 week csv file from the lottery HQ and adding any missing draws. It details statistical analysis to help you interpret and decide on which winning ball combinations to play

Lotto Hotpick Analyser uses the fade in/out preloader transition in vba for excel by Mark Kubiszyn using spin.js library by Felix Gnass

lotto hotpick analyser.xlsm (3.14 MB)





Resize Range and Formula on Paste

[Back to Search]

With the advent of BI Portal reporting whereby data is used to Paste over existing data into Excel following the refresh of a Portal Report, I decided to write a small piece of Code to handle the action of resizing both data and Formula when Pasting over a Range with new data. This Code can be added to the Worksheet_Change Event Handler of any Worksheet Code Module and with a few tweaks you can have a really dynamic 'On Paste' routine that will automatically handle the Range resizing and Formula spawn. Please note: even if someone tries to Paste Special values the Code will retain the source data formatting

2 Workbooks can be downloaded - the second Workbook will allow you to Paste data over a Range and the Range will resize and keep the data as Special Values (adjacent Formula will also be resized and copied down the Range)

Copy & Paste to Resize Range and Automatically Adjust Formulas to Match.xlsm (18.6 KB)
Copy & Paste to Resize Range and Automatically Adjust Formulas to Match (as values).xlsm (18.6)





Full Screen Fade Overlay

[Back to Search]

This is a demonstration of a Full Screen Overlay transition for Excel 2007, 2010 & 2013. When you run the Code a Window will run Full Screen covering everything including the Taskbar and a Progress Bar will loop until a pre-determined time before giving focus back to Excel. You can use this while waiting for a task to complete. You can change the background Colour, the Display Duration time and the Fade out Duration time

To see the effect, download the file from the link above, open the Workbook and press the 'Fade Overlay' Button. The Code requires the FadeOverlay UserForm and a single call to the FadeOverlay.Display Function, passing an RGB Colour (Long), a Display Duration (Long) and a Fadeout Duration (long). When you press the Button it will run the Example

Fade Overlay.xlsm (54.2 KB)





Generate Text and Number Combinations

[Back to Search]

Here is a simple Workbook that generates combinations from either an AxB list or an AxBxC list for both Text and Numbers using Excel Formula

Generating the Combinations
Download the Tool from the link above and open it. On either Worksheet, enter (or Copy & Paste as Special Values) your Text and Numbers into the Input Cells underneath the Headers where it states "EDIT >>". Change the Headers to whatever Name you want the Headers to be. Your list of combinations will be shown on the RHS, where it states "RESULT >>". You can simply Copy & Paste these as Special Values. You can also Sort the Values thereafter as you wish. The AxB combination list was fairly easy to replicate using some formulas given by the Sources mentioned in the Workbook, whereas the AxBxC combination list is a little more advanced

Now you can simply Copy & Paste the results coloured yellow into any Workbook. If you want, you can further Sort the combinations as you want them by applying a Sort in Excel. I have also left a Concat in case you need to use this for any Lookups

Generate Combinations using Formula.xlsx (23.5 KB)





Read and Import CSV Files

[Back to Search]

The following Code will import a CSV File into Sheet2 of a Workbook at starting Cell "A1". Download the File below - no references are required but you will need to change the Filepath & Filename to your own! The DeleteData Macro will delete the data connection. The Function returns True if it is successful or False if it fails due to any error. Test Data can also be downloaded - I urge you to test with 100,000 Rows of data to see how fast this Subroutine is

Read & Import CSV Files.xlsm (23.1 KB)
Test Data.csv (14.2 KB)





Prevent Multiple Pivot Table Slicer Selections

[Back to Search]

Here is a simple, easy to configure example of preventing multiple Slicer Item selections for a single Slicer connected to a Pivot Table. This is useful when using Pivot Tables as Buttons or for example when you have 2 different Units of Measure associated with a single Pivot Table Data Source when you do not want both Units of Measure included in Pivot Table Totals. For example, say you have a Slicer with the Items HL and PC and that your data contains Volumes in HL and in PC (pieces) for certain Categories in the Pivot Table Source Data - you would not want both different types of measurements added together as the result would not be accurate. You want to give the user the ability to select either HL or PC, but not both at the same time. You can also hide the Filter in the Slicer

The download below also shows you how to capture Mouse clicks on an individual Slicer items

Prevent Multiple Pivot Table Slicer Selections & Capture Mouse Clicks.xlsm (23.9 KB)





Hide or Minimise the Ribbon

[Back to Search]

Here are 3 variations of the same simple method of hiding and/or minimising the Ribbon in Excel

Hide Ribbon.xlsm (10.8 KB)
Hide Ribbon & Persist FullScreen When Minimized.xlsm (12.5 KB)
Hide Ribbon SendKeys.xlsm (10.8 KB)

If you are searching for a way to Minimize the Ribbon as opposed to actually hiding it, then use the following Code to Minimize the Ribbon if it is Maximized instead of the SendKeys approach:

 
Sub MinimizeRibbon()
 
 If Application.CommandBars.Item("Ribbon").Height > 80 Then _
  Application.CommandBars.ExecuteMso "MinimizeRibbon"
 
End Sub

 
Or to Toggle the Ribbon Minimize/Maximize use this:
 
Sub ToggleRibbon()
 
 Application.CommandBars.ExecuteMso "MinimizeRibbon"
 
End Sub

 





Radio Button

[Back to Search]

Here is a simple, easy to configure Cell-based Radio Button to use as an Option Select for Excel 2010 & 2013. Download the file from the link above and click a Radio Button to make a Selection. Press ALT+F11 to view the Code. It uses the Wingdings Font for the Radio Button

Please Note: when implementing your own Radio Buttons you need to add the Defined Names "Radiobuttons" and "SelectedOption" to your Workbook. Then Copy & Paste the Code and the Radio Buttons into your new or existing Workbook. Link the Defined Name "Radiobuttons" to all of your added Radio Buttons. Place the Code below in the Worksheet Code Module (the same Worksheet that your Radio Buttons are in). To capture the Selection using VBA simply iterate the Defined Name and check the Offset Value

Radiobutton.xlsm (18.2 KB)





Check Box

[Back to Search]

Download Checkbox from the link below and double-click the highlighted Cells to toggle the Tick on and off. Press ALT+F11 to view the Code. The Code works by toggling between an "a" / "r" (small Cross) or Nothing (simply clearing the Cell). It uses the Marlett Font for the Tick and / or Cross

Please Note: when implementing your own Checkboxes you need to add Defined Names called "Toggle" and "Toggle2" to your Workbook. Then Copy & Paste the Code and the Checkboxes into your new or existing Workbook. Create two links to Defined Names "Toggle" and "Toggle2" to all of your added Checkboxes. Place the Code below in the Worksheet Code Module (the same Worksheet that your Checkboxes are in). Then to test if a Toggle is on or off using VBA you can give each Cell a Defined Name and use Code like:
if [option] = "a" then "do something in your Code..."

Checkbox.xlsm (16.2 KB)
Tickbox.xlsm (15.5 KB)





Powerpoint Traffic Light Conditional Format

[Back to Search]

Here is a PowerPoint Presentation that uses a single Function to iterate all of the Tables on each Slide and Conditionally Format the Text Frame Font Colour and Shape Foreground Colour. Let's suppose you have a Range of Tables that you update each week and then Paste into a PowerPoint Presentation. When you may wish to change the Colour of the status for each Row in the Table you can press ALT+F8 and run the ConditionalFormatTables() Macro. If you change the status to "Red" then the status Colour will be Red, if you change the status to "Amber" then the status Colour will be Amber and if you change the status to "Green" then the status Colour will be Green. Try changing the Text status from "Green" to "Red" and then run the Code

Okay, so you may have a different scenario than this, but you can easily adapt the Code to Format Table values for a Status or some other Text

Conditional Format PowerPoint.pptm - PowerPoint (48.5 KB)





Resize Alternative For ReDim Preserve

[Back to Search]

Here is a Workbook containing a single Function that will Resize an Array by both Bounds (Subscripts). This can be used as a replacement for a Call to ReDim Preserve. The use of the SizeOf Function allows you to determine the size of the Array Subscript, 1=Rows, 2=Columns

Resize.xlsm (66.4 KB)





Transpose Dictionary Alternative For Application Transpose

[Back to Search]

Here is a Workbook containing a single Function that will Transpose a 1D Dictionary Keys or Items array into a 2D Worksheet array that can be written to a Range. This can be used as a replacement for the Application.Transpose or WorksheetFunction.Transpose Functions in Excel. I use this Function in my Pivot Notes Software

You can use the Function below to Transpose a 1D Dictionary array so that either Keys or Items can be stored in a Variant array or written out to a Worksheet Range. Remember to clear down the Items already displayed in the Workbook before running the Subroutines to see the result of transposing Keys or Items

Transpose Dictionary.xlsm (19.7 KB)





User UI Interface Elements - Rollover Buttons and Tree List

[Back to Search]

Here is a Free Workbook containing some recent UI Elements I have been messing about with in Excel. You have a very simple Tree List, two Drop-down Lists, some Rollover Buttons and an Accordion List. You can use these for anything that you like - press ALT+F11 to examine the Code. I do not have the time to go into detail regarding the Code, but it is simple enough and some comments are provided in the Download

UI Elements Rollover Buttons

UI Elements Simple Tree List

UI Elements.xlsm (45.6 KB)





Rhino Dashboard Template

[Back to Search]

Hello, I'm rhino, a beautiful dashboard ui for excel. I can be used as a frontend for any kind of dashboard or reporting interface in excel

The rhino background colour is RGB(34,34,34)
rhino uses a single colour for the dashboard background, RGB(34,34,34) or the HTML code #222222

The rhino font colour is RGB(206,206,206)
rhino uses a single colour for the dashboard font, RGB(206,206,206) or the HTML code #CECECE

A rhino section divides the content
rhino uses 2 colours as a section divider, a dark line RGB(18,18,18) or the HTML code #121212 next to a light line RGB(42,42,42) or the HTML code #2A2A2A. A section divider can be a column or row but the dark line must come first, followed by the light line. Rhino sizes the column and row to 0.08 and 1 respectively. A column section divider should be surrounded by a column either side, sized at 2.57 to ensure that if text is entered, it is spaced evenly within each section

A rhino placeholder displays a widget
rhino uses 2 colours as a placeholder to display a widget, a dark line RGB(18,18,18) or the HTML code #121212 for the left / top border and a light line RGB(42,42,42) or the HTML code #2A2A2A for the right / bottom border - this creates a sunken effect, perfect for show-casing any kind of widget

A rhino panel displays some text
rhino uses 2 colours as a panel to display text, a a light line RGB(42,42,42) or the HTML code #2A2A2A for the left / top border and a dark line RGB(18,18,18) or the HTML code #121212 for the right / bottom border - this creates a raised effect, perfect for writing beautiful things

Choosing additional colours to use with your rhino skin
rhino uses includes 3 styles and 4 colours for the layout ui. when choosing additional colours to use with rhino, keep it simple and stylish, use only a handful of colours - your rhino should never be garish or lurid, he will not thank you for it!

What can I do with my rhino skin when I've downloaded it?
you can do anything you want, add some chart widgets, 'pimp it up' using code - rhino will love you for it!

rhino donut chart widget

hino progress bar chart widget

rhino column chart widget

rhino area chart widget

rhino calories burned chart widget

rhino.xlsx (10 KB)

Rhino.zip (204 KB)





Hide Taskbar Routines

[Back to Search]

Looking to hide the Hide Taskbar using VBA for Excel in XP / Win7 or the Win7 Start Button Icon? - here are 7 routines that may help and if my Final Solution which works in all Versions of Excel (XP/Windows 7) 9-14 only!

Examples Include
1. This routine hides everything leaving the Start Icon active
2. This routine autohides the Taskbar safely (hovering near the Taskbar will bring it into focus)
3. This routine will hide the Start Menu & Taskbar
4. This routine will firstly hide the Taskbar & then hide the Start Icon
5. This routine will hide the Start Menu & Taskbar & Go Excel Full Screen - tested in XP, Win7 Office 2000/2010, try this one
6. This routine will toggle the Start Menu & Taskbar using Sendkeys
7. This routine will hide the Start Menu & Taskbar using the Registry


Please note: some of these routines do not work on Windows 10

Hide Taskbar Routines.xls (112 KB)
The Final Solution, True Full Screen Kiosk Mode.xls (95.5 KB)





Get Range Using Application InputBox

[Back to Search]

To use the GetRange() Subroutine and Application.InputBox to return the Workbook Path, Workbook Name, Worksheet Name & Selected Range Address, download the File below. Open the File and press the Demo Button. Select a Range within the current Excel Instance collection of Workbooks. The Workbook Path, Name, Worksheet Name & Selected Range Address part will be returned and displayed in a MsgBox Dialog

The VBA Code also checks the Err.Number Object and only runs if it does not equal 424 ie. the InputBox is Not Closed or Cancelled. The Range Object is also checked for validity. Works with all Versions of Excel - Excel Instances must be the same for Excel Versions > 2013

Application-InputBox.xlsm (15.4 KB)





Force Cell And Case Formatting

[Back to Search]

How to Force the Cell & Case Formatting in an Excel Workbook using the Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Event Handler

Force Case.xls (34 KB)





Generate Random Data

[Back to Search]

Here are various methods to generate and dump out Text & Numerical data very quickly into Excel using VBA. So that you can explore the extra Rows/Columns available in Excel 2010 the example Downloads Files are .XLSM

The Random Function
The RND function allows you to generate a random number (integer value). You can specify the random number to be a value between 2 user-specified numbers. The syntax being Int ((upperbound - lowerbound + 1) * Rnd + lowerbound) (upperbound is the highest value that the random number can be and lowerbound is the lowest value that the random number can be). If you use it without coersing to an int the Rnd Function would retun a double value ie. 0.79048. We coerse the value into something meaningful by forcing it into a whole Integer Number and ensure it is never zero by adding the 1

' // an example for VBA
Public Sub RandomExample()
 
 ' // generate a random number between 1 and 100
 MsgBox Int((100 - 1 + 1) * Rnd + 1)
 
End Sub
Alternative Volatile Random Function
Here is an example if you want to use it in a Worksheet as an alternative to the existing volatile =RAND() or =RANDBETWEEN() in Excel (ie. this will not change upon a Worksheet Calculate)
' // use for a Worksheet, non-volatile
Public Function Random(ByVal upper As Double, ByVal lower As Double) As Integer
  
 ' // generate a random number 
 Random = Int(((upper - lower) + 1) * Rnd + lower)
 
End Function
Generate Random Text Data.xlsm (18.9 KB)
Generate Random Numeric Data.xlsm (22.3 KB)
Generate Random Numeric Row Data Subroutine.xlsm (17.4 KB)





Get Range Data By Header

[Back to Search]

To see the GetRangeData() Function in action, download the File below. Open the File and press the Demo Button. The VBA Code will pick up all of the data underneath the Header Material into a Variant array ar() display a slot of data from the array and then output the entire data to Sheet2 Cell A1. You can move the entire data anywhere in the Worksheet and the Code will still pick up the Range

Get-Range-Data.xls (126 KB)





Get Column Letter From Column Number

[Back to Search]

To get the Column Letter from a Column Number using VBA you can use the GetColumnLetter() Function. This Function is accessible from a Worksheet or from a Subroutine within your VBAProject. You pass in a Column Number and the Function returns the Column Letter as a String. Copy & Paste the following Function into a Code Module

You can also use the Function from a Worksheet. Enter the following into a Cell: =GetColumnLetter(1) or reference a Value from another Cell by entering the following: =GetColumnLetter($B$14) where $B$14 is an Integer from 1-256 (Excel 2000-2003) up to 16,384 for Excel 2007/2010/2013)

Get-Column-Letter.xlsm (16.3 KB)





Get Column Number From Column Letter

[Back to Search]

To get the Column Number from a Column Letter using VBA you can use the GetColumnNumber() Function. This Function is accessible from a Worksheet or from a Subroutine within your VBAProject. You pass in a Column Letter and the Function returns the Column Number as an Integer. Copy & Paste the following Function into a Code Module

You can also use the Function from a Worksheet. Enter the following into a Cell: =GetColumnNumber("A:A") or reference a Value from another Cell by entering the following: =GetColumnNumber($B$14) where $B$14 is a String from "A:A" to "IV:IV" (Excel 2000-2003) or "A:A" to "XFD:XFD" for Excel 2007/2010/2013)

Get-Column-Number.xlsm (15.6 KB)





Build And Select Dynamic Range

[Back to Search]

Here is a Workbook to demonstrate how to build and Select a Range dynamically ie. given a single Cell Reference "D4" a number of Columns 6 and a number of Rows 4 we will Select the dynamic Range "D4:I7" (try adjusting the Parameters to see different Results). To go backwards given the Example Cell Reference "D4" you can pass rows = 0 and cols = 0 which would give you a Range of "C3:D4" which is 1 Row and 1 Column backwards from your starting Cell

I have also included a Function that will return a Range Object for you to then use to Select or Format a Range (ideal for using in VBA Procedures when data is in varying Column/Rows lengths)

Option Explicit

Public Sub BuildRangeExample()

    Dim r As Range
    Set r = BuildRange("A1", 2, 6)
    If Not r Is Nothing Then r.Select

End Sub

Public Function BuildRange(ByVal start As String, ByVal cols As Integer, ByVal rows As Long) As Range
    On Error GoTo Catch
    Set BuildRange = Range(start, Range(Left(Cells(1, Int(cols + Range(start).Column - 1)).Address(1, 0), _
                                             InStr(1, Cells(1, Int(cols + Range(start).Column)).Address(1, 0), "$") - 1) & Range(start).Row + rows - 1)).Cells
    Exit Function
Catch:
    Set BuildRange = Nothing
End Function


Build-Dynamic-Range.xlsm (16.4 KB)





Get Last Row

[Back to Search]

How to Get the Last Row of a Worksheet without Using Cells.SpecialCells. Return the Last Row for Staggered Column Length Data within a Worksheet

You can also use the Function from a Worksheet. Enter the following into a Cell: =GetColumnNumber("A:A") or reference a Value from another Cell by entering the following: =GetColumnNumber($B$14) where $B$14 is a String from "A:A" to "IV:IV" (Excel 2000-2003) or "A:A" to "XFD:XFD" for Excel 2007/2010/2013). Download and open the File below. On the front Worksheet you will see some data underneath Headers. Click the Get Last Row Hyperlink to run the VBA Code that returns the Last Row on the Worksheet as a long variable

A Message will be displayed informing you of the Last Row on the Worksheet. Another Message will then give you the Next Row on the Worksheet. I prefer using this method of returning the Last Row as opposed to using Cells.SpecialCells as although most of the time the Code Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row would work fine, if you then clear the contents near the end of your Range in the Worksheet (try it with the Example) and then try to Get the Last Rown again, Excel still thinks that there's more rows than there should be thereby giving you an incorrect value. I have included both methods with the one I always use first.

NB: you can use "A1" or "A1:A1" to just check against a single Column A for the Number of Rows down

This method also allows you to have staggered length data anywhere in the Worksheet without the requirement to clean formatting and it is also fast

Get-Last-Row.xlsm (18.1 KB)





Google Charts Google-O-Meter

[Back to Search]

Create a Dynamic & Stylish Google-O-Meter Chart Image using the Google API library

The Google Chart API lets you dynamically generate charts with a URL string. You can embed these charts on your web page, or download the image for local or offline use ie. inserting into an Excel Workbook. My Google-O-Meter VBA Code allows you to specify dynamic parameters to build, create, download and insert a Google-O-Meter Chart into any Worksheets within an Excel Workbook. NB: Google has now deprecated Image Charts but the Code still works fine

Google-o-meter

Google-O-Meter.xls (89 KB)
Google-O-Meter Chart Image Generator.xls - deprecated (94 KB)

Links
View Google Developer Information Online: Google Developers Google Chart Tools
Online support:  https://developers.google.com





Message Light Box MsgLightbox

[Back to Search]

How to Implement a Full Screen MsgLightbox (Lightbox Effect) Overlay

MsgLightBox

This is a File that implements a Full Screen Lightbox Overlay effect with a Message on a Modal styled UserForm. The MsgLightBox creates a Full Screen UserForm overlay in Excel similar to a Lightbox and uses a keyboard hook to disable ALT+TAB & F1-F12 function keys. The effect is a semi-transparent (light / dark) overlay for the whole of the visible Screen area. The Lightbox locks off all mouse clicks aside from the click on the relevant Button offered by the MsgLightbox

Download and open the File below. Click on the MsgLightBox Demo Button to view mutliple MsgLightboxes in action. The first MsgLightbox is very light, almost transparent, however try clicking anywhere on your Screen. The second MsgLightbox will capture the Keypress Value of the Button you press. It is stored as a VbMsgBoxResult. Pressing Yes will result in the number 6 being returned. Pressing No will result in the number 7 being returned. The third MsgLightbox demonstrates a much darker, Critical Warning MsgLightbox. I have also stripped down the Demo to allow you to test each part by Clicking on each of the Blue Buttons in turn

MsgLightbox.xls (73.5 KB)





Trial Dialog

[Back to Search]

How to create an Excel VBA UserForm Trial Dialog with Information Icon & Timer Enabled OK Button

Trial Dialog

Download and open the File below. Click on the Trial Dialog Demo Button to view the Dialog in action. The Trial Dialog VBA Code creates a Msgbox using a Modal UserForm with an Information Icon and a Countdown Message / Timer This Trial Dialog can be closed in 4. Upon finishing the Countdown the OK Button will be enabled. The Trial Dialog has VBA code that handles a hack attack via Ctrl+Break - this makes it ideal for Trial programs. You will notice from the Image above that the small Red Close Cross is not displayed whilst the Dialog is running (this is optional ie. you can still have the Red Close Cross but bring up a Message informing the user that they cannot Close the Dialog until the Countdown has finished)

Trial Dialog.xls (49.5 KB)





Worksheet Browser

[Back to Search]

Simple Worksheet Browser Add-In that Creates 2 Buttons on the Ribbon. Previous Sheet & Next Sheet allowing you to Navigate Worksheets for Excel 2007/2010/2013

Download and Extract the 3 Files from the Worksheet Browser ZIP Archive File below. The Worksheet Browser Add-In is for Excel 2007/2010/2013. I have included the .XLSM File so that you can see how I built the AddIn together with the .XLAM (AddIn) File. When the Add-In in installed it adds two Buttons before the Group Show on the View Tab of the Ribbon. The Add-In passes the Ribbon Button Control.Tag ID which is either 1 to go to the Next Worksheet or -1 to go back to the Previous Worksheet into the Browser Subroutine. The Code will skip over any Hidden or Very Hidden Worksheets and will also wrap around i.e. when it reaches the last or the first Worksheet it wraps to the next available Worksheet depending on the direction

Worksheet-Browser.xlsm (20.3 KB)
Worksheet Browser.xlam 55.5 KB)
Worksheet Browser.zip (106 KB)





Progressbar

[Back to Search]

Here is a great little Progressbar to use in Excel

Progressbar

Progressbar with HTML Sheet (Non Modal).xls (73.5 KB)
Progressbar with Self-contained HTML Markup (Non Modal).xls (68.5 KB)
Progressbar (Non Modal) FRM.zip (4.44 KB)

Links
Open the Progressbar.htm File to view the HTM File used with this example





Math Functions Sigma And Huge Strings

[Back to Search]

These Math Functions will Add, Subtract and Multiply huge Strings together. They will also convert huge Strings to Base10 and calculate Sigma Codes

Math Functions.xls - All Math Functions · Includes other Functions ie. Sigma, to_base10 (82 KB)
Math Function.xls - Multi-purpose & Multiplication (79 KB)
Sigma Function.xls (36 KB)

The Sigma Worksheet Function (UDF) - derives the Sigma Code for a single Number

Public Function Sigma(ByVal n As String) As Integer
 
 Dim i, c As Integer
 
 While Len(n) <> 1
  i = 1: c = 0
  While i <> Len(n) + 1
   c = c + Mid(n, i, 1): i = i + 1
  Wend
  n = CStr(c)
 Wend
 
 Sigma = n

End Function
The Sigma String Worksheet Function (UDF) - takes a String of Numbers thus: "8,3,9,12,27" and derives the overall Sigma Code ie. 5
Public Function SigmaString(ByVal n As String) As Integer
  
 Dim i As Integer, c As Integer, s As String
 Dim ar: ar = Split(n, ",", , vbTextCompare)
 For i = 0 To UBound(ar):  s = s + ar(i): Next i
 
 While Len(s) <> 1
  i = 1: c = 0
  While i <> Len(s) + 1
   c = c + Mid(s, i, 1): i = i + 1
  Wend
  s = CStr(c)
 Wend
  
 SigmaString = s
 
End Function
The Sigma Worksheet Addition Formula - adds an Integer to each digit of another Integer and derives the Sigma Code (requires the Sigma Worksheet Function above) ie. 28 + 1 = (2+1) + (8+1) = 3+9 = 12 = Sigma Code 3
' Cell "A2" is the Integer and Cell "D2" is the Integer to be added to each digit
=SUMPRODUCT(MID(A2,ROW(OFFSET($A$1,,,LEN(A2))),1)+D2)
 





Indexing Worksheet Data Using Dictionary

[Back to Search]

Indexing Worksheet Data using a Dictionary Object & Keeping the Index in Scope for Ultra-fast Searching/Record Retrieval - demonstration of Persistent Indexing on Worksheet Data using a Dictionary Object & Late Binding

I am going to show you how to Index 30,000 Rows X 16 Columns of fictional Product data within an XLS Worksheet. We will index the data by 2 seperate values to build a unique index (a concat). We can then simply find the row number and therefore any data relating to it. This is a very simple and powerful technique (especially if you keep the Index in scope) yet is often overlooked in preference to iterating ranges or using find, match etc. It not only allows us to have a handle on all the data, but furthermore, we can link in additional data from other sheets or change / pull data from our existing Sheet simply by passing the row offset of our indexed, unique key. Timings for hammered retrievals are not applicable (ie. the retrieval is instant) once you have/maintain your Index. Download the File below to see the Code in action

Indexing a Worksheet.xls (4.71 MB)
Indexing a Worksheet (Blank Data).xls - smaller file to download (48 KB)





List And Move Desktop Icons

[Back to Search]

List Icon Names & Move an Icon Off Screen using Excel 2010. Download and open the File below. This Workbook allows you to list all of your Desktop Icons by Name along with a count of the total number of Icons found. It also enables you to move an Icon using its Name by passing X and Y co-ordinates. In effect you can then move an Icon off screen should you so wish ie. when you have Desktop Shortcuts that have been installed by your Admin and you have limited rights to delete them. The Icons are still there, they are just not visible due to their placement outside of your visible Desktop area. To bring them back again you re-run the Utility or simply right-click on your Desktop and Select Sort by and then by Name in the Popup Sub-menu (in fact Selecting View and then Align icons to grid will also bring them back into view). NB: you need to have the View->Align icons to grid setting set to unchecked in order to use the Utility to move Icons

Press the blue Hyperlink entitled Run Desktop Icons Utility to retrieve all of the Icons currently on your Desktop. To move an Icon off screen Copy & Paste the Icon Name into Cell G4. Set the X Co-ord to -100 and the Y Co-ord to 0. Press the blue Hyperlink entitled Move an Icon using Co-ords to move the Icon off screen

Desktop-Icons.xlsm (29.3 KB)





Prime Number Functions

[Back to Search]

Here is a Workbook to Highlight Cells & Ranges where Numbers are Prime or Non Prime. Download and open the File below. You will see 3 Columns of Numbers from 1 to 1,000. The first Column has the IsPrime() Worksheet Function in situ down the entire Range - see Cell C10 with the Formula =IsPrime(B10). The Formula identifies whether or not the adjacent Cell is Prime or not by either True:=Number is Prime or False:=Number is Non Prime

The other 2 Columns have Numbers from 1 to 1,000 but you need to Press ALT+F8/ALT+T+T+M and run the Macros to Colour the Cells accordingly. Press the Key combination to bring up the Macros Dialog. Run the ColourPrimes() and ColourNonPrimes() Macros. The Cells will be Coloured Yellow in the first Column if the Number is Prime and Grey in the second Column if the Number is Non Prime

Prime-Functions.xls also features a Worksheet Function to generate the next Prime Number given any Number - see the second Worksheet "NextPrime"

Prime-Functions.xls (144 KB)
Prime-Number-Checker.xls (41.5 KB)





Purge Workbook Data And Formula En Masse

[Back to Search]

Here is a Workbook that uses my 'Purge' Function to purge data en masse

Purge.xlsb (2.25 MB)





Sorting Algorithms

[Back to Search]

VBA Array & Worksheet Array Sorting Algorithms for Excel 2010/2013 using VBA

Here are Workbooks which demonstrate different Sorting Algorithm Functions to sort data using VBA by a variety of methods. All of these Functions have been written from scratch using Pseudocode from Wikipedia or Reference books

Selection Sort.xlsm (21.5 KB)
Bubble Sort.xlsm (21.6 KB)
Insertion Sort.xlsm (21.4 KB)
Counting Sort.xlsm (19.3 KB)
Heap Sort.xlsm (21 KB)
Comb Sort.xlsm (21.2 KB)





Email Template Outlook

[Back to Search]

Avoid the Outlook Security Guard. Free Excel Mailing Template for Excel 2003-2010 · automate your Report Mailing using powerful RegEx Tags

Email Template Outlook

email-template (Outlook) is a lightweight Worksheet that you can copy into any Excel Workbook or install and use as an Excel Template to automate Report Mailing. It is quick to setup and does not require any VBA code knowledge

Features
Zip Files & Attachments on-the-fly
Remove formatting from Workbooks as you Email your Reports
Dynamically rename Workbooks & File Paths as you Email your Reports
Copy the Workbook Colours on-the-fly
Install email-template (Outlook) as a Template in Excel (to insert into a Workbook at any time)
Powerful RegEx Tags - allows dynamic data exchange in Email Templates, Signatures & Excel Ranges

How does it work?
The Software avoids the Outlook Object Guard via an email-template interface (called unsurprisingly email-template) which you save on a local or network drive. Everytime you use the lightweight Excel Template called email-template (Outlook) it querys the interface(email-template). This means you have a flexible, lightweight Worksheet that you can populate with To, CC & BCC Email Addresses, add & Zip Attachments (including dynamically removing Worksheets), select Outlook Signatures, TXT, HTM or HTML Templates, Ranges and setup all your Report Mailing options, leaving the automation to the interface

RegEx (or Regular Expressions)
RegEx (Regular Expression) Tags can be inserted into Signatures and TXT, HTM or HTML Files allowing dynamic content exchanges. For example consider the following. You have a Report that you update each week with a Date and Week Number. You can embed 2 RegEx Tags into your Signature so that when email-template (Outlook) Mails the Workbook your updated dynamic content will replace the Tags with the respective Date and Week Number. You would avoid having to: create a new Email, edit the Email with your new Date & Week Number, attach your Report and then Send the Email - it would all be done by one-click in email-template (Outlook). You can add Tags by editing your Signatures or opening TXT, HTM or HTML FIles in a Text Editor Program such as Notepad, Word, Wordpad or CuteHTML etc.

email-template (Outlook).zip (2.21 MB)





Kiosk 4.1 AddIn Full Screen Excel

[Back to Search]

Add a Kiosk Mode Transition to your Workbooks available everytime that you open Excel. Kiosk is a Workbook transition AddIn that renders Excel as a Full Screen Application. In Kiosk Mode Excel is entirely borderless delivering a true Full Screen appearance. Features include a borderless Full Screen transition for all Workbooks with the ability to toggle Headings, Gridlines, Worksheet Tabs and Scroll Bars. Kiosk works alongside the Excel Application.DisplayFullScreen only exiting the Kiosk Mode upon Escape being pressed or right-click Close Full Screen on any Worksheet. Kiosk is an Excel AddIn which is installed or uninstalled easily. It does not use Sub-classing or install/register any third-party DLL's and is therefore perfect for Workplace or Network scenarios where Admin rights are normally required for Software installations. The Kiosk AddIn Icon for Excel 2007/2010 is added to the Ribbon on the View Tab next to Workbook Views every time that you open Excel. Kiosk has been tested on Windows XP, XP Professional, Vista & Windows 7. If you need a version of Kiosk for Excel 2015 - Kiosk Full Screen Borderless Excel

Kiosk 4.1 AddIn

Kiosk4.1.xlam (288 KB)
Kiosk4.1.xlsm (149 KB)
Kiosk4.1.zip (570 KB)
Open Full Screen & Hide Everything Example.xlsm (16 KB)
Slideshow Example.xlsm (3.73 MB)





Kiosk v6 Full Screen Excel

[Back to Search]

Create & Distribute your own Kiosk Full Screen Workbook Projects. Kiosk is a Workbook transition that renders Excel as a Full Screen Application. In Kiosk Mode Excel is entirely borderless delivering a true Full Screen appearance. Kiosk v6 Dev Software for Developers allows you to create and distribute your own Kiosk Full Screen Excel Workbook Projects for all Versions of Excel. In other words you can design Worbooks to automatically launch into true Full Screen Kiosk Mode or give the end user the ability to launch them Full Screen by pressing F4. Kiosk v6 Dev.XLS can be used for Office 2003-2010. The Kiosk v6.XLSM (used to build the XLAM AddIn) & the Kiosk v6.XLAM can be used in Office 2007/2010

Please Note: if this does not work for later Excel versions in Windows 10, then download Kiosk 4.1 AddIn Full Screen Excel or if you need a version of Kiosk for Excel 2015 - Kiosk Full Screen Borderless Excel

Kiosk v6 Dev

Kiosk v6 Dev.xls (119 KB)
Kiosk v6.xlsm (77.2 KB)
Kiosk v6.xlam (115 KB)
Kiosk v6.zip (255 KB)
Kiosk.xls - all versions 9-14, download and press F4 (121 KB)





Ribbon Extensibility AddIn XLRibbonX

[Back to Search]

Add Multiple Selection capability, Full Screen Excel, Indexing functionality & easy Worksheet Browsing to all of your Workbooks for Excel 9-14 only. Extensibility (sometimes confused with forward compatibility) is a system design principle where the implementation takes into consideration future growth

XLRibbonX

XLRibbonX is a useful Excel 2007/2010 Add-In that features the Kiosk Full Screen Excel transition as well as providing extended Ribbon functionality for Excel through the use of additional Ribbon Controls. I have called it a Ribbon Extensibility AddIn as I plan to develop this AddIn over time and enrich it with lots of other Controls

Once installed you can explore the added Ribbon Controls. The AddIn has a Paste Multiple Special Button allowing you to Paste Multiple Selections into another Workbook. It has a Kiosk Button to run Excel in Full Screen Kiosk Mode. There is an Index Button which allows you to create a Hyperlink Index of all the Worksheets within the ActiveWorkbook (you can then deploy this Index and distribute it with Workbooks you send to other people). It has integrated Worksheet Browser Buttons to iterate Worksheets either forwards or backwards using the Ribbon. You can also toggle Page Breaks and Unhide all Hidden Worksheets in one go

XLRibbonX.xlam (149 KB)
XLRibbonX.xlsm (91.1 KB)





Colour Banding

[Back to Search]

This Workbook will show you how to apply a Formula to Colour Band your report data every nth Row or every Row by some criteria

To Colour Band report Data every other Row using Formula, select the entire data Range in your Excel Workbook that you want to Colour Band and add a Conditional Format using the Formula below (I selected B6:D105). Change the X in the Formula to the Row Down number that the Selected Range begins (I chose to enter 7 so that the Colour Banding was applied after the first Row within my Selected Range). Change the Y in the Formula to the number of Rows you want to highlight (so for every other Row change it to the number 2). Choose your desired Formatting (I chose a light Grey Fill)

' // Formula to copy - modify the X & Y for your own selected report Range
=MOD(ROW()-X,1*Y)+1<=1
 
' // Example 1, Formula I used in the Example Workbook
=MOD(ROW()-7,1*2)+1<=1
 
' // Example 2, Formula to start at Row 10 and Colour Band every 5 Rows 
=MOD(ROW()-10,1*5)+1<=1

For a working example of How to perform Colour Banding by Mouseover (using the Rollover technique) for Excel 2007/2010 by Bert van Zandbergen download the Colour Banding with Hyperlink Rollover.xlsm Workbook

Colour-Banding.xls (41 KB)
Colour Banding with Hyperlink Rollover.xlsm (25.4 KB)





Colour Pallets

[Back to Search]

Here are some Excel Colour Pallet Workbooks

Reporting Template Pallet.xls (25.5 KB)
Office 2013 Pallet.xls (23.5 KB)





Report Template

[Back to Search]

My Reporting Template is a combination of Excel & VBA. It has a bespoke Colour Pallet including beautiful Hyperlinks that will look fantastic in all Versions of Excel even when a Worksheet is copied into another Workbook. The default Font is Calibri 9pt (which also looks great as you scale it up CTRL+Mouse Wheel Up). To use any of the Formats in my template simply copy & paste the Formatting

Reporting Template.xls (120 KB)





Generate CVCS Using Formula For Demand Planning

[Back to Search]

I was interested to see how easy it was to generate Products x Depots x CPG's (Customer Planning Groups) using Formula (and without using Array Formula). That's AxBxC for all combinations. Well it turns out that is is a little more tricky than I anticipated, in fact searching only served up a number of 2 list combinations and some interesting articles on permutations. However I managed to find some good online resources to get to AxB and then with a tweak to this method I then managed to add the last part to perform AxBxC (I have added the links for online Sources into the Generate CVC's AxB Worksheet). There are probably plenty of ways to arrive at the end result that I outline, but it was satisfying to do the last piece of work myself and prove that it is possible to generate CVC lists using Excel Formula (without Array Formula)

Anyhow, here is my method for generating these combinations using nothing more than (fairly) basic Excel Formula. By the way remember to use your F9 key to examine how the Formula work. Please not: do not delete the Headers - you can edit them so that they make sense for your combination types

To generate thousands of CVC combinations using VBA Code you can purchase my CVC Generator

Generate CVC's using Formula.xlsx (23.3 KB)





Hamilton Largest Remainder Method Tool Phase Split Using Formula

[Back to Search]

This is my latest Phase & Split Tool for Excel. You can use this Tool in Demand Planning to Phase and Split Promotional Volumes for up to 78 Weeks. The Tool implements Largest Remainder Methods for signed numbers in order to prevent loss of significance when rounding. Please note: this Tool does not use VBA - it uses Formula to do both the phasing and splitting

Phasing Step 1
The Tool asks you to enter Unique ID, Products, Codes, etc. (Column 'B') and their respective Integer Values (Column 'C') - we will use the term Products and call the Values, Promotional Volumes. You should begin by phasing some Products. If you only want to phase a couple of Products highlight from Row 13 downwards and delete the Rows. If you want to phase many Products then select the entire last ROW ie. 'A16:RM16' and DRAG down to add more ROWS to the Tool - that way all of the Formula will continue to work correctly including the Sparklines. You can press the little arrow to navigate to the first Cell or click directly into Cell 'B11'. Enter your Products and their respective Promotional Volumes in Columns 'B' and 'C'. Ensure that your Products are unique ie. they only appear once on the Worksheet and that they are sorted ascending or descending. Even though you may be entering positive Promotional Volumes, there may be instances when you want to Phase & Split negative Values - this Tool allows you to enter both positive and negative Values

Phasing Step 2
Once you have entered your Products and their respective Promotional Volumes, you need to setup your phasing. Press the little arrow next to step 2 or click directly into Cell 'D10'. You now need to decide on your phasing shape and enter different percentages (%) for each Week, for up to 78 Weeks. You should enter the percentages like this '10' or '10.5', you do not need to add the '%' sign. All of your percentages must add up to 100% otherwise you will see an alert informing you that something is not right and that the process will fail. A running total is also shown so that you can tell how much you may need to adjust to ensure compliance. You may miss out any Weeks that you like. Please note: you cannot use negative percentages - indeed why would you

Phasing Step 3
Now you can view the results of the phasing by clicking on either of the two litle arrows. The first arrow takes you to the final phasing Values and the second arrow takes you to the phasing shape using Sparklines with Values - my favourite use for this is to snippet out as an image and include in an email to show how you have phased the Volumes. The phasing shape details the shape using Sparklines and shows you both the phasing shape by percentage (%) and by Values

Once you have phased your Products, the next thing that you may want to do is to split down the phasing Promotional Volumes to a lower level so that you can add the data into APO at the lower level or load back into APO by Flat File to avoid loss of significance caused by rounding. Of course you can just let APO aggregate and dissaggregate by adding the new phasing Volumes at a higher level, your choice, but I know which method I prefer

Split Step 1
Click on the "Split" Worksheet. The Tool asks you to enter Unique ID, Products, Codes, etc. (Column 'B') to a level that you want to split to, together with the Sales data (Column 'F') to enable the Tool to perform the splits. Enter your Products and the Sales data ensuring that it is sorted by Product ascending or descending

Split Step 2
Copy down the Formula from the last Cell in Column 'G' to Column 'UG'. The Formula will perform the splits

Split Step 3
Now you can view the results of the phasing by clicking on the litle arrow. You will see that the splits have been performed without any loss of significance through rounding. If you scroll to the end you will see a check SUM to validate that the splits were done correctly. Validation takes place for each change of Product (highest level)

Phase & Split.xlsx (132 KB)





Hamilton Volume And Promotional Product Split Tools

[Back to Search]

This is my Hamilton Volume Product Split Tool for Excel. You can use this Tool in Demand Planning to Split Promotional Volumes across 12 Weeks by the Sales of other Products. The Tool implements Hamiltons Largest Remainder Method so that you never lose any values due to rounding errors. Add your Volumes (Phased or otherwise) to the Top of the Workbook and add some Products with Sales - your Volumes will be Split by the Product Sales using Hamiltons Largest Remainder method. Version 2 allows you to just enter the Promotional Volumes and will Phase these volumes across the Weeks using your percentages and Hamiltons Largest Remainder method

The Steps to Split your Promotional Volumes
1. Add your Products and Product Sales in Cells "B13" & "C13" in the "Phasing Tool" Worksheet. You have 100 Product capacity, but you can extend this to 1,000 by dragging the last Row down
2. Copy & Paste one of the Volume Phasings from the "Phasings" Worksheet into the "Phasing Tool" Worksheet underneath the WK Number Headers - alternatively add your own Volumes directly underneath the WK Number Headers in the "Phasing Tool" Worksheet
3. Your values will be Split using Hamiltons Largest Remainder Method and displayed underneath the "Hamilton Share WK" Number Headers

You do not have to populate all of the Volume Weeks - you can also have disparate Volumes up to 12 Weeks

Hamilton Promotional Volume Product Split Tool.xlsx (109 KB)
Hamilton Promotional Volume Phasing Tool v2.xlsx (21.5 KB)





Hamilton Promotional Volume Splitter Tools

[Back to Search]

You can enter Volumes at any of the CVC points directly into the Other Forecast Adjustment Key Figure in the Demand Planning Book and DP will split these values based upon Proportional Factors (using the last 12 weeks Sales) by Location or by CPG (Customer Planning Group) - aggregating and disaggregating amounts accordingly. Another way is to use a Promotional Uplift Key Figure in DP APO and then load in Promotional Volumes split down to the lowest level i.e. CPG x APO Product x APO Location

The Hamilton method of apportionment is actually a largest-remainder method which uses the Hare Quota or the Droop Quota. It is named after Alexander Hamilton, who invented the largest-remainder method in 1792. These 2D & 3D VBA implementations demonstrate the technique using both the Hare Quota & the Droop Quota. If you want to play with examples of the Hare & Droop Quota then download my Example Workbook from the link below

Promotional Volume Splitter.xlsb (252 KB)
Promotional Volume Splitter (old version).xlsb (124 KB)
CPG Splitter.xlsm (195 KB)
Hamiltons Largest Remainder Method using the Hare & Droop Quota.xlsx (15 KB)





Scrolling Category Chart

[Back to Search]

Use this Workbook to build a scrolling Chart for Key Figures of your choice. The Workbook features Checkboxes to toggle the Chart Series and Legend Entries on and off using VBA. This means that when you turn off a Series then the Legend Entry will also be removed correctly. Please Note: if you change the Primary Axis or add new Series then you may need to check that your Series and Legend Entries are aligned. You can use the Subroutine below:

Scrolling Category Chart

Private Sub CheckSeriesVersusLegend()
 
    Dim cht As Chart
    ' // change the name of your Chart from "CategoryChart" to your Chart name below
    Set cht = Charts.ChartObjects("CategoryChart").Chart
    ' or if the above fails, try... Set cht = ActiveSheet.ChartObjects("Chart 1").Chart
 
    Dim i As Integer
     
    ' // iterate & Select each Series & Legend as a validity check
    With cht
        For i = 1 To .SeriesCollection.Count
            .SeriesCollection(i).Select
            Application.Wait Now + TimeSerial(0, 0, 1)
            .Legend.LegendEntries(i).Select
            Application.Wait Now + TimeSerial(0, 0, 1)
        Next i
    End With
    Application.Wait Now + TimeSerial(0, 0, 1)
 
End Sub
Category Charts.xlsb (477 KB)





CVC Generator

[Back to Search]

APO allows you to create single and multiple CVC's (Characteristic Value Combinations) for CPG's (Customer Planning Groups) by APO Products by APO Locations. You can create CVC's directly in APO or via an Excel Workbook and then Paste them from the Clipboard or upload them from a flat file. The SAP Transaction Code is /n/SAPAPO/MC62 - Maintain Planning-Relevant Characteristic Combinations, Create Characteristic Combinations

CVC Generator

I have developed a simple Tool that makes the process even quicker and easier for CVC creation en masse. First of all you enter or Copy & Paste a new list of APO Products into the Worksheet underneath the APO Products Header. Then you highlight the relevant CPG's and APO Locations you want to generate combinations for. Next, you press the 'Generate' Button to execute the Macro. The Code will output all of the combinations instantly into a new Worksbook where you can then Copy & Paste from the Clipboard or upload from a flat file into APO

You can use this Workbook to generate any number combinations from 3 pieces of data ie. if you entered the numbers 1, 2 & 3 in all 3 Columns and pressed the "Build CVC Matrix" Button, 27 combinations will be built with every possible combination of the numbers 3 X 3 X 3

Please Note: I have a brand new version of this Tool for sale here. It is faster, more powerful and more feature-rich than this free Tool

CVC Generator.xlsm (79.4 KB)





Promotional Volume CSV File Creator

[Back to Search]

We import promotional volumes en masse into a Key Figure in APO Demand Planner Workbook called 'Promotional Uplift'. In order to achieve this we take up to 78 Weekly buckets of promotional volume for a CPG (Customer Planning Group) by APO Product by APO Location (a CVC) and turn the data into a Semi-colon delimited CSV file which is then uploaded daily by an automated process into APO. Duplicate Rows sent out to the CSV File are aggregated automatically by APO. Depending on how APO has been configured you can also add Volumes at any level and the system will aggregate or dissagregate accordingly ie. you could add Promotion Volumes at a Product by Location level and the values will dissagregate to the CPG level so that you have values at all levels (the lowest level)

Promotional CSV File Creator

We see this data in the "Promotional Uplift" Key Figure and Promo Sales (Automatic) Key Figure is calculated whenever we have Sales against a Promotion. If there are any daily changes we write out an updated file ad-hoc otherwise we simply output a new File on the Monday rolling the "Output date" on one Week in the Promo File. The Promo File also uses an ISO Week Numbers Hidden Worksheet for all its Year and Date Calendar Calculations

The Tool I have developed makes the entire process quick and effortless. The Tool requires intermediate Excel skills and advanced VBA knowledge to configure so if in doubt refer the Workbook to your VBA Administrator or IT professional. Once up and running any proficient Demand Planner can utilise its functionality. It can be configured to write out similar CSV file data depending on the specific requirements

Promo File.xlsm (300 KB)





Booking Systems

[Back to Search]

This is Version 7 of my Daily Booking System written for Excel 2010 & Excel 2013. Previous Versions had issues with the Calendar Control so I have written a new Worksheet Calendar to replace the ActiveX Control using a Microsoft Calendar Template. Version 7 is also more powerful, quicker and flexible than previous Versions. Version 7 also includes a blank Booking System with Counts, so when you book something like 'Raincoats' for example, the total quantity is SUMMED and displayed next to the Booking Type

Booking System v7

You can also find some older versions for download below

Booking System with Counts.xlsm (159 KB)
Booking System v7.xlsm (159 KB)
Booking System v6 (unfinished).xlsm (67 KB)
Media Booking System Demo (version 5.1).xlsm (186 KB)
Booking System v5.xlsm (186 KB)
Booking System v4.xlsb (99.7 KB)
Booking System v3.xls (325 KB)





SendMail Template

[Back to Search]

This is SendMail, a Template that you can use to Email Reports and Attachments. SendMail can automate complex Reporting tasks using it's "Woodpecker" system to repeatedly hammer out Emails

SendMail

You can install SendMail as a Template and then add it to any Excel Workbook. Once it is configured you can use it to automate / send individual Worksheets, mail the entire Workbook and use Outlook Signatures without warnings. SendMail is feature-rich and allows you to use a Woodpecker setting to send the same Worksheet with a separate File Attachment to multiple people. You can send Emails using an Excel Range or a HTML Template and also add a Signature. You can Mail individual Worksheets as .PDF Files. The Developer Code can be used in VBA to do more or less the same thing. As the Code will Email without warnings it is perfect to use to fully automate and Email BEx Workbooks

The SendMail Template
Download the SendMail Template from the link below. Configure using the following instructions and start automating the Mailing of your Worksheets, Attachments & Workbooks

Installing the File
When you open the File it will have been reset using the "Reset" Button at the top of the page - this clears and populates all the default settings (except the Woodpecker configuration). The first thing to do is to press the "Install/Uninstall" Button to install the Workbook as a Template. This makes it easy to add to a new or existing Workbook, so press the "Install/Uninstall" Button and press "Yes" to accept. A message will appear confirming the Install. Close the File. Please note: if you use the Automation then you CANNOT install the Software as a Template - instead simply use as an Excel File and Copy into a New or existing Project

Configuring the File
Open a new Workbook. Save the new Workbook as either ".xlsm" or ".xlsb". Right-click a Worksheet Tab and select "Insert...". Locate the File "SendMail.xltm" and click "OK"

Underneath the Configuration Header press the tiny ellipse Button on the left-hand side. An Outlook Profile will pop up. Add the default profile. For testing some defaults have been populated - leave these in situ and move down to the recipient Email Address section at the bottom. Add an email address to the "To List" and press the "SendMail" Button at the top of the Worksheet to send the Email - that's it your first Email has been sent!

Videos
1. Watch a Video on Installing SendMail and sending a simple Text Message
2. Watch a Video on Mailing a Widget Report using SendMail
3. Watch a Video on Mailing a Widget Report as a PDF File
4. Watch a Video on Mailing a Widget Report with an Outlook Signature
5. Watch a Video on Mailing a Widget Report with an Email Template
6. Watch a Video on Mailing a Widget Report with Attachments & Requesting a Read Receipt
7. Watch a Video on Mailing a Widget Report as a Workbook & using an Excel Range as the Body
8. Watch a Video on Mailing a Widget Report as separate Worksheets to multiple people using the Woodpecker functionality

SendMail.xlsm - version 2 (129 KB)
SendMail v1.3.xlsm (123 KB)
Widget Report.xlsm - mail a report (94.5 KB)
Widget Report as PDF.xlsm - mail as PDF (94.6 KB)





Build a Ribbon Toolbar

[Back to Search]

Here is a little Project that shows you how to create a simple Ribbon Toolbar for Excel 2010 & 2013. It builds a minimal Ribbon with a single Tab and some Buttons that navigate to a Worksheet when they are pressed

Ribbon Toolbar

We see this data in the "Promotional Uplift" Key Figure and Promo Sales (Automatic) Key Figure is calculated whenever we have Sales against a Promotion. If there are any daily changes we write out an updated file ad-hoc otherwise we simply output a new File on the Monday rolling the "Output date" on one Week in the Promo File. The Promo File also uses an ISO Week Numbers Hidden Worksheet for all its Year and Date Calendar Calculations

The Tool I have developed makes the entire process quick and effortless. The Tool requires intermediate Excel skills and advanced VBA knowledge to configure so if in doubt refer the Workbook to your VBA Administrator or IT professional. Once up and running any proficient Demand Planner can utilise its functionality. It can be configured to write out similar CSV file data depending on the specific requirements

Video
Please follow the Video for advice on how to build this Toolbar:
Watch a Video on creating a Ribbon Toolbar

Ribbon Toolbar.xlsm 35.6 KB)





Embedding Fonts

[Back to Search]

Here is a Project that shows you how to embed multiple Fonts into Excel as OLE Objects so that you can distribute the Workbook with Fonts that are not usually available as a resource in Excel

Embedding Fonts

How it all works
You download some "ttf" Fonts and then embed them as OLE Objects into Excel. Then when the Worbook is opened the Fonts are automatically copied into the "temp" Folder and added as a resource. When the Workbook is closed the Fonts are removed as a resource

Video
Please follow the Video for advice on how to embed Fonts in Excel:
Watch a Video on Embedding Multiple Fonts into Excel

Embedding Multiple Fonts.xlsb 153 KB)





Kiosk Full Screen Borderless Excel

[Back to Search]

A Kiosk Window is an Excel Application with a single Workbook that is styled as a Bordered or Borderless Window. The Window can be ran either as a Full Screen Window or as a Manual Window sized by an X Co-ordinate, a Y Co-ordinate, a Width and a Height. The Window may or may not have Excel Objects like the Formula Bar or the Workbook Tabs displayed

Kiosk is written for Excel 2010 (32bit) using Windows 7 and Excel 2013 (64bit) using Windows 8.1. It has also been tested as working with Excel 2013 on Windows 10 (19.09.2015)

Kiosk Full Screen Borderless Excel

The Kiosk Example Workbook
Download and open the Kiosk.xlsm Workbook from the link below. Click on each Hyperlink to run any of the Examples. The examples include:

Create a FullScreen Window with a Border
Create a FullScreen Window without a Border
Create a Manual Window with a Border
Create a Manual Window without a Border
Create a Manual Window without a Border & Unhide Stuff


The Workbook exposes these Subroutines that then Call a single Kiosk() Subroutine passing the appropriate Parameters required to style the Window. This routine call also be Called via automation

Kiosk.xlsm
Automation Example for Kiosk.xlsm (15.7 KB)





Pivot Slicer Button Toolbar

[Back to Search]

Here is a little Project that embeds a really nice type of Font into Excel and then uses a Pivot Table and Slicer technique for the Toolbar Buttons. It embeds the Icon-works Font by Tiago. I have also built a Workbook using an OLE Embedded Font

Pivot Slicer Button Toolbar

Toolbar Character Set

Video
Watch a Video for the Pivot Slicer Button Toolbars:
Watch a Video on using the Pivot Slicer Button Toolbar

How it all works
The Workbook automatically installs the icon-works-webfont.ttf (Icon-Works Regular) Font when it opens by copying it to the Users Temp Folder and then adding it to the Excel Font Resource. The Font is then automatically removed when the Workbook closes

A Pivot Table is included on "Sheet2". The Slicer is used for the Toolbar on "Sheet1". Hover over a Button. The Pivot Table with Slicers Buttons technique is really nice in that it allows you to resize the Columns without effecting the Toolbar Buttons. Notice that you can only select 1 of the Slicer Buttons at a time - try pressing CTRL or SHIFT. Press ALT+F11 to view the Code. There is a section marked where you can link to the Buttons

Pivot Toolbar.xlsb (307 KB)
Pivot Toolbar (OLE Embedded Font).xlsb (74.9 KB)
Pivot Toolbar.xlsm (39.6 KB)





Spinner PixelBuddha Preloaders

[Back to Search]

Spinner is a beautiful set of 5 PixelBuddha Preloaders for Excel 2010 & 2013 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. Download the Spinner.xlsm from the link above and see the Demo in the File or read this VBA Project article for more information. Check out the raw HTML Markup (Spinner fade in and out.html) with embedded Base64 encoded Animated GIF data that was used to create the Spinners for this VBA Project at the top of this article - you can also see HTML that allows you to Right-click and View the page source

Spinner

Spinner Rotator

Spinner Newton's Cradle

Spinner Swirl

Spinner Pulse

Spinner in action

The guys at Spreadsheet1 have taken the Spinner Project and improved the data storage, added additional Spinners and created a stunning visual demo of Flat Animated Loaders for Excel.
You can view it at:
http://www.spreadsheet1.com/flat-animated-preloaders-spinners-for-excel.html

Creating a Spinner
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. MyDoEvents allows the Excel Application to process more regularly than the normal DoEvents and gives better Control - the downside is that it may slightly increase the task running time as it checks the Windows Messaging system more frequently

' // 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
Video
Watch a Video for the PixelBuddha Preloaders:
Watch a Video for the PixelBuddha Preloaders

Spinner.xlsm (1.03 MB)
Spinner UserForm.zip (284 KB)
Single Spinner.xlsm (431 KB)
Spinner fade in and out.html
Spinner fade in and out (source).html





Nanoscopic VBA Progressbar

[Back to Search]

Display the status of a determinate process - VBA Progressbar is a nanoscopic Progressbar (on a transparent UserForm) with Spinner (Base64 embedded animated GIF) and Pause, Resume & Abort Controls. Thanks to Ejaz Ahmed for inspiring me on progressbars again

Nanoscopic VBA Progressbar

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

Introducing the Nanoscopic Progressbar
Download and open one of the 'first two' Progressbar Files using the links below. Run one of the demos included with the File to view the Progressbar. The Progressbar is a very tiny nanoscopic bar with an animated Spinner and Pause, Resume & Abort Controls that can be displayed for a determinate task

You can choose whether or not to display the Spinner or any combination of Controls. Here is an image of just the Progressbar with a Bar Colour 'rgbRedDevil' without any Controls or Window Border. By passing rgbWhite in the Progressbar.Display Function for the Optional Parameter TransparentColour you can effectively render the entire background of the UserForm transparent so that there is never a background on any Cell Colour you display the Progressbar upon

Nanoscopic VBA Progressbar without Controls

Progressbar.xlsm (102 KB)
Progressbar.xls (177 KB)
Progressbar UserForm.zip (15.3 KB)

Deprecated Progressbars
Non-Modal Progressbar Self-contained Markup.xls (66.5 KB)
Non-Modal Progressbar with HTML Sheet Markup.xls (71.5 KB)
Modal Progressbar with HTML Sheet Markup.xls (71 KB)





Filterlist Fast Flexible Searchable List Control

[Back to Search]

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

Filterlist

Filterlist Change Text and Display Options

Filterlist Standard Theme

Initialise a Filterlist
To initialise the Filterlist using vba in excel you would typically use the following code to initialise and load the userform into memory (some of the parameters are optional):

Filterlist.Initialise ListWorksheet:="demo", _
                               ListStartCell:="b11", _
                               Display:=DisplayItemsKeys, _
                               Behaviour:=LinkCellToSearchTermAndKey, _
                               LinkedCell:="demo!H1", _
                               SortList:=PersistSortAscending, _
                               EnterBehaviour:=True, _
                               Capture:=vars, _
                               Caption:="Filterlist featuring the ''Stack Overflow'' theme", _
                               Title:="Select a Product", _
                               Width:=320, _
                               Height:=291.75, _
                               HideControls:=HideFuzzyControl + HideCaseSensitivityControl + HideDisplayOptionsControl
Displaying a Filterlist
To display the Filterlist as a Modal or Modeless dialog you would use the following code:
' // display the Filerlist as modeless
If Filterlist.Loaded = True Then _
 Filterlist.Show (0)
 
' // display the Filerlist as modal
If Filterlist.Loaded = True Then _
 Filterlist.Show (1)
Themes
There are 4 different Themes available to use for Filterlist. You can easily modify or add Code to create your own bespoke Themes. To display a different Theme you would insert the following code before a call to Initialise:
' // ''StackOverflow'' Filterlist theme
Filterlist.Theme StackOverflow ' SAP, Frostee, Reporting
Video
Watch Filterlist on YouTube

Filterlist.zip (1.08 MB)
filterlist boilerplate.xlsm (1.13 MB)
filterlist autofilter boilerplate.xlsm - an example of using Filterlist to Filter data (287 KB)





Preloader Transition Using Spin JS Library By Felix Gnass

[Back to Search]

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 Note: I now recommend using an alternative DoEvents procedure with the Preloader. This allows the Preloader to run nicely while performing any VBA routines

Preloader

Preloader Microsoft Yellow

Preloader Nero Fast Circle

Features
Create custom Window Styles including Borderless, Framed & Dialog Windows
Change the Style, Colours, Size, Lines, Line Colour, Corner Roundness, Inner Circle Radius, Speed & Trail Afterglow of the Preloader
Control the Preloader Fade In, Duration & Fade Out times
Import the BAS files into a new or existing VBA Project
Use the Fiddle Code to generate any kind of Preloader and then quickly Copy & Paste the resulting VBA Code into any Code Module
(Method 1): Using the Preloader in the Real World - you can now turn off all user Interaction Including Mouse & Keyboard
(Method 2): Using the Preloader in the Real World - you can now switch Workbooks, Worksheets, perform Cell Drag & Drop (with limitations) and allow Data Entry

The Preloader Subroutines
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 Transition()
 
    ' 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

Start & Stop the Preloader
Starting & Stopping allows you to run your own Code whilst the Preloader is animating. This involves initialising the Preloader which loads the UserForm into memory and then running the Start() and Halt() Subroutines. There are now 2 preferred methods of running a Preloader whilst completing a task

USING THE PRELOADER IN THE REAL WORLD - TURNING OFF ALL INTERACTION INCLUDING MOUSE & KEYBOARD (METHOD 1)
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)
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
Add Preloader to a VBA Project
Preloader can be added to any VBA Project. To do this follow these steps:

1. Extract the Files from the ZIP archive and open your Workbook
2. Press ALT+F11 to go into the VBA Editor
3. Click on ''File'' and then ''Import File...''
4. Browse to locate the Preloader.frm (BAS) File and open the File

The UserForm will be displayed in the VBA Editor underneath the Folder ''Forms''
Click on the Preloader UserForm and press F7 or double-click the UserForm. Take some time to examine the Code and the Subroutines that make up the Preloader UserForm Code Module. Add the Transition Subroutine above to test the Preloader

Using an Alternative DoEvents
You can use 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. Just add the Code below into any Code Module including the API declarations. You can call this routine any time by entering:
' // 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
 
' =============================================================================================================================================================
' ## 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

Videos
Watch a Video on Preloader
Watch a Video on Preloader Nero Fast Circle
Watch a Video on a Preloader Workaround to Allow Cell Entry & Worksheet Switching
Watch a Video on Preloader Red Devil
Watch a Video on Using the Preloader Fiddle Code

Preloader.zip (330 KB)
Preloader.xlsm (322 KB)
VBA Projects - Preloader.xls - Blank Preloader Workbook (139 KB)





BI__RIBBONX Format BI Portal Report Data AddIn

[Back to Search]

BI__RibbonX adds a small Control next to the Styles Group on the Home Tab of the Ribbon. It can be used to format a BI Portal Report data extract using Styles and has lots of additional settings that you can apply via an Options Dialog. Once formatted you have the option to either Save the extract or have it copied to the Clipboard ready to Paste into your Report. The AddIn is protected only so that the Project does not expand every time that you enter the VBE - I have included the source File BI__RibbonX.xlam so that you can examine the Code

Version 1.1 has been modified for Formatting large extracts of 150,000 plus Cells. It also no longer adds the Styles automatically to the extract - an option is available to add the Styles into a Workbook if they are required

Quick Install for Excel 2010 & 2013 if you already have Macros & VBOM enabled: Download the AddIn by right-click 'Save link as...' or 'Save target as...'. Right-click on the AddIn and select 'Copy'. Open Excel. Press ALT+F and click 'Options'. Click 'Add-ins'. Press the 'Go...' Button. Press the 'Browse...' Button. Right-click Paste. Double-click to select the AddIn. Click OK

BI__RibbonX.xlam (52.9 KB)
BI__RibbonX.xlsm (56.9 KB)
BI__RibbonX (1).xlam (41.6 KB)
BI__RibbonX (1).xlsm (56.2 KB)

BI__RibbonX



BI Portal Report Before Formatting



BI Portal Report After Formatting





Little Chart Widgets

[Back to Search]

Here are five different types of simple little Chart Widgets, Doughnut, Line, Scatter (2x2), Stacked Column and Area. The Doughnut Chart can be used to display the percentage in a Doughnut. The design allows you to select the individual layers in the "Selection Pane" of the "Arrange" Group of the "DRAWING TOOLS" Tab on the Ribbon. You can change the Colours of the Doughnut including the background Circle and Percentage for the Chart. These little fellows look nice in Excel at any Zoom level. The Line Chart is a Value versus Month Chart where you can plot a single Series against a half Year. You can add more Series or lengthen the Months if required. These are nice to show Quarterly Sales patterns and are visually very pleasing. The Stacked Column Chart stacks 2 Series in Columns for 12 Months of the Year. The Area Chart layers 2 Series for 12 Months and 4 Quarters of the Year. I have kept all of the Colours the same and provided the RGB values. The settings are all clearly marked with Series Values shown as 'Input' Style and Calculations show as 'Calculation' Style. I have deliberately kept these little Charts plain - you can add Titles, Axis Tiles etc.

Required: Excel 2010 / 2013

Screen Shots
Doughnut Chart Excel Screenshot
Line Chart Excel Screenshot
Scatter Chart Excel Screenshot
Stacked Column Chart Excel Screenshot
Area Chart Excel Screenshot

Editing the settings
On the "Doughnut" Worksheet you will see a 2 Doughnut Charts linked to the Values underneath the 'Chart Settings' Header. The grey Cells with bright orange Font are the Calculation Cells used to derive the Percentage (%) and should not be touched. The Input settings are the orange Cells - these are the Actual and Max Values. Leave the 'Max' setting as '100' and change the 'Actual' setting to a number from 0-100. You will see the Doughnut Chart adjust to the new Percentage shown next to the 'Percent' setting. The 'X' and 'Y' setting Caluclations are used by the Doughnut Chart Series 1

Zooming the Doughnut Chart
Try Zooming the Chart by changing the 'Zoom' level using the '-' and '+' buttons at the bottom right of the Excel Workboo to 'Zoom in' and 'Zoom out'. The Doughnut Chart should look nice at all Zoom levels

Moving the Doughnut Chart
You can move the Chart by grabbing the 'Plot Area' and dragging it to a new location on the Worksheet. You can move the Chart to another Worksheet by selecting the Chart Group and pressing CTR+X to Cut the Chart and associated Objects. Navigate to a different Worksheet within the Workbook and press CTRL+V to Paste the Chart and associated Objects. The Colours used for the Doughnut slices are shown as RGB Values

Changing the Doughnut Chart Colours in Excel 2013
In Excel 2013 you can change the Doughnut Colours by clicking once over the Chart and then clicking again in the middle of the Chart to select the Chart Object itself. On the 'CHART TOOLS' Tab, select the 'FORMAT' Tab and click 'Format Selection' on the 'Current Selection' Group on the Ribbon. You will see the 'Format Plot Area' sidebar slide out on the right-hand side of the Excel Workbook - adjust the Colours as neccessary by clicking on each slice of the Doughnut on the Doughnut Chart Object and changing the 'FILL' and 'BORDER' Colours

Changing the Doughnut Chart Colours in Excel 2010
In Excel 2010 you can change the Doughnut Colours by clicking once over the Chart, then clicking again. On the 'Chart Tools' Tab, select the 'Format' Tab and click 'Format Selection' on the 'Current Selection' Group on the Ribbon. You will see the 'Format Plot Area' Dialog appear. Now select a Doughnut slice on the Doughnut Chart and it will change to the 'Format Data Series' Dialog. Adjust the Colours as neccessary for each slice of the Doughnut Chart by changing the 'Fill' and 'Border Colour' / 'Border Styles' on the individual Tabs of the Dialog

Using the Selection Pane to view the Assosiated Grouped Objects for the Doughnut Chart in Excel 2013
In Excel 2013 click once on the Doughnut Widget (top, left-hand side). You will see the 'DRAWING TOOLS' Tab appear on the Ribbon. Select this and you will automaitcally be in the 'FORMAT' Tab. Now click on the 'Arrange' Group and press the 'Selection Pane' to slide out the 'Selection' Dialog on the right-hand side of the Worksheet. You will see 2 Dougnuts with grouped associated Objects. Underneath either 'DoughnutChartA' or 'DoughnutChartB' you can click 'Circle' to select the Shape used for the Percentage or 'Doughnut' to select the Chart used to display the Doughnut slices. Once you have selected the individual Objects, then you can adjust any of their settings. You can also click 'Group' on the 'Arrange' Group of the 'FORMAT' Tab on the Ribbon to Ungroup the associated Chart and Objects - this will leave you with the 'Chart' and the 'Circle' Shape

Using the Selection Pane to view the Assosiated Grouped Objects for the Doughnut Chart in Excel 2010
In Excel 2010 click on the Widget (top right-hand side) and then select the 'DRAWING TOOLS' Tab that appears at the top of Excel. On the 'Arrange' Group click 'Selection Pane' to bring up the Objects available to select on the 'Selection and Visibility' Pane (right-hand side). You will see 2 Dougnuts with grouped associated Objects. Underneath either 'DoughnutChartA' or 'DoughnutChartB' you can click 'Circle' to select the Shape used for the Percentage or 'Doughnut' to select the Chart used to display the Doughnut slices. Once you have selected the individual Objects, then you can adjust any of their settings using right-click or any of the available Ribbon Groups. You can also click 'Group' on the 'Arrange' Group of the 'FORMAT' Tab on the Ribbon to Ungroup the associated Chart and Objects - this will leave you with the 'Chart' and the 'Circle' Shape

The Line Worksheet - Editing the Chart Series
On the "Line" Worksheet you will see a 2 Line Charts linked to the Values for the 'Line1' Series. The Values for the Series are the orange 'Input' Cells - these are shown for JAN through to JUN, 6 Months. Try changing the Values - the Charts will adjust to your new settings. To see how the Chart uses the Series, click anywhere on the Object and then use right-click, 'Select Data...' to bring up the 'Select Data Source' Dialog

The Scatter (2x2) Worksheet - Editing the Chart Data
The data used to build this Chart is fairly complex and is built from an excellent example by Jon Peltier which goes into great detail around how to design these type of Scatter plot grids. You can find the article here: Scatter XY Shaded Quadrant Chart

The Stacked Column Worksheet - Editing the Chart Series
On the "Stacked Column" Worksheet you will see a 2 Stacked Column Charts linked to the Values for the 'Column1' and 'Column2' Series. A Stacked Column Chart builds a Column Chart by 'Stacking' each Series ontop of the other. The Values for the Series are the orange 'Input' Cells - these are shown for J (January) through to D (December), 12 Months. Try changing the Values - the Charts will adjust to your new settings. To see how the Chart uses the Series, click anywhere on the Object and then use right-click, 'Select Data...' to bring up the 'Select Data Source' Dialog

The Area Worksheet - Editing the Chart Series
On the "Area" Worksheet you will see 2 different Area Charts, 'Area Chart A' and 'Area Chart B'. These are linked to their respective Series, 'Area1' and 'Area2'. An Area builds a graphical Chart by 'Layering' each Series ontop of the other (similar to the Stacked Column Chart but as a filled Area of Colour). The Values for each Chart Series are the orange 'Input' Cells - these are shown for J (January) through to D (December), 12 Months for 'Area Chart A' and Q1 (Quarter one, January, February & march) through to Q4 (Quarter 4, October, November & December). Try changing the Values - the Charts will adjust to your new settings. To see how each Chart uses the Series, click anywhere on one of the Objects and then use right-click, 'Select Data...' to bring up the 'Select Data Source' Dialog

Using a Chart in your own Workbook - Moving the Charts into your own Workbook
To move any of the Charts in your own Workbooks, you can Copy in the entire Worksheet containing the Chart that you want. To do this open 'LittleChartWidgets.xlsx' and your own Workbook. Select the 'LittleChartWidgets.xlsx' Workbook and right-click on the Tab of the Worksheet that contains the Chart that you want to Copy. Select 'Move or Copy...'. On the 'Move or Copy' Dialog, underneath 'Move selected sheets' and 'To book:', select your Workbook (this is the Workbook where we will create the Copy of the Chart). Then underneath 'Before sheet:' select the Worksheet where you want to insert the Copy. Now ensure that you tick the 'Create a copy' Checkbox at the bottom of the Dialog and click 'OK' to Copy the Worksheet. The Chart will now be added into your Workbook as a new Worksheet. Now you can change the input Values or link the Charts to your own Series or input Values

Using a Chart in your own Workbook - Copying a single Chart into your own Workbook and using your own Values as the input Value or Series
To Copy a Chart into oyour own Workbook and then link up the Chart Value or Series to your own input Value or Series, you can do the following. Select the Chart you want in the 'LittleChartWidgets.xlsx' Workbook and press CTRL+C to Copy the Object(s) to the Clipboard (in the case of the Doughnut Chart Widget you will need to select the Group of Objects). Now switch to your Workbook and select the Worksheet that you want to Paste the Chart into - click CTRL+V to Paste the Object(s). The Chart will appear in your Workbook, but the Data Series will still link to the 'LittleChartWidgets.xlsx' Workbook. To link the Series, click anywhere on the Object and then use right-click, 'Select Data...' to bring up the 'Select Data Source' Dialog. Now Edit the Series, for example, using the Doughnut you just confirm the 'Series values' to your own 'X' and 'Y' Values (you will need to switch Workbooks as you do this as by default Excel will switch Workbooks when you try to select your new Value). In the case of the Doughnut Widget Chart you will also need to select the 'Circle' Shape Object that displays the Percentage (%) Value and then change the Source Cell link to the Value (derived Percentage) in your own Workbook as well as link the Series for the Chart (use the 'Selection Pane' on the 'Arrange' Group of the 'FORMAT' Tab on the Ribbon. If the Colours change for the Doughnut, then you will need to adjust these to your own Colours or to the Colours that I use in the Examples in the 'LittleChartWidgets.xlsx' Workbook

Using a Chart in your own Workbook - How I add the Charts when I want to use them in another Project
I prefer to Copy in the Worksheet for the Chart I want to use. I then use CRTL+X to Cut the Chart to the Clipboard and then use CTRL+V to Paste it into the Worksheet I want it on. This way I get the Chart in my Workbook and the inputs / calculations stored in the Worksheet that I copied in. Then you can always reference these inputs / calculations from another Sheet if required and then hide the Worksheet

LittleChartWidgets.xlsx (74.7 KB)





Dynamic Chart

[Back to Search]

This is my Dynamic Horizon Chart with Series Toggle for Excel. You can view any Horizon in your data Range by Selecting the Horizon Start and End Buckets. You can Toggle Series on/off using the Checkboxes. Adjustments to this Worksheet are fairly straightforward as there is no VBA Code. The Dynamic Chart works by referencing a copy of the data using Formula which also prevents Blank values from being plotted on the Chart. Other settings are used by 6 Defined Names, 1 for the Horizon Buckets and then 5 more, 1 for each Series of data. These Defined Names use a Cell Start Reference, the number of Columns wide (X-Offsets) and an Offset for the Series position (the Y-Offset). You can add more or remove Series with minor modifications to the Worksheet

Required
Excel 2010 or Excel 2013 (32bit or 64bit), working with Windows 7 & 10
Knowledge of Excel Formula and Named Ranges (Defined Names) if you want to develop this for other Projects. You can Copy & Paste your own data with minor adjustments to the Worksheet and you are good to go

Features
Select any Horizon to display the buckets dynamically on the Chart ie. M 01.2015 - M 12.2018
Toggle any of the Chart Series on/off using Checkboxes
Paste new data in Quarters, Weeks or Days with only minor modifications to the Worksheet
Add/remove Series with only minor modifications to the Worksheet

Screen Shot

Dynamic Chart Screenshot

Using the Dynamic Chart
Open the Workbook. The License can be removed as long as an attribution is left near the Chart Settings (these may of course be hidden if you wish). Test the Workbook by selecting a different Horizon. Try toggling the Series on and off. The settings that control the Chart, the Checkboxes and the dynamic Chart Series are shown further down in the "Sheet1" Worksheet. Here is the same Chart with a shorter Horizon and the 'Baseline Forecast with C Seasonality' Series removed (I have also removed the yellow information labels). Unfortunately there is no way without VBA to toggle the Legend Entry, so that will remain even though the Series is not displayed:

Dynamic Chart Short Horizon Screenshot

The Chart Series Types
The Chart is a Combination Chart. It uses these Chart Types: 'Line', 'Line with Markers' and 'Area'. THe 'Line' Chart Type is used for the 'Final Forecast' Series. The 'Line with Markers' Chart Type is used for both the 'Baseline Forecast with C Seasonality' Series and the 'APO Baseline Forecast' Series. The 'Area' Chart Type is used for the 'Total Sales Last Year' Series

Changing the Data for Your Data
Obviously you will not want to use my data for your Chart, so here I will detail how to change the data to be your own data of different Series and of different Buckets ie. Weeks, Days etc. For this example I will chage the Buckets to be Weeks. I will change the Series data for the first 3 Series to be 'Total Sales', 'Total Sales Last Year' and 'Final Baseline Forecast' - we will ignore the last 2 Series completely. The first Series will become 'Calendar year/week'

Step 1. Paste your new data over the existing data

Ensure that the data is aligned in the sense that you have an extra Column (see Column B below which is Blank) where I have 'Unit' in the Dynamic Chart Workbook and then Copy the new data and Paste Special Values over the existing data like this:

Dynamic Chart New Data Screenshot

Don't worry that you receive the message shown below - we will fix that in a minute:

Dynamic Chart Chart Message Following Paste Screenshot

Click okay to the warning message. Type in the first and last Bucket of your first Series (this should always be the Time ie. Years, Months, Quarters, Weeks or Days etc.) into the Horizon Start Week and Horizon End Week Drop-downs. Click through any additional warning messages. Oh' and depending on your data, remember to use an aposthrophe if the leading digit is a zero (0) like this '01.2015. Also ensure that you do indeed enter a valid Bucket for both Horizons otherwise Excel will tell you that the entry has been restricted. The Chart will now spring into life like this:

Dynamic Chart Update Horizons Screenshot

Clear the 2 Series that we do not want to show and remove the Checkboxes from the view. Click on the Chart Legend and highlighting the unwanted Legend Series one at a time, press Delete. Select a different Horizon. My NEW Chart looks like this:

Dynamic Chart New Chart Screenshot

Hiding the Settings
You can Hide the settings data by selecting Row 46-80 and then using right-click->Hide. Unfortunately hiding any of the copy data for the Series will prevent the Series from being displayed on the Chart - you can of course simply change the Font Colour for this data to white and change the Calculation Style to 'Normal' as a workaround

Moving the Settings or Rescoping the Defined Names
You can Move the settings within the current Worksheet. Unfortunately, you cannot Move these settings to another Worksheet. The Defined Names are scoped to the 'Workbook' and cannot be rescoped to the 'Worksheet'

Copying the Chart into Another Workbook
You can of course Copy this Chart into another Workbook by right-clicking on the Tab and using 'Move or Copy...'

A Word on Horizon Start & End Weeks
The Worksheet uses Defined Names to dynamically build the Horizon Start and End Weeks. This is to ensure that a user cannot select a Start Week that is greater than an End Week otherwise the Chart would lose its Series and corrupt. If you would like to just use the full Horizon for both Start and End Weeks, simply use the Data Validation for List and Select the entire Horizon of your data (removing the Denfined Names). Now you will be able to pick any Buckets for the full Horizon

Changing the Worksheet Name
You can change the Worksheet Name and the dynamic Named Ranges will reflect the change with the Chart Series picking up on the new Sheet Name. The Formula that does this is in the Chart Settings Section

DynamicChart.xlsx (35.4 KB)