vba VBA列表框按索引选择工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27930130/
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
VBA listbox select worksheet by index
提问by Mark
I have a form with listbox which dynamically provides a list of the worksheets in the current workbook (code below). I wish to take the selected Sheet and refer to it in a formula later in the process. From hours of playing around I cannot seem to accomplish this. I believe I read somewhere that you cannot take the string back to the sub and use it to refer to to an object. So I thought maybe I can create two listboxes
我有一个带有列表框的表单，它动态提供当前工作簿中的工作表列表（下面的代码）。我希望选取所选的工作表，并在此过程的稍后阶段在公式中引用它。经过数小时的玩耍，我似乎无法做到这一点。我相信我在某处读到您不能将字符串带回 sub 并使用它来引用一个对象。所以我想也许我可以创建两个列表框
- for sheet name
- for sheet index
- 对于工作表名称
- 表索引
that I could pass the index number to and maybe use that in my formula to lookup items from the correct sheet.
我可以将索引号传递给并可能在我的公式中使用它来从正确的工作表中查找项目。
For the life of my I cannot seem to find a way to connect the two since the items will always be changing; the code will be ran on multiple workbooks by multiple operators so the layout will most likely change between users. I can easily add the second list box with index #'s but I have a block on how to associate the name which will have meaning to the user and the index which I can pass back to the sub. I realize the "On click" procedure for the list box to associate the two but with the dynamic nature of the fields I cannot come up with the logic to put that into code.
对于我的生活，我似乎无法找到将两者联系起来的方法，因为项目总是在变化；代码将由多个操作员在多个工作簿上运行，因此布局很可能会在用户之间发生变化。我可以轻松地添加带有索引 # 的第二个列表框，但是我有一个关于如何关联对用户有意义的名称和我可以传递回子的索引的块。我意识到列表框的“点击”过程将两者关联起来，但由于字段的动态特性，我无法想出将其放入代码的逻辑。
For N = 1 To ActiveWorkbook.Sheets.Count
With ListBox1
.AddItem ActiveWorkbook.Sheets(N).Name
End With
Next N
采纳答案by peege
Try this out.
试试这个。
Declare a public variable above the code for the UserForm, making it available throughout your workbook from any module or code.
在用户窗体的代码上方声明一个公共变量，使其在您的工作簿中的任何模块或代码中均可用。
Public listChoice As String
Using your code to get the sheet names for the ListBox rowsource.
使用您的代码获取 ListBox 行源的工作表名称。
Private Sub UserForm_Activate()
For n = 1 To ActiveWorkbook.Sheets.count
With ListBox1
.AddItem ActiveWorkbook.Sheets(n).name
End With
Next n
End Sub
Including an update event for the ListBox
包括 ListBox 的更新事件
Private Sub ListBox1_AfterUpdate()
listChoice = ListBox1.Text
End Sub
I included a test just to demonstrate that the result is still retained. You don't need this, it demonstrates the results on the screenshot.
我包括一个测试只是为了证明结果仍然保留。你不需要这个，它在屏幕截图上展示了结果。
Private Sub cmdTestChoice_Click()
MsgBox ("The choice made on the ListBox was: " & listChoice)
End Sub
edit:To access that sheet later, you can call it using something like this:
编辑：要稍后访问该工作表，您可以使用以下内容调用它：
Some examples of different ways to access a cell, using .Range, or .Cells, with numbers or letters.
使用带有数字或字母的 .Range 或 .Cells 访问单元格的不同方式的一些示例。
Using lRow & lCol as Long to set row and column numbers.
使用 lRow 和 lCol 作为 Long 设置行号和列号。
Sheets(listChoice).Cells(lRow, lCol).Value = TextBox1.Value 'Set cell on sheet from TextBox
TextBox2.Value = Sheets(listChoice).Range("A2").Value 'Set TextBox From Cell on Sheet
'Set a cell on another sheet using the selected sheet as a source.
Sheets("AnotherSheet").Cells(lRow, "D") = Sheets(listChoice).Range("D2")
