gallery Element

The gallery element is designed to graphically display user options. A good example is the Styles gallery, which allows users to select styles by viewing a graphical representation of the style, rather than the name.

You can use the gallery to organize photos, access styles or store color palette. A gallery is the perfect control for customization that requires and provides visual impact.

Graphical View of gallery Attributes

The following figure shows all the visible graphical attributes you can set on the gallery control.

This is specified using the following XML:

<gallery id="customGallery"
         label="My Photo Gallery"
         image="Tulips"
         columns="2"
         rows="2"
         itemWidth="100"
         itemHeight="75"
         showItemLabel="false"
         size="large">
	<item id="item0"
	      label="Chrysanthemum"
	      image="Chrysanthemum"/>
	<item id="item1"
	      label="Desert"
	      image="Desert"/>
	<item id="item2"
	      label="Hydrangeas"
	      image="Hydrangeas"/>
	<item id="item3"
	      label="Jellyfish"
	      image="Jellyfish"/>
	<item id="item4"
	      label="Koala"
	      image="Koala"/>
	<item id="item5"
	      label="Tulips"
	      image="Tulips"/>
</gallery>

Parent Elements

• box
• group

Children Elements

• button
• item

Static Attributes and Callbacks

Dynamic Attributes and Callbacks

Using Built-in gallery Controls

The following example brings together three gallery controls onto a custom tab and group in Excel:

This is specified using the following XML:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"
          onLoad="IRibbonUI_onLoad">
	<ribbon startFromScratch="false">
		<tabs>
			<tab id="customTab"
			     insertBeforeMso="TabHome"
			     label="Custom Tab">
				<group id="customGroup"
				       label="Custom Group">
					<box id="customBox1"
					     boxStyle="horizontal">
						<gallery idMso="FontColorPicker"/>
					</box>
					<box id="customBox2"
					     boxStyle="horizontal">
						<gallery idMso="CellFillColorPicker"/>
					</box>
					<box id="customBox3"
					     boxStyle="horizontal">
						<gallery idMso="ChartTypeColumnInsertGallery"
						         size="normal"/>
					</box>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Because we are only using built-in controls, this example does not require VBA.

Example of Static Attributes

Using static values for attributes means that customizations cannot be modified after the UI is loaded. Remember that the Ribbon is built at design time, not at runtime.

In order to create a gallery, follow these steps:

1. Create your macro-enabled Excel file. Save and close the file.
2. Launch the Office RibbonX Editor.
3. Open your workbook by clicking the Open button on the Office RibbonX Editor toolbar and navigating to the file.
4. From the Insert menu, choose Office 2010+ Custom UI Part. This will add a customUI14.xml entry under your workbook in the tree view on the left.
   
5. Double click customUI14.xml, paste the following XML code into it:

   <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"
             onLoad="IRibbonUI_onLoad">
   	<ribbon startFromScratch="false">
   		<tabs>
   			<tab id="customTab"
   			     insertBeforeMso="TabHome"
   			     label="Custom Tab">
   				<group id="customGroup"
   				       label="Gallery">
   					<!-- Starts the definition of our gallery-->
   					<gallery id="customGallery"
   					         label="My Photo Gallery"
   					         image="Koala"
   					         columns="2"
   					         rows="2"
   					         itemWidth="200"
   					         itemHeight="150"
   					         showItemLabel="false"
   					         size="large">
   						<!-- Insert the photo gallery-->
   						<!-- Import the photos you want to use first-->
   						<item id="item0"
   						      label="Hydrangeas"
   						      image="Hydrangeas"/>
   						<item id="item1"
   						      label="Jellyfish"
   						      image="Jellyfish"/>
   						<item id="item2"
   						      label="Koala"
   						      image="Koala"/>
   						<item id="item3"
   						      label="Lighthouse"
   						      image="Lighthouse"/>
   						<item id="item4"
   						      label="Penguins"
   						      image="Penguins"/>
   						<item id="item5"
   						      label="Tulips"
   						      image="Tulips"/>
   						<item id="item6"
   						      label="Chrysanthemum"
   						      image="Chrysanthemum"/>
   						<item id="item7"
   						      label="Desert"
   						      image="Desert"/>
   						<!-- Insert a button at the end of the gallery-->
   						<button id="custombtn"
   						        imageMso="RefreshStatus"
   						        label="Visit ExcelBaby.com online..."
   						        onAction="custombtn_Click"/>
   					</gallery>
   				</group>
   			</tab>
   		</tabs>
   	</ribbon>
   </customUI>

6. From the Insert menu, choose Icons.
   
7. Browse and open your image files. Download Demo Images.
8. Your icon file has been added under the "customUI14.xml" element.
   
9. Download Full Example: gallery Element Static Attributes Demo

Creating a Dynamic Gallery

The following example creates a dynamic gallery. This example differs from the previous example in that:

• This example loads images dynamically.
• You don't need to enter more in the XML code.
• Code maintenance is also very simple.

XML

Copy and paste the following XML code into customUI14.xml. If you don't know how to do it, read this article: Creating a Custom Ribbon.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"
          onLoad="IRibbonUI_onLoad">
	<ribbon startFromScratch="false">
		<tabs>
			<tab id="customTab"
			     insertBeforeMso="TabHome"
			     label="Custom Tab">
				<group id="customGroup"
				       label="Photo Gallery Demo">
					<!-- Starts the definition of our gallery -->
					<gallery id="customGallery"
					         label="My Photo Gallery"
					         columns="2"
					         rows="2"
					         itemWidth="100"
					         itemHeight="75"
					         getItemCount="customGallery_getItemCount"
					         getImage="customGallery_getImage"
					         getItemImage="customGallery_getItemImage"
					         onAction="customGallery_onAction"
					         showItemLabel="false"
					         size="large">
						<!-- Inserts a button at the bottom of the gallery -->
						<button id="customButton"
						        imageMso="RefreshStatus"
						        label="Visit ExcelBaby.com online..."
						        onAction="customButton_onAction"/>
					</gallery>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

You will notice three new attributes:

• getImage: You will use it to load the front picture of the gallery (not the actual gallery pictures).
• getItemCount: This attribute is used to return the total number of items in the gallery. You'll use a constant or dynamic in your VBA code.
• getItemImage: This attribute is used to load the item image — that is, each image that appears in the gallery.

VBA

Note that you don't need to loop through each item. instead, the Ribbon will call back on the getItemImage attribute until it runs through all the items determined for the getItemCount attribute. As it does so, it returns an index number for each item’s image. You then use this index to grab and load each corresponding picture.

The index parameter of getItemImage starts from 0, so the image name also starts from 0. E.g. img0.png, img1.png.

Copy and paste the following VBA code into a normal module:

'------------------ Modules ------------------
'--------------- ExcelBaby.com ---------------
Option Explicit
Public MyRibbon As IRibbonUI

'Callback for customUI.onLoad
Sub IRibbonUI_onLoad(ribbon As IRibbonUI)
    Set MyRibbon = ribbon
    MyRibbon.ActivateTab ("customTab")  'Activate customTab when workbook open
End Sub

'Callback for customGallery getItemCount
Sub customGallery_getItemCount(control As IRibbonControl, ByRef returnedVal)
    returnedVal = GetImagesCount(ThisWorkbook.Path & "\images\") - 1
End Sub

'Callback for customGallery getImage
Sub customGallery_getImage(control As IRibbonControl, ByRef returnedVal)
    Set returnedVal = LoadPicture(ThisWorkbook.Path & "\images\GalleryCover.png")
End Sub

'Callback for customGallery getItemImage
Sub customGallery_getItemImage(control As IRibbonControl, index As Integer, ByRef returnedVal)
    Set returnedVal = LoadPicture(ThisWorkbook.Path & "\images\img" & index & ".png")
End Sub

'Callback for customGallery onAction
Sub customGallery_onAction(control As IRibbonControl, id As String, index As Integer)
    MsgBox "You have click on image index number " & index
End Sub

'Callback for customButton onAction
Sub customButton_onAction(control As IRibbonControl)
    ActiveWorkbook.FollowHyperlink Address:="https://excelbaby.com/", NewWindow:=True
End Sub

'Return the files count
Private Function GetImagesCount(folderspec)
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    GetImagesCount = fso.GetFolder(folderspec).Files.Count
End Function

In this example, all images are stored under the images folder. Since getImage occupies one image, the number of getItemCount must be reduced by 1.

If you don't have many images, you can also set a constant for the getItemCount attribute, so you can easily change the item count if needed. For example:

Public Const GalleryItemCount = 6
'Callback for customGallery getItemCount
Sub customGallery_getItemCount(control As IRibbonControl, ByRef returnedVal)
    returnedVal = GalleryItemCount
End Sub

Download This Example

Download Creating a Dynamic Gallery Demo