VBA在下一个空白行正确输入用户表单数据 [英] VBA Entering userform data at next blank row correctly

问题描述

1. 创建了用户表单
2. 添加了一个文本框和一个组合框
3. 添加了一个提交按钮
4. 单击提交时，它将数据添加到电子表格

从我被告知和我读到的内容来看，这是错误的

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  

这有效，但我被告知我不应该这样做尽可能使用.select关键字.我读过，为了使我的代码可重用，我应该创建变量.专业的开发人员将如何编写此代码，是否可以用更少的行编写?我如何在不使用select的情况下引用activecell偏移?

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?

推荐答案

我假设您要在A列中使用TextBox3在B列中使用ComboBox1.如果要使用其他列，只需更改字母引用即可.

I am assuming you want TextBox3 in column A and ComboBox1 in column B. If you want different columns just change the letter references.

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

如果要使用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

这篇关于VBA在下一个空白行正确输入用户表单数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！