Up next in 10
Create a Distributable Excel Add-In Using VBA and XML
9K views
Jul 6, 2024
Excel Add-ins for Beginners: https://www.youtube.com/watch?v=QLFtf5yy8Gg Welcome to our comprehensive tutorial on creating an Excel add-in and customizing the Excel Ribbon using macros and XML! In this step-by-step guide, we'll walk you through everything you need to know to create and manage your own custom tabs and buttons in Excel. Whether you're a beginner or an experienced user, this video has something for everyone. Check out the sections below to jump to specific parts of the video: 00:00 Introduction 00:31 Enable Developer Tab 00:37 Write a Macro with IRibbonControl 01:42 Create a Group and Button on Home tab Using Custom UI 02:52 Configure Windows to Show File Extensions 03:00 Use Excel Add-In .xlam File as an Archive File 04:08 Activate and Deactivate the Excel Add-in (.xlam file) 04:48 Set Large Icon for the Add-in in XML File 05:15 Download Icon Gallery For the Microsoft Office 05:50 Create a Custom Tab 07:00 Reposition the Custom Tab on the Ribbon
View Video Transcript
0:00
Welcome to this video. Today, I'll demonstrate an advanced method for creating an Excel
0:04
add-in by designing a custom user interface for the ribbon using XML files. Previously
0:09
the add-in required manually adding buttons and tabs through Excel options on each computer
0:14
This advanced method eliminates that hassle, performing all tasks with a single click and making
0:19
the add-in easily distributable. The add-in will display the customized ribbon when activated
0:24
and restore the original ribbon when deactivated. By the end of this video, you'll be equipped to create
0:29
custom buttons and tabs using XML. First, ensure the developer tab is enabled in Excel
0:35
options if it is not already visible on the ribbon. Next, press Altf11 to open the VBA editor
0:41
Insert a new module and write the code as shown on the screen. Define a subroutine named toggle
0:46
gridlines, which takes one parameter, control of type iRibbon Control. I Ribbon Control is an interface
0:52
used to interact with the control, such as a button, that triggered the subroutine. Since an XML file will be
0:58
used to customize the ribbon, it is essential to include the iRibbon control parameter in the subroutine
1:04
Otherwise, the subroutine will not be called. The context property refers to the active window where the button exists
1:10
toggling the visibility of gridlines in the active worksheet each time the subroutine is called
1:15
Save the workbook as an Excel add-in. Dot Slam file in a suitable location, such as the desktop
1:21
Finally, close the Excel application. Next, open a text editor such as Notepad
1:43
I'm using Notepad++, but you can use any text editor. Start writing the XML code to customize the Excel ribbon
1:50
First write the custom UI opening and closing tags Inside the custom UI tag write the ribbon tag Inside the ribbon opening and closing tags write the tabs tag and then write the tab tag
2:02
Inside the tab tag, write the group tag, and then write the button tag within it
2:06
Use the label attribute to assign a name to the button. The label is what will be displayed on the ribbon when Excel shows the button
2:13
Set the XML&S attribute of the custom UI tag as shown on the screen
2:17
Use the ID attribute to assign a unique ID to each XML tag
2:22
Do not assign an ID to the tab tag as it points to an existing built-in tab
2:27
Instead, use the IDMSO attribute to reference a built-in tab, such as Tab Home or TabData
2:32
by prefixing the tab name with Tab. For example, to display this button on the Home tab tab home tab
2:39
Insert a new attribute on action, inside the button tag, and assign at the subroutine toggle grid lines that we created in the VBA editor
2:46
Save the XML file with a name, such as BrainbellU.I.XML. Next, navigate to the Excel add-in file and change its extension to Zip
2:55
To do this, ensure file extension is visible. If it's not, open any folder
3:01
Click View and choose options. In the folder options dialog box, go to the View tab and uncheck hide extensions for known file types
3:09
Click OK. Now, rename the Zlam file by adding dot zip at the end of its file name
3:14
This will turn the SLAM file into an archive file. Double click it to open the archive and drag the BrainbellUI.XML file into it
3:22
This will insert a copy of our custom UI file into the SLAM archive. Next, open the underscore RELS folder inside the SLAM archive and drag
3:30
.RELS. File out to your desktop to make a copy. Open the RELS file in a text editor and insert a new relationship tag
3:37
Give this tag a unique ID. In the target attribute, write the name of the XML file we just created
3:42
BrainbellU In the type attribute write the URL as shown in the video Save the file and close the editor Now drag the updated Rails file back into the underscore Reels folder inside the archive choosing copy and replace when Windows warns you that the
3:58
file already exists. Finally, close the archive file and rename it by deleting the dot zip extension
4:03
Make sure the dot xLAM file extension is intact and not deleted. Open the Excel application
4:09
and navigate to the Developer tab to see how the changes take effect. Go to the Developer tab
4:15
and click Excel Addins. Click Browse and select the XLAM file. Once chosen, click Open
4:21
Ensure the add-in is enabled by checking the box, and then click OK
4:25
Now, the add-in is ready for use. Navigate to the Home tab
4:29
where you'll find a newly added Brainbell group containing a button-label toggle gridlines
4:33
Clicking this button triggers the toggle gridline subroutine, which toggles the visibility of gridlines on the current sheet
4:39
To deactivate the add-in, uncheck the add-in in Add-in's dialog box, and click OK
4:44
Now all customizations made by the add-in have been removed. If you've noticed that the toggle gridlines button does not have an icon
4:52
here's how to add one. First, close the Excel application. Rename the add-in file extension from .xlaim to dot zip
4:59
Open the BrainbellUI.comfile file in a text editor. Add the ImageMSO attribute to the button tag
5:05
and assign it a suitable icon name. For example, group table select
5:10
Specify the size of the icon by adding the size attribute to the button tag
5:14
and assign it a large value. You can find suitable icon in the Office Adin icons gallery
5:19
which you can download in Word format from the Microsoft website at the address shown on the screen
5:23
After downloading the gallery document, open it, and click file tab, and select ImageMSO from the backstage view
5:29
browse the gallery, and note down the icon name that you want to use in the add-in. Save the XML file and drag it back into the add-in archive
5:37
overriding it when prompted. rename the file back to its original.xLAM extension
5:42
Reopen the Excel application activate the add if needed and navigate to the Home tab Now the icon should be visible alongside the Toggle Gridlines button Next let create a new custom tab
5:53
Close the Excel application. Navigate to the Add-in file and make it an archive file by renaming it to .Zip
5:59
Open the BrainbellU.I.xml file and insert a new tab tag within the tab section
6:04
either before or after the existing tab Home tag. To insert a custom tab in Excel, add an ID attribute and assign it a unique name
6:12
unique name. Add a label attribute with the Value Brainbell tab, which will appear as the name of the
6:17
tab on the ribbon. Within the tab tag, add a group tag, and inside the group tag, insert a button
6:22
as demonstrated earlier for the tab Home tab. When creating a group and button inside an existing
6:27
Excel tab, IDMSO attribute is used and you don't need to add ID and label attributes. Similarly, when
6:34
creating a custom tab, ID and label attributes are used, you don't need to add the IDMSO attribute
6:39
Save the XML file and drag it back into the Add-In archive, overriding it when prompted
6:45
Rename the file back to its original.xLAM extension. Reopen the Excel application
6:51
You'll now see a new tab named Brainbell tab displayed on the ribbon. Deactivating the add-in will remove this custom tab from the ribbon, along with the toggle gridlines
6:59
button from the Home tab. If you want to reposition the custom tab on the ribbon, open the BrainbellUI-XML file in a text editor
7:06
Add the insert after MSO, attribute to the tab tag, and specify the name of the built-in tab
7:12
tab where you want the custom tab positioned, such as tab Home. Save the XML file and drag it back into the add-in archive, overriding it when prompted
7:20
Rename the file back to its original.xLAM extension. Reopen the Excel application
7:26
You'll now see the Brainbell tab positioned after the Home tab on the ribbon. Thanks for watching
7:31
I hope you found this video helpful. Be sure to check out my previous video on creating an Excel ad-in
7:36
which doesn't involve using XML and is a simpler version of this method
7:40
Please like, share, and subscribe to my channel. Thank you
#Business & Productivity Software
#Computer Education
#Educational Software