vba 如何使用组合框输入在excel vba中选择要复制数据的工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13222885/
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
How to use combo box input to select work sheet to copy data to in excel vba
提问by JulieD
I think this is a simple question but as I haven't used excel in this way before I am struggling. I am trying to create a program based in Excel VBA. I work in a call center. We have numerous excel sheets to log calls for different schemes. I have a project to streamline this process. So far I have created a workbook that has separate sheets named to represent each scheme. I have created a userform to gather information. One of the combo boxes indicates which scheme the call is in regards to. The input from the userform needs to be logged on to a sheet in the work book that corresponds to the name of the scheme selected in the combo box. I cant seem to get this close to working. Any suggestions or advice would be greatly appreciated!
我认为这是一个简单的问题，但因为在我挣扎之前我没有以这种方式使用过 excel。我正在尝试创建一个基于 Excel VBA 的程序。我在呼叫中心工作。我们有许多 excel 表来记录不同方案的调用。我有一个项目来简化这个过程。到目前为止，我已经创建了一个工作簿，其中有单独的工作表，分别命名为代表每个方案。我创建了一个用户表单来收集信息。其中一个组合框指示呼叫所涉及的方案。来自用户表单的输入需要登录到工作簿中与组合框中选择的方案名称相对应的工作表。我似乎无法接近工作。任何建议或意见，将不胜感激！
The code I have is:
我的代码是：
Private Sub cmdClear_Click()
Call UserForm_Initialize
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub ComboBox1_Change()
Dim ShtName As String
ShtName = ComboBox1.List(ComboBox1.ListIndex)
End Sub
Private Sub CommandButton1_Click()
Call Add_Data
End Sub
Private Sub Add_Data()
Dim Worksheets As Range
Dim ws As Worksheet
Dim UserForm As Object
Dim iRow As Long
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set ws = this.ComboBox1.GetItemText(this.ComboBox1.SelectedItem)
'find first empty row in database
iRow = ShtName.Cells(Rows.Count, 1).End(x1up).Offset(1, 0).Row
'copy the data to the database
ws.Cells(iRow, 1) = Date
ws.Cells(iRow, 2) = Me.txtName.Value
ws.Cells(iRow, 3) = Me.txtNumber.Value
ws.Cells(iRow, 4) = ComboBox2.Value
ws.Cells(iRow, 5).Value = Me.txtQuery.Value
If optYes = True Then
msgstr = "Yes"
ElseIf optNo = True Then
msgstr = "No"
End If
ws.Cells(iRow, 6).Value = msgstr
If chkMain = True Then
msgstri = "Main"
ElseIf chkExec = True Then
msgstr = "Exec"
Else
msgstri = ""
End If
ws.Cells(iRow, 7).Value = msgstri
ws.Cells(iRow, 8).Value = Me.txtTime.Value
End Sub
Private Sub UserForm_Initialize()
ComboBox1.Clear
ComboBox1.AddItem ("Sheet2")
ComboBox1.AddItem ("Sheet3")
ComboBox1.AddItem ("Sheet4")
ComboBox1.AddItem ("Sheet5")
ComboBox1.AddItem ("Sheet6")
ComboBox1.AddItem ("Sheet7")
ComboBox1.AddItem ("Sheet8")
ComboBox1.AddItem ("Sheet9")
ComboBox1.AddItem ("Sheet10")
ComboBox1.AddItem ("Sheet11")
ComboBox1.AddItem ("Sheet12")
ComboBox2.Clear
ComboBox2.AddItem ("agent1")
ComboBox2.AddItem ("agent2")
ComboBox2.AddItem ("agent3")
ComboBox2.AddItem ("agent4")
ComboBox2.AddItem ("agent5")
ComboBox2.AddItem ("agent6")
ComboBox2.AddItem ("agent7")
ComboBox2.AddItem ("agent8")
ComboBox2.AddItem ("agent9")
ComboBox2.AddItem ("agent10")
txtNumber.Value = ""
txtName.Value = ""
txtQuery.Value = ""
txtTime.Value = ""
optYes = False
optNo = False
chkMain = False
chkExec = False
ComboBox1.SetFocus
End Sub
采纳答案by Bmo
You should be getting an error at
你应该得到一个错误
Set ws = this.ComboBox1.GetItemText(this.ComboBox1.SelectedItem)
You are trying to set an object to a string. Try using
您正在尝试将对象设置为字符串。尝试使用
Set ws = Sheets(this.ComboBox1.GetItemText(this.ComboBox1.SelectedItem))
This will actually give you the ws object in that variable. Sheets is the collection of worksheets in the workbook. You can select by index number or by the name of the sheet.
这实际上会为您提供该变量中的 ws 对象。Sheets 是工作簿中工作表的集合。您可以通过索引号或工作表名称进行选择。
