插入选项卡的控制名称 [英] Control Name for Insert tab

问题描述

我试图设计一个有限制的工作簿，而不使用Excel中的VBA，这在2007年和2010年是兼容的。我已经选择了用于Microsoft Office的自定义UI编辑器与XML代码来限制几个选项：与信息选项卡，插入，删除，移动/复制工作表，隐藏工作表，取消隐藏工作表。我成功地这样做了，但我注意到插入表单选项卡ICON

仍在工作，可以访问。任何人都可以指向控制名称以通过XML格式禁用它？

我的代码是：

 <？xml version =1.0encoding =UTF-8standalone =yes？> 
< customUI xmlns =http://schemas.microsoft.com/office/2009/07/customui> 
< commands> 
< command idMso =FileSaveAsWebPageenabled =false/> 
< command idMso =FileSaveAsenabled =false/> 
< command idMso =FileSaveAsMenuenabled =false/> 
< command idMso =FileSaveAsExcelXlsxenabled =false/> 
< command idMso =FileSaveAsExcelXlsxMacroenabled =false/> 
< command idMso =FileSaveAsExcel97_2003enabled =false/> 
< command idMso =FileSaveAsExcelOpenDocumentSpreadsheetenabled =false/> 
< command idMso =FileSaveAsPdfOrXpsenabled =false/> 
< command idMso =FileSaveAsOtherFormatsenabled =false/> 
 
< command idMso =SheetInsertenabled =false/> 
< command idMso =SheetInsertPageenabled =false/> 
< command idMso =SheetDeleteenabled =false/> 
< command idMso =SheetRenameenabled =false/> 
< command idMso =SheetMoveOrCopyenabled =false/> 
< command idMso =SheetUnhideenabled =false/> 
< command idMso =SheetProtectenabled =false/> 
< command idMso =SheetTabColorGalleryenabled =false/> 
< command idMso =SheetTabColorMoreColorsDialogenabled =false/> 
< command idMso =SelectAllSheetsenabled =false/> 
< / commands> 
< backstage> 
< tab idMso =TabInfovisible =false/> 
< / backstage> 
< / customUI> 
  

我已经尝试搜索Microsoft和 rondebruin 也适用于Office Fluent用户界面控制标识符。

解决方案

为了通过XML完成，您需要能够访问该元素 - 它需要一个ID。手动扫描Microsoft发布的各种列表没有任何帮助，但由于他们的文档非常恶劣，我决定写一个非常小的代码，在Excel应用程序中找到每个控件与ID的ID，并列出它：

  Sub listID（）
 Dim r As Range 
 Dim ctls 
 Dim ii As Long 
 
细胞（1，1）.Value =ID
细胞（1，2）.Value =caption
细胞（1，3）=键入
设置r =范围（a1）
 
对于ii = 1至100000 
设置ctls = CommandBars.FindControl（Id：= ii）
如果不是（ctls是没有）然后
'Debug.Print控件ID& ii& 存在;标题是& ctls.Caption& ;类型是& ctls.Type 
设置r = r.Offset（1，0）
 r.Value = ii 
 r.Offset（0，1）= ctls.Caption 
 r.Offset （0，2）= ctls.Type 
 r.Offset（0，3）= ctls.TooltipText 
结束如果
下一步ii 
 
 End Sub 
  

我运行这个后，过滤任何与 eet 在名称中，我希望看到所有的控件可以被控制（因为它们有一个 msoID ），并且与Sheets有关。以下是这个产生的快照：

当我将鼠标悬停在要隐藏的按钮上时，我得到工具提示Insert Sheet - 这不是任何我在列表中可以看到的。我从此得出结论，确实不可能做你所要求的 - 你不能用XML禁用该按钮。

这并不意味着你无法实现什么你想要。我会建议以下方法。

1. 捕获创建新工作表时触发的工作簿事件，并将其现场删除。当按钮停止工作时，人们很快就会放弃。下面的示例代码。


2. 完全隐藏工作表选项卡，并提供在工作表之间导航的替代方法。由于这显然是一个受控电子表格，无论如何，这可能是一个好主意。您可以在功能区上创建一个自定义选项卡（使用XML，您似乎很熟悉），或者创建一个位于工作表底部的浮动工具栏 - 靠近旧选项卡的位置。以这种方式你可以模拟行为 - 但是这是一个很大的工作，有点黑客


3. 为工作簿添加保护。使用保护 - >保护工作簿 - >保护结构：工作表无法移动，删除，隐藏，取消隐藏或重命名。新的工作表无法插入。

您必须添加到 ThisWorkbook 是：

  Private Sub Workbook_NewSheet（ByVal Sh As Object）
 Dim temp As Boolean 
 temp = Application.DisplayAlerts 
 Application.DisplayAlerts = False 
 Sh.Delete 
 Application.DisplayAlerts = temp 
 End Sub 
  

一旦这是您的工作簿，用户点击新工作表按钮的任何时间将会有一个非常简短的闪光灯，但没有新的工作表被创建。您可以添加一个 Application.ScreenUpdating = False ，但简短的Flash仍然保留...

对不起我不对你有更好的消息。

I am trying to design a workbook with some restrictions without using VBA in Excel, which is compatible in 2007 and 2010. I have chosen "Custom UI Editor For Microsoft Office" with XML code to restrict a few options:- Save-as with info tab, Insert, Delete, Move/Copy Sheet, Hide sheet, Unhide sheets. I was successful in doing so but I have noticed that insert sheet tab "ICON"

is still working and is accessible. Can anyone point me to the Control Name to disable it through XML in file please?

My code is:

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <commands>
               <command idMso="FileSaveAsWebPage" enabled="false" />
               <command idMso="FileSaveAs" enabled="false" />
               <command idMso="FileSaveAsMenu" enabled="false" />
               <command idMso="FileSaveAsExcelXlsx" enabled="false" />
               <command idMso="FileSaveAsExcelXlsxMacro" enabled="false" />
               <command idMso="FileSaveAsExcel97_2003" enabled="false" />
               <command idMso="FileSaveAsExcelOpenDocumentSpreadsheet" enabled="false" />
               <command idMso="FileSaveAsPdfOrXps" enabled="false" />
               <command idMso="FileSaveAsOtherFormats" enabled="false" />

               <command idMso="SheetInsert" enabled="false" />
               <command idMso="SheetInsertPage" enabled="false" />
               <command idMso="SheetDelete" enabled="false" />
               <command idMso="SheetRename" enabled="false" />
               <command idMso="SheetMoveOrCopy" enabled="false" />
               <command idMso="SheetUnhide" enabled="false" />
               <command idMso="SheetProtect" enabled="false" />
               <command idMso="SheetTabColorGallery" enabled="false" /> 
               <command idMso="SheetTabColorMoreColorsDialog" enabled="false" />
             <command idMso="SelectAllSheets" enabled="false" />
  </commands>
  <backstage>
           <tab idMso="TabInfo" visible="false"/>
  </backstage>
</customUI>

I have tried searching Microsoft and rondebruin for Office Fluent User Interface Control Identifiers also.

解决方案

For this to be done through XML you would need to be able to access the element - it needs to have an ID. Manually scanning through the various lists that Microsoft published turned up nothing helpful, but since their documentation is notoriously sloppy I decided to write a very small piece of code that finds the ID of "every control with an ID" in the Excel application, and lists it:

Sub listID()
Dim r As Range
Dim ctls
Dim ii As Long

Cells(1, 1).Value = "ID"
Cells(1, 2).Value = "caption"
Cells(1, 3) = "Type"
Set r = Range("a1")

For ii = 1 To 100000
  Set ctls = CommandBars.FindControl(Id:=ii)
  If Not (ctls Is Nothing) Then
  'Debug.Print "controls ID " & ii & " exists; the caption is " & ctls.Caption & "; the type is " & ctls.Type
  Set r = r.Offset(1, 0)
  r.Value = ii
  r.Offset(0, 1) = ctls.Caption
  r.Offset(0, 2) = ctls.Type
  r.Offset(0, 3) = ctls.TooltipText
  End If
Next ii

End Sub

After I run this, and filter on anything with eet in the name, I would expect to see all the controls "that can be controlled" (because they have an msoID) and that relate to "Sheets". The following is the snapshot of what this produces:

When I hover my mouse over the "button" you want to hide, I get the toolTip "Insert Sheet" - which isn't any of the ones I can see in my list. I conclude from this that it is indeed impossible to do what you are asking - you cannot disable that button with XML.

That doesn't mean you can't achieve what you would like. I would suggest the following approaches.

1. Trap the workbook event that is triggered when a new sheet is created, and delete it on the spot. When the button "stops working" people will soon give up. Example code below.
2. Hide the sheet tabs completely, and provide an alternative method of navigating between sheets. Since this is obviously a "controlled spreadsheet" that may be a good idea anyway. You could either create a custom tab on the ribbon (using XML, you seem to be familiar with that), or create a floating toolbar that lives at the bottom of the sheet - close to where the "old" tabs used to be. In that way you simulate the behavior - but it's a lot of work and a bit of a hack
3. Add protection to the workbook. Use Protection -> Protect Workbook -> "Protect structure": sheets can not be moved, deleted, hidden, unhidden, or renamed. New sheets cannot be inserted.

The code you would have to add to ThisWorkbook is:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
  Dim temp As Boolean
  temp = Application.DisplayAlerts
  Application.DisplayAlerts = False
  Sh.Delete
  Application.DisplayAlerts = temp
End Sub

Once this is in your workbook, any time a user clicks the "new sheet" button there will be a very brief flash, but no new sheet is created. You could add a Application.ScreenUpdating = False but the brief flash remains...

Sorry I don't have better news for you.

这篇关于插入选项卡的控制名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！