Excel自定义功能区入门：如何在同一Tab中新增Group？

嘿，作为刚接触Custom UI Editor的新手，你已经做得很棒了！要在同一个Tab1里添加更多group其实非常直观——只需要在现有</group>标签之后、</tab>标签结束之前，插入新的<group>节点就行。每个group需要有唯一的id和自定义的label，里面可以像第一个group那样放置菜单、按钮等控件。

修改后的完整代码示例 #

我在你的现有代码基础上，添加了两个新group：Data Tools和Report Tools，你可以参考这个结构扩展更多功能分组：

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab id="Tab1" label="QUICK FIX">
        <!-- 原有的第一个Group -->
        <group id="Group1" label="Time Saving Tools">
          <menu id="Menu1" image="Home" label="Menu" size="large">
            <menu id="Menu2" label="Add New" imageMso="TablePropertiesDialog">
              <button id="Button01" label="1. Add a new sheet with Current Date" imageMso="ExportExcel" onAction="AddSheetCurrentDate" />
              <button id="Button02" label="2. Add a Calendar Sheet with user given period" imageMso="CreateTable" onAction="CalendarMake" />
              <button id="Button03" label="3. Add a sheet with This Year Calendar" imageMso="ViewAllProposals" onAction="ThisYearCalendar" />
              <button id="Button04" label="4. Create Table of Contents" imageMso="FunctionsLogicalInsertGallery" onAction="TableOfContent" />
            </menu>
            <menu id="Menu3" label="Visibility and Protection" imageMso="DatabaseSetLogonSecurity">
              <button id="Button05" label="1. Make this sheet very hidden" imageMso="RelationshipsHideTable" onAction="ActiveSheetVeryHidden" />
              <button id="Button06" label="2. Show all very hidden sheets" imageMso="SubformMenu" onAction="ShowVeryHiddenShts" />
              <button id="Button07" label="3. Unhide All Sheets" imageMso="PersonaStatusOnline" onAction="UnhideAllHiddenShts" />
              <button id="Button08" label="4. Protect All Sheets" imageMso="Lock" onAction="Protect_All_Shts" />
              <button id="Button09" label="5. Un-Protect All Sheets" imageMso="FileCompatibilityChecker" onAction="Unprotect_All_Shts" />
              <button id="Button10" label="6. List All Un-Protected Sheets" imageMso="Numbering" onAction="ListAllUnprotectedShts" />
              <button id="Button11" label="7. List All Visible and Hidden Sheets" imageMso="DatasheetColumnLookup" onAction="List_all_visible_and_hidden_shts" />
              <button id="Button12" label="8. Lock Cells Containing Formula" imageMso="FileLinksToFiles" onAction="LockCellsWithFormula" />
              <button id="Button13" label="9. Set Custom Zoom % For All Sheets" imageMso="ZoomPrintPreviewExcel" onAction="SetCustomZoomForAllShts" />
            </menu>
            <menu id="Menu4" label="Columns and Rows" imageMso="TablePropertiesDialog">
              <button id="Button14" label="1. Auto Fit All Rows in this Worksheet" imageMso="GridlinesGallery" onAction="AutoFitAllRows1" />
              <button id="Button15" label="2. Auto Fit All Columns in this Worksheet" imageMso="RelationshipsHideTable" onAction="AutoFitColumnsInActiveSheet" />
              <button id="Button16" label="3. Delete All Blank Columns" imageMso="OmsDelete" onAction="DeleteBlankColums" />
              <button id="Button17" label="4. Delete All Blank Rows" imageMso="OmsDelete" onAction="DeleteBlankRows" />
            </menu>
            <menu id="Menu5" label="Apply Formula On Cells and Comments" imageMso="ActionInsert">
              <button id="Button18" label="1. Insert Formula On Selected Cells PS:(No Undo)" imageMso="AutoSum" onAction="InsertFormulaOnSelectedCells" />
              <button id="Button19" label="2. Insert Comments (Multiple Cells) PS:(No Undo)" imageMso="WebServerDiscussions" onAction="InsertCommentsOnSelection" />
              <button id="Button20" label="3. Highlight Cells with Comments PS:(No Undo) " imageMso="ObjectEffectGlowGallery" onAction="HighlightCommentOnCells" />
              <button id="Button21" label="4. Highlight Duplicated Values on the Selected Range PS:(No Undo)" imageMso="ConditionalFormattingHighlightCompareColumns" onAction="HighlightDuplicatedValuesOnRange" />
              <button id="Button22" label="5. Change Comment Box Appearance in this Sheet PS:(No Undo)" imageMso="AppointmentColorDialog" onAction="ChangeCommentBoxColorOnThisSheet" />
              <button id="Button23" label="6. Replace Blank Cells With Zero on the Selected Range PS:(No Undo)" imageMso="O" onAction="ReplaceBlankCellsWithZeroOnSelection" />
            </menu>
            <menu id="Menu6" label="Text Utilities" imageMso="FontSchemes">
              <button id="Button24" label="1. Convert Text To Lower Case PS:(No Undo)" imageMso="ReplaceDialog" onAction="ConvertTextToLowerCase" />
              <button id="Button25" label="2. Convert Text To Upper Case PS:(No Undo)" imageMso="SlideThemesGallery" onAction="ConvertTextToUpperCase" />
              <button id="Button26" label="3. Convert Text To Proper Case PS:(No Undo)" imageMso="FontSchemes" onAction="ConvertTextToProperCase" />
              <button id="Button27" label="4. Remove all Numbers from the Selected Cells PS:(No Undo)" imageMso="InterconnectDeleteCard" onAction="RemoveAllNumbersFromSelection" />
              <button id="Button28" label="5. Remove all Text from the Selected Cells PS:(No Undo)" imageMso="WatermarkRemove" onAction="RemoveAllTextFromSelectedRange" />
              <button id="Button29" label="6. Convert Text Number to Number Format PS:(No Undo)" imageMso="_1" onAction="ConvertTextNumbersToNumberFormt" />
            </menu>
            <menu id="Menu8" label="Prefix and Suffix" imageMso="ReviewCompareTwoVersions">
              <button id="Button30" label="1. Prefix to Existing Data PS:(No Undo)" imageMso="MailMergeGoToNextRecord" onAction="PrefixToExistingLeft" />
              <button id="Button31" label="2. Suffix to Existing Data PS:(No Undo)" imageMso="MailMergeGoToPreviousRecord" onAction="SuffixToExistingRight" />
            </menu>
            <menu id="Menu9" label="Clean Up!" imageMso="ControlToolboxOutlook">
              <button id="Button32" label="1. Remove Leading and Trailing Spaces in this Sheet PS:(No Undo)" imageMso="AsianLayoutMenu" onAction="RemoveLeadingTrailingSpacesInActiveSht" />
              <button id="Button33" label="2. Delete Unused Formats in this Sheet PS:(No Undo)" imageMso="ControlActiveX" onAction="DeleteUnusedFormatsActiveSheet" />
              <button id="Button34" label="3. Delete Unused Formats in this Workbook PS:(No Undo)" imageMso="CoverPageRemove" onAction="DeleteUnusedFormatsAllWorkSheets" />
              <button id="Button35" label="4. Clear Print Area in this Workbook PS:(No Undo)" imageMso="MasterDocumentUnlinkSubdocument" onAction="ClearPrintAreaInAllShts" />
              <button id="Button36" label="5. Clear All Hyperlinks in this Worksheet PS:(No Undo)" imageMso="HyperlinkRemove" onAction="RemoveAllHyperlinksThisSheet" />
              <button id="Button37" label="6. Remove all Styles not in use. PS:(No Undo)" imageMso="HyperlinkRemove" onAction="RemoveStylesNotInUse" />
              <button id="Button38" label="7. Reduce Size of This Workbook. PS:(No Undo)" imageMso="UpgradeWorkbook" onAction="ReduceSizeOfWorkbook" />
              <button id="Button39" label="8. Delete all Blank Sheets. PS:(No Undo)" imageMso="CoverPageRemove" onAction="DeleteAllBlankWorkShts" />
            </menu>
            <menu id="Menu10" label="Sort" imageMso="SortDialog">
              <button id="Button40" label="1. Sort all Worksheets Alphabetically. PS:(No Undo)" imageMso="SortUp" onAction="SortAllWorkSheetsAlphabetically" />
              <button id="Button41" label="2. Sort all Worksheets by Colour. PS:(No Undo)" imageMso="ViewBackToColorView" onAction="SortWorkSheetsByColor" />
            </menu>
          </menu>
        </group>

        <!-- 新增的第二个Group：Data Tools -->
        <group id="Group2" label="Data Tools">
          <button id="Button42" label="Remove Duplicates" imageMso="RemoveDuplicates" onAction="RemoveDuplicatesFromRange" />
          <button id="Button43" label="Text to Columns" imageMso="TextToColumns" onAction="RunTextToColumns" />
          <menu id="Menu11" label="Data Validation" imageMso="DataValidation">
            <button id="Button44" label="Add Dropdown List" imageMso="DataValidationList" onAction="AddDropdownValidation" />
            <button id="Button45" label="Clear Validation from Range" imageMso="DataValidationClear" onAction="ClearDataValidation" />
          </menu>
        </group>

        <!-- 新增的第三个Group：Report Tools -->
        <group id="Group3" label="Report Tools">
          <button id="Button46" label="Generate Summary Report" imageMso="ReportGallery" onAction="GenerateSummaryReport" />
          <button id="Button47" label="Export to PDF" imageMso="ExportPdf" onAction="ExportWorkbookToPDF" />
        </group>

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

关键注意事项 #

• 唯一ID：所有控件（group、menu、button）的id必须唯一，不能和现有控件重复，否则功能区会加载失败。
• 结构层级：确保新的group标签嵌套在<tab id="Tab1">和</tab>之间，不要放在现有group内部。
• 图标设置：可以使用imageMso引用Office内置图标（比如示例中的RemoveDuplicates、TextToColumns），或者用image属性引用你添加到Custom UI Editor中的自定义图片。
• OnAction绑定：每个button的onAction对应的宏需要在Excel VBA编辑器中编写实现代码，确保宏名称和这里的一致。

这样修改后，你的"QUICK FIX" tab里就会有多个并列的group了，每个group可以分类存放不同功能的控件，让你的自定义功能区更有条理！

内容的提问来源于stack exchange，提问作者Soji Naveen