Version 1
If you have already purchased this Software, let me take a moment to thank you for being a loyal customer
You are entitled to free lifetime updates for ALL future builds
This documentation is to help you understand the Software and to give you a more meaningful insight into what it can do. Please go through the documentation and read it carefully · Basic Excel and some VBA skills are required
You will need the following Excel Version & Prerequisite to use this Software
No support is provided for customization or development of this Software
My mission is to take your Excel UI to another level. You can purchase all of my Material UI and Code Examples in a single UI Pack from the link above or use your own images with any of the Effects Code (where provided). All of my Icons (lightweight SVG Files 3KB) can have both Fill and Outline Colour set in Excel. They are fully responsive and can be added to Excel as a 'Graphic' via Insert->Pictures on the Illustrations Group of the Insert Tab on the Ribbon. These Icons are similar to the new Icons available in Excel 2016 with an office 365 subscription
I also include the EMF Files which are even smaller at 1KB without Transparency so you can add a Shadow without any transparent areas taking on the effect. EMF Files are also fully scaleable maintaining crisp lines no matter what the size
With the UI Pack I also include a Worksheet for the Material UI Teal Fill & Font Colours including links to Websites with Material UI and Colour Harmony. Protection is added to any Code where Shapes are used. I use a method that allows Code to run and also allows you to edit anything except Shapes. To remove this Protection, click 'Unprotect Sheet' on the 'Protect' Group of the 'Review' Tab on the Ribbon. This method is only available to you when you purchase my UI Pack
Right-click on the xlui.zip File and extract the contents. Open each example Worksheet and examine how the UI works. Press ALT+F11 to view the Code. Use this online Help to find out more information on each Worksheet. The image below details the current xlui.zip File contents (4 .EMF Files, 14 .SVG Files & 7 Workbooks):
When you purchase the UI Pack you get SVG (Scalable Vector Graphic) Icons which are XML-based vector image formats for two-dimensional graphics with support for interactivity and animation. You also get EMF (Enhanced MetaFile) Icons which are native vector formats for Microsoft Office applications. SVG Icons can have both Fill and Outline Colours set in Excel and are fully responsive - at 3KB (expanded decimal places) they are small Files. EMF Icons are even more lightweight at 1KB. EMF Icons can have their Colour (limited), Contrast and Brightness adjusted and are fully responsive - they are useful when you want to add outline Shadow effects as any holes in the Icons have a White Fill and do not accept the internal Shadow
The Burger Menu Icons (Burger and Close Cross). The SVG default Fill is Teal, #4DB6AC, RGB(77, 182, 172) and the Outline is Black. Both the Fill Colour and Line Colour can be changed in Excel to anything that you want. Here is the final Burger after I have set the 'Graphic' outline to 'No outline', added a light Teal Fill for the background Cell and resized it to 1 cm:
The Toggle Button Icons (Minus and Plus or Collapse and Expand). The SVG default Fill is Teal, #4DB6AC, RGB(77, 182, 172) and the Outline is Black. Both the Fill Colour and Line Colour can be changed in Excel to anything that you want. The hole in the center of the Buttons is transparent. Here are the final Toggle Buttons after I have set the 'Graphic' outline to 'No outline', added a light Teal Fill for the background Cell and resized them to 0.8 cm:
The EMF Toggle Buttons default Fill is Teal, #4DB6AC, RGB(77, 182, 172). The Minus and Plus Fill is White. You can only adjust the Contrast and Brightness of these Icons, however they do take a Shadow or other effects well due to the fact that there are no transparent holes. You can adjust the Shadow effect to be darker or lighter if required. They are also Responsive and have a slightly smaller File Size of just 1KB
The Table Toggle Button Icons (Up and Down Arrows or Collapse and Expand). The SVG default Fill is Teal, #4DB6AC, RGB(77, 182, 172) and the Outline is Black. Both the Fill Colour and Line Colour can be changed in Excel to anything that you want. Here are the final Toggle Buttons after I have set the 'Graphic' outline to 'No outline', added a light Teal Fill for the background Cell and resized them to 1 cm:
These 5 SVG Icons are used to demonstrate my Click Effect. Both Fill and Outline Colours can be adjusted in Excel
Whenever you use an Icon in Excel as a Button you can add a Click effect by dynamically creating an expanding Circle together with a transparent fade animation, giving the user a much more pleasing experience. Animation effects can be used in Excel to Collapse and Expand Rows or Columns in Excel or toggle Table Rows. I will show you some basic free techniques using Code, however, if you want to use the really cool Click effects demonstrated in the Videos, then you will need to purchase my UI Pack
My Toggle button consists of 2 overlaid Shapes using Vector images for the Expand and Collapse buttons. When you click the Expand button, hidden Rows in the Worksheet will be unhidden revealing the Row contents and when you click the Collapse button they will be hidden again. A UI Click effect is made by dynamically creating an expanding Circle together with a transparent fade animation. The UI can be used on Tables and it can also be used on any Colour background due to the shadow effect allowing it to stand slightly proud from the surface
With this UI Element you get an Excel Example File demonstrating the technique on Rows and Tables, the 2 SVG Icons that can have both Fill and Outline Colour set in Excel and 2 EMF Icons. I also include the ClickEffect() Subroutine used in the Video, where you can pass animation Size, Colour, Speed and Growth.
You can purchase the Toggle effect as part of my UI Pack from the link above including all of the Code and images with Code to Expand & Collapse Columns as well. If you want to have a go and develop something yourself, then here is the Code to Toggle any 2 Shapes and any Row Range on the Active Worksheet - add this Code to the Worksheet Code Module ie. 'Sheet1'. Shapes must adhere to the following Naming conventions ie. '6:11|Toggle' '6:11|Toggle1', '10:25|Foobar', 10:25|Foobar1' etc. where the first part is the Row Range to Toggle (which should match similar Rows for each pair), followed by the '|' pipe symbol, then any non numeric Name - the second Shape having the '1' suffix (link as many pairs of Shapes as you require to this single Macro):
Public Sub Toggle() With Application Shapes(.Caller).Visible = msoFalse If Right(.Caller, 1) = 1 _ Then Shapes(Split(.Caller, "|")(0) & "|" & Replace(Split(.Caller, "|")(1), 1, vbNullString)).Visible = msoTrue _ Else Shapes(.Caller & 1).Visible = msoTrue Me.Rows(Split(.Caller, "|")(0)).EntireRow.Hidden = _ IIf(Me.Rows(Split(.Caller, "|")(0)).EntireRow.Hidden = False, True, False) End With End Sub
My Rollover Toggle button is the same as the Toggle button except that when you hover on the Shape it changes the Fill Colour. When you move away, the original Fill Colour is restored. It uses the Rollover technique first identified by Jordan Goldmier. The Hyperlink function takes 3 Parameters, the Button Name, the original button Colour and the Rollover button Colour
Of course, using the Rollover with the Toggle buttons to toggle Rows leads us on naturally to my Rollover Treelist Toggle button. Exactly the same as the Rollover Toggle button except I format the Cells to be merged and add tiny Node Hairlines. There is no extra Code, it is exactly the same as the Rollover Toggle Code - the only difference is that I add more Shapes and name them accordingly ie. '13:17|ToggleA', '13:17|ToggleA1', '13:17|ToggleB', '13:17|ToggleB1'
My Table Toggle button consists of 2 overlaid Shapes using Vector images for the Down and Up Arrow buttons. When you click the Down Arrow, the Table Rows will be hidden. Clicking the Up Arrow will restore the Table Rows. A UI Click effect is made by dynamically creating an expanding Circle together with a transparent fade animation. You can use either SVG Icons or EMF Icons. In this example I have unchecked the Filter button for the Table, but you can still display and use this if you want. For a Toggle on Table Columns, using Left and Right Arrows (Up/Down Arrows that have been rotated) a specific Column has been set to be hidden and unhidden in the Code. You can Collapse and Expand the entire Table Columns if you like - the whole Table will dissapear, leaving only the Right Arrow button
You can purchase the Table Toggle effect as part of my UI Pack from the link above which includes the Table Toggle Columns Code. If you want to have a go and develop something yourself, then here is the Code to Toggle a Table using any 2 Shapes - add this Code to the Worksheet Code Module ie. 'Sheet1'. Shape pairs must adhere to the following Naming conventions ie. 'Table1|Arrow' and 'Table1|Arrow1' where the first part is the Table Name, followed by the '|' pipe symbol, then any non numeric Name - the second Shape having the '1' suffix (link as many pairs of Shapes as you require to this single Macro):
Public Sub ToggleTable() With Application Me.Shapes(.Caller).Visible = msoFalse If Right(.Caller, 1) = 1 _ Then Me.Shapes(Split(.Caller, "|")(0) & "|" & Replace(Split(.Caller, "|")(1), 1, vbNullString)).Visible = msoTrue _ Else Me.Shapes(.Caller & 1).Visible = msoTrue Me.ListObjects(Split(.Caller, "|")(0)).DataBodyRange.EntireRow.Hidden = _ IIf(Me.ListObjects(Split(.Caller, "|")(0)).DataBodyRange.EntireRow.Hidden = False, True, False) End With End Sub
The button Click Effect is used throughout all of the examples and is only available when you purchase my UI Pack. The ClickEffect() Function takes 4 Parameters, Size, Colour, Speed and Growth. The Size is the initial diameter of the Circle prior to Growth. The Colour is the Colour of the Circle which is always set at a Transparency of 0.8 prior to the Fade (you can adjust this in the Code if you need to but this is set by default). The Speed is the rate at which the Circle is expanded before it disappears. The Growth is a Coefficient that adds both Width and Height in pixels as the Circle animation runs to enlarge the Circle. With the Click example Workbook, you also get the 5 SVG Icons, Stop, Play, Pause, Next & Previous to use as buttons
My Code Cell Rollover technique uses VBA to highlight the 'Rolled over' Cell. It prevents a double-click of the Cell (F2 still allowed). It only fires once and uses a Timer to detect Cells outside of the Rollover Cells to turn off the highlighting - this effect is demonstated when you purchase my UI Pack
Here is the Formula and Code to do the Code Cell Rollover highlighting part, however it will not 'Clear' the Rollover Cell - for this you need to purchase this Rollover effect as part of my UI Pack from the link above. The Formula can be Copied anywhere in your Worksheet - change the "Button1" occurances to anything you want for the Cell Text. The Code must be added to a Standard Code Module - change the 2 Long Variables to any Colours that you want, otherwise they will use the same Colours as the image above
=IFERROR(HYPERLINK(foobar(),"Button1"),"Button1") Option Explicit Private CellAddress As String Public Property Get LastCellAddress() As String LastCellAddress = CellAddress End Property Public Property Let LastCellAddress(ByVal value As String) CellAddress = value End Property Public Function foobar() If LastCellAddress = vbNullString Then LastCellAddress = Application.Caller.Address If Application.Caller.Address <> LastCellAddress Then Range(Application.Caller.Address).Interior.Color = 15856352 Range(LastCellAddress).Interior.Color = 12897152 End If LastCellAddress = Application.Caller.Address End Function
New for version 1.1 is the Toggle Button - use as a switch to turn an option or setting On/Off. I have been meaning to create some of these little beauties for a while now. I have grouped 2 Shapes, a Toggle Button and a Cylinder to make a nifty Toggle Button that can be copied into a Worksheet. After that, you can then add/link the small VBA Code Subroutine to do the toggling work and to store or update the On/Off values. These Buttons are my own design and Code. I took the Colours for the Demo from an image I found by searching on Google. Then I added a nice little easing function for the smooth animation. The easing function is a Robert Penner easing function called 'easeInQuad'...
Using the Toggle Buttons in your own Project
To use the Toggle Buttons in your own Project, first Unprotect the Interface by just doing Unprotect Sheet on the Protect Group of the Review Tab on the Ribbon. Right-click a Shape and use the Selection Pane on the Editing Group of the Home Tab of the Ribbon to make Selections easier. Now, Copy the Shape (it's Grouped) and Paste onto any Worksheet. If you need more than one set of Toggle Buttons then repeatedly Paste more until you have the amount that you need. Now rename the individual additional Shapes using the suffix 2, 3, 4 etc. Remember to use the Selection Pane to do this as it's easier and you can see what you are changing. Now Copy the Code in the Sheet1 Code Module into your own Project Worksheet Code Module. That's it, click one of the Toggle Buttons to protect the Interface
The Toggle or Click Macro
You need to add the Sheet1.Toggle Macro to the ToggleButton Shape only, not the Cylinder or the Group, Toggle Shape. You should use the Find & Select, Selection Pane to highlight the ToggleButton and then right-click on it and assign the Macro. Always use the same Macro for different Toggle Buttons. Please Note: when starting a new Project and Copying over the Buttons, Save the Project when you have finished and added the Macros to the Toggle Buttons and then Close the File! Reopen it and test the Toggle Buttons
Changing the Colours of the Toggle Button and the Cylinder Shapes
Changing the Colour of the Shapes is easy. Go into the Code and in the Toggle Subroutine, scroll down to the bottom of the Code. To change the Colour of the Cylinder, change the RGB values of the Cylinder and to change the Colours of the Toggle Button, change the Fill and Line (Border) RGB values of the Toggle Button. Do this for both RGB values for each Shape Object as per below (you can also use long Colours):
' style according to the current direction applied to the Toggle (this is the switched On direction) If Direction Then Cylinder.Fill.ForeColor.RGB = RGB(77, 182, 172) ' the Cylinder ToggleButton.Fill.ForeColor.RGB = RGB(128, 203, 196) ' the circle of the Toggle Button ToggleButton.Line.ForeColor.RGB = RGB(224, 242, 241) ' the outer circle or border of the Toggle Button
There are currently no frequently asked questions about this Software that cannot be answered via this documentation
Please remember you have purchased very affordable Software and you have not paid for a full-time Software design agency - I am but one man. Occasionally I may help with small tweaks, but these requests will be put on a much lower priority due to their nature. You have not PAID for Support, Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful
You can find the version history in the Code Module for any Macro-enabled Software or read more information below. The latest Version is always shown first
16.12.2020 (Version 1.2) bug fix for the Cell Rollover where an '@' character is added to the Formula by Excel. If this happens then the Code will moan that it cannot open the Cell - removing the @ symbol fixes the issue. Added 2 more Files to the Zip Archive. One File, Cell Rollovers.xlsm, which details how to do 'almost anything' after capturing a click on a Rollover and a modified version of Code Cell Rollovers.xlsm entitled Code Cell Rollovers (Cond. Format).xlsm which details how to use a grid technique of zeros and 1's to enable Conditional Formatting to extend the functionality of the Rollover Function - you can adjust Cell Shading, Font Colour, Underline, Cell Borders etc. within the Conditional Formatting limitations
04.12.2020 (Version 1.1) added in Toggle Buttons to the xlui Pack
14.03.2019 (Version 1) xlui pack released