vba VBA在下一个空白行正确输入用户表单数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27783783/
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 Entering userform data at next blank row correctly
提问by Pete
- Created a userform
- Added a textBox and a comboBox
- Added a submit button
- When submit is clicked it adds the data to a spreadsheet
- 创建了一个用户表单
- 添加了一个文本框和一个组合框
- 添加了提交按钮
- 单击提交时，它会将数据添加到电子表格中
From what I have been told and what I have read this is wrong
从我被告知和我读到的内容来看，这是错误的
ActiveCell.Value = TextBox3.Text
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ComboBox1.Text
ActiveCell.Offset(1, -1).Select
This works but I've been told I shouldn't use the .select keyword when possible. I've read that to make my code reusable I should create variables. How would a professional developer write this code, can it be written in less lines and how can I refer to the activecell offset without using select?
这有效，但我被告知我不应该在可能的情况下使用 .select 关键字。我读过为了使我的代码可重用，我应该创建变量。专业开发人员将如何编写此代码，是否可以用更少的行编写，以及如何在不使用 select 的情况下引用 activecell 偏移量？
回答by Chrismas007
I am assuming you want TextBox3in column A and ComboBox1in column B. If you want different columns just change the letter references.
我假设您想要TextBox3在 A 列和ComboBox1B 列中。如果您想要不同的列，只需更改字母引用。
Sub OnClick() 'whatever your current sub is called.
Dim LastRow As Long, ws As Worksheet
Set ws = Sheets("Name of Sheet where data is going")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
ws.Range("A" & LastRow).Value = TextBox3.Text 'Adds the TextBox3 into Col A & Last Blank Row
ws.Range("B" & LastRow).Value = ComboBox1.Text 'Adds the ComboBox1 into Col B & Last Blank Row
End Sub
If you want a method using Offset():
如果你想要一个使用的方法Offset()：
Sub OnClickwithOffset() 'whatever your current sub is called.
Dim LastRow As Long, ws As Worksheet
Set ws = Sheets("Name of Sheet where data is going")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
ws.Range("A" & LastRow).Value = TextBox3.Text 'Adds the TextBox3 into Col A & Last Blank Row
ws.Range("A" & LastRow).Offset(0, 1).Value = ComboBox1.Text 'Adds the ComboBox1 into next cell to the right of TextBox3 data.
End Sub
回答by silentsurfer
The main reason why you want to avoid using ActiveCell is that it may produce unexpected results should the user select another cell while the code is executing.
您想要避免使用 ActiveCell 的主要原因是，如果用户在代码执行时选择另一个单元格，它可能会产生意外结果。
If your goal is to always write the contents of your controls to the same cells, you may wish to start by defining a variable of type Range and set your offsets relative to that variable.
如果您的目标是始终将控件的内容写入相同的单元格，您可能希望首先定义一个 Range 类型的变量并设置相对于该变量的偏移量。
E.g.:
例如：
Dim myCell as Range
Set myCell = ThisWorkbook.Sheets(1).Range("C4")
myCell.Value = TextBox3.Text
myCell.Offset(0, 1).Value = ComboBox1.Text
'[...]
