Geek Documentation Logo

XLUI Material UI Pack

Version 1


Introduction


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

Requirements

You will need the following Excel Version & Prerequisite to use this Software

  1. Excel 2010, 2013 & 2016 (32bit or 64bit)
  2. Basic Excel and some VBA skills
  3. Windows PC · NOT a Mac!

No support is provided for customization or development of this Software






About xlui #back to top

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

Features include:

  • * NEW for version 1.1 - Toggle Buttons
  • * Custom SVG & EMF Icons for Material UI
  • * Material UI Cheat Sheet
  • * Beautiful Button Click Effects
  • * Minified, powerful VBA Code
  • * Toggle Buttons, Lists, Tables & Treelists





The Download File (the xlui pack)#back to top

The download File

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):

xlui pack contents

Icons (SVG & EMF)#back to top

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





Icon Burger at 400% Zoom

Icon Close Cross at 400% Zoom

Burger Menu Icons

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:

Icon Burger resized to 1 cm

Icon Burger at 100%

Icon Close Cross resized to 1 cm

Icon Closs Cross at 100%






Toggle Button Minus at 400% Zoom

Toggle Button Plus at 400% Zoom

Toggle Button Icons

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:

Toggle Button Minus resized to 0.8 cm

Toggle Button Minus at 100%

Toggle Button Plus resized to 0.8 cm

Toggle Button Plus at 100%

EMF Files

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

EMF Toggle Button Minus at 400% Zoom

EMF Toggle Button Minus at 100% with Shadow





Icon Table Arrow Down at 400% Zoom

Icon Table Arrow Up at 400% Zoom

Table Toggle Arrows

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:

Table Down Arrow resized to 1 cm

Table Down Arrow resized to 1 cm

Table Up Arrow resized to 1 cm

Table Up Arrow resized to 1 cm





Stop, Pause, Play, Next & Previous Icons with Fill Colour at 400% Zoom

Stop, Pause, Play, Next & Previous Icons with Outline Colour at 400% Zoom

Stop, Pause, Play, Next & Previous Icons

These 5 SVG Icons are used to demonstrate my Click Effect. Both Fill and Outline Colours can be adjusted in Excel

Toggle Rows & Columns with a Click Effect #back to top

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




Toggle Effect (Flat)

Toggle Effect (Shadow)

Toggle Button UI

Watch a Video of my Toggle button & click effect in action:

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.

Try the Toggle Yourself...

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

Rollover Toggle Button with a Click Effect #back to top




Rollover Toggle Effect (Flat)

Rollover Toggle Effect (Flat)

Rollover Toggle Button UI

Watch a Video of my Rollover Toggle Button & click effect in action:

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




Rollover Treelist Toggle Effect #back to top

Rollover Treelist Toggle Effect (Flat)

Rollover Treelist Toggle Effect (Flat)

Rollover Treelist Toggle Effect

Watch a Video of my Rollover Treelist Toggle Button & click effect in action:

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'




Table Toggle Effect #back to top

Rollover Toggle Effect (Flat)

Table Toggle Effect (Flat)

Rollover Toggle Effect (Flat)

Table Toggle Effect (Flat)

Table Toggle Effect

Watch a Video of my Table Button & click effect in action:

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

Try the Table Toggle Yourself...

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 #back to top




Click Effect (Flat)

Click Effect

Watch a Video of my Click effect in action:

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

Code Cell Rollover Effect #back to top




Click Effect (Flat)

Code Cell Rollover Effect

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

Try the Code Cell Rollover Yourself...

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

Toggle Buttons #back to top




Toggle Buttons for xlui - use as a switch to turn an option or setting On/Off

Toggle Buttons

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






Support #back to top

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

Support (limited) for my Software includes:

* Responding to questions or problems regarding the Software and its features
* Fixing valid (replicated) bugs and reported issues for the VERSION I HAVE WRITTEN

Software support does not include:

* Customization and installation services
* Support for third party software or ANY kind of development whatsoever

Before seeking support, please...

* Make sure your question is a valid Software Issue and not a customization request
* Make sure you have read through the documentation and any related video guides before asking support on how to accomplish a task
* Make sure to double check the Software FAQs or online documentation
* Ensure that you access to the VBOM is allowed and that Macros can run in Excel
* Make sure to provide 'proof of purchase' and state the name / version of the Software that you are having issues with when requesting support by Email or via Facebook

How to get Support

Contact Mark Kubiszyn on the Email address provided when you purchased the Software, including the Order Number
Contact Kubiszyn.co.uk via our Facebook Page - remember to be patient, if there has been an issue with your download, I will always respond within 48 hours and will Email you the File directly if neccessary or via Messanger. For other issues the response time may be considerably longer and I may choose to respond to specific questions only (as is my right), depending on what has been asked

Version History (Changelog) #back to top

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


Changelog

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