Programming with Excel VBA
Home » learn » Programming with Excel VBA » gallery Element

gallery Element

13 minutes to read
13 0

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

Children Elements

Static Attributes and Callbacks

STATIC ATTRIBUTE ALLOWED VALUES VBA CALLBACK SIGNATURE FOR DYNAMIC ATTRIBUTE
columns 1 to 1024 N/A
itemHeight 1 to 4096 Sub getItemHeight (control As IRibbonControl, ByRef height)
itemWidth 1 to 4096 Sub getItemWidth (control As IRibbonControl, ByRef width)
rows 1 to 1024 N/A
sizeString 1 to 1024 characters N/A
showItemImage true, false, 1, 0 N/A
showItemLabel true, false, 1, 0 N/A

Dynamic Attributes and Callbacks

DYNAMIC ATTRIBUTE ALLOWED VALUES VBA CALLBACK SIGNATURE FOR DYNAMIC ATTRIBUTE
getItemCount 0 to 1000 Sub GetItemCount (control As IRibbonControl, ByRef count)
getItemID 1 to 1024 characters Sub GetItemID (control As IRibbonControl, index As Integer, ByRef id)
getItemImage Unique text string Sub GetItemImage (control As IRibbonControl, index As Integer, ByRef image)
getItemLabel 1 to 1024 characters Sub GetItemLabel (control As IRibbonControl, index As Integer, ByRef label)
getItemScreentip 1 to 1024 characters Sub GetItemScreenTip (control As IRibbonControl, index As Integer, ByRef screentip)
getItemSupertip 1 to 1024 characters Sub GetItemSuperTip (control As IRibbonControl, index As Integer, ByRef supertip)
getSelectedItemID 1 to 1024 characters Sub GetSelectedItemID (control As IRibbonControl, ByRef id)
getSelectedItemIndex 1 to 1024 Sub GetSelectedItemIndex (control As IRibbonControl, ByRef index)
onAction Sub OnAction (control As IRibbonControl, selectedId As String, selectedIndex As Integer)

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

❮ PreviousNext ❯

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>