excel 2007 vba 将项目表名称添加到列表框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3319754/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
excel 2007 vba add item sheet names to listbox
提问by Kirk Hings
Using Excel 2007 VBA
使用 Excel 2007 VBA
Have a sheet "Dashboard", have other sheets but some special task sheets that can be anywhere in the book, but will always be found between Sheets "TaskNew" and "TaskEnd". These special task sheets will vary in count.
有一个工作表“仪表板”，有其他工作表，但有一些特殊的任务工作表，它们可以在书中的任何地方，但总是在工作表“TaskNew”和“TaskEnd”之间找到。这些特殊任务表的数量会有所不同。
Upon dashboard activation, want to populate the listbox with the names of all special task sheets, so that user can select a sheet form drop down and navigate to that sheet.
在仪表板激活后，想要使用所有特殊任务表的名称填充列表框，以便用户可以选择一个表表单下拉列表并导航到该表。
Following results in runtime error '438' Object doesn't support this property or method:
以下导致运行时错误“438”对象不支持此属性或方法：
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Dim StartIndex As Integer
Dim EndIndex As Integer
Dim LoopIndex As Integer
'Dim sht As Worksheet
StartIndex = Sheets("TaskNew").Index + 1
EndIndex = Sheets("TaskEnd").Index - 1
For LoopIndex = StartIndex To EndIndex
'SheetsNavigationDropDown.AddItem Sheets(LoopIndex).Name
'Sheets("Dashboard").ListBox20.AddItem Sheets("Sample").Name
Sheets("Dashboard").ListBox20.AddItem Sheets(LoopIndex).Name
Next LoopIndex
I have tried it also as just ListBox20.AddItem ... but still doesn't work. Yes, ListBox20 exists on Sheets("Dashboard"), yes I edited it's name to eliminate spaces.
我也试过它只是 ListBox20.AddItem ......但仍然不起作用。是的，ListBox20 存在于表格（“仪表板”）上，是的，我编辑了它的名称以消除空格。
It chokes on/highlights the Sheets("Dashboard").List.... line when debugging.
它在调试时阻塞/突出显示 Sheets("Dashboard").List.... 行。
Ideas?
想法？
采纳答案by Kirk Hings
Sorry, answered my own question, key was to switch to an activex form element instead of regular.
抱歉，回答了我自己的问题，关键是切换到 activex 表单元素而不是常规元素。
Sheets("Dashboard").TaskSheetsComboBox.Clear
Dim StartIndex As Integer
Dim EndIndex As Integer
Dim LoopIndex As Integer
StartIndex = Sheets("TaskNew").Index + 1
EndIndex = Sheets("TaskEnd").Index - 1
For LoopIndex = StartIndex To EndIndex
Sheets("Dashboard").TaskSheetsComboBox.AddItem Sheets(LoopIndex).Name
Next LoopIndex
Private Sub TaskSheetsComboBox_Click()
Sheets(Sheets("TaskNew").Index + TaskSheetsComboBox.ListIndex + 1).Activate
End Sub
回答by barrowc
Might as well start with a sanity check:
不妨从健全性检查开始：
Dim o As OLEObject
Debug.Print "---"
For Each o In Sheets("Dashboard").OLEObjects
Debug.Print o.Name & " = " & o.progID
Next o
Debug.Print "---"
and you should get ListBox20 = Forms.Listbox.1as one of the results
你应该得到ListBox20 = Forms.Listbox.1作为结果之一
(edit: this works in Excel 2003 anyway)
（编辑：无论如何，这在 Excel 2003 中有效）
