Creating the XML File That Contains the Markup to Modify the UI

1. Create a folder called customUI.

2. Open a new file in the text editor of your choice, and save it as customUI.xml in the customUI folder.

3. Add the following code to the customUI.xml file:

<customUI xmlns=""> <ribbon startFromScratch="true"> <tabs>

<tab id="DataFunctions" label="XML Data Functions'^ <group id="NewDataControls" label="New Data">

<button id="Button1" size="large" label="Get Emps Dept" ^ onAction="ThisWorkbook.GetEmpDataBtn" /> <button id="Button2" size="large" label="Get HR Info" ^ onAction="ThisWorkbook.GetHRDataBtn" />

<group id="AppendDataControls" label="Append Data">

<button id="Button3" size="large" label="Append Emps Dept" ^ onAction="ThisWorkbook.AppendEmpDataBtn" />

<group id="RefreshDataControls" label="Refresh Data">

<button id="Button4" size="large" label="Refresh Emp Dept"

onAction="ThisWorkbook.RefreshEmpDataBtn" /> <button id="Button5" size="large" label="Refresh HR Info"

onAction="ThisWorkbook.RefreshHRDataBtn" />

<group id="SaveDataControls" label="Save Data">

<button id="Button6" size="large" label="Save Emp Dept" ^ onAction="ThisWorkbook.SaveEmpBtn" /> <button id="Button7" size="large" label="Save Emp Dept As"

onAction="ThisWorkbook.SaveEmpNewFileBtn" />

</tab> </tabs> </ribbon> </customUI>

4. Save the file.

This XML defines the XML Data Functions tab and its four groups. Within each group, note the reference to each macro we just created in the ThisWorkbook module in our Excel project.

Next, we will modify some of the files contained in the macro-enabled Excel file that we just created.

1. Change the extension of XML_Class_Ribbon.xlsm to .zip, and double-click the file to open it.

2. Add the customization file to the ZIP container by dragging the customUI folder from its location to the ZIP file.

3. Extract the .rels file to a local folder. A _rels folder containing the .rels file is copied to your folder. (If only the file appears, use your ZIP tool's extract function rather than dragging the file from the ZIP window.)

4. Open the .rels file and add the following line between the last Relationship tag and the Relationships tag, as shown in Listing 3-1. This creates a relationship between the workbook file and the customization file.

Listing 3-1. Adding the CustomUI Relationship to the .rels. File Relationships xmlns=""> Relationship Id="rId3" Type=" officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml" /> Relationship Id="rId2" Type=" /package/2006/relationships/metadata/core-properties" Target="docProps/core.xml" /> Relationship Id="rId1" Type=" /officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml" /> Relationship Id="someID" Type=" 2006/relationships/ui/extensibility" Target="customUI/customUI.xml" /> </Relationships>

5. Close and save the file.

6. Add the _rels folder back to the container file by dragging it from its location, overwriting the existing file.

7. Rename the workbook file back to its original name.

8. Open the workbook and notice that the Ribbon UI now displays your XML data functions.

9. Click the buttons to check that the functionality is there and working (shown in Figure 3-32).

Figure 3-32. A custom ribbon added using an XML configuration file
+1 0

Post a comment