CODEWITHSUNDEEP
vbaexcel
Pete
Pete

Reputation: 171

VBA Entering userform data at next blank row correctly

  1. Created a userform
  2. Added a textBox and a comboBox
  3. Added a submit button
  4. 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?

Upvotes: 4

Views: 67484

Answers (2)

Chrismas007
Chrismas007

Reputation: 6105

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

If you want a method using 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

Upvotes: 13

silentsurfer
silentsurfer

Reputation: 2428

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.

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.

E.g.:

Dim myCell as Range

Set myCell = ThisWorkbook.Sheets(1).Range("C4")
myCell.Value = TextBox3.Text
myCell.Offset(0, 1).Value = ComboBox1.Text

'[...]

Upvotes: 0

Related Questions