Check out some similar questions!

dannac · Jan 26, 2011, 01:32 PM

I have a user form that inputs data to the next available row.

It starts the data in col A... I need it to start in col B.

Private Sub CommandButton1_Click()
' Make sure Sheet1 is active
Sheets("2011").Activate

' Determine next empty row
'Nextrow = _
'Application.WorksheetFunction.CountA(Range("A:A") ) + 1

nextrow = Range("B" & Rows.Count).End(xlUp).Offset(1)

' Transfers the name
Cells(nextrow, 2) = TextBox1.Text
Cells(nextrow, 3) = TextBox2.Text
Cells(nextrow, 4) = TextBox3.Text
Cells(nextrow, 5) = TextBox4.Text
Cells(nextrow, 6) = TextBox12.Text
Cells(nextrow, 7) = TextBox5.Text
Cells(nextrow, 8) = TextBox6.Text
Cells(nextrow, 9) = TextBox7.Text
Cells(nextrow, 10) = TextBox8.Text
Cells(nextrow, 11) = TextBox9.Text
Cells(nextrow, 12) = TextBox10.Text
Cells(nextrow, 13) = TextBox11.Text
Cells(nextrow, 14) = ComboBox1.Text
Cells(nextrow, 15) = ComboBox2.Text

' Clears form for next entry
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
TextBox1.SetFocus

End Sub

I thought this line would have it start in col B

Cells(nextrow, 2) = TextBox1.Text

JBeaucaire · Jan 26, 2011, 05:45 PM

First, use CODE tags, not QUOTE tags:

Originally Posted by dannac

It starts the data in col A .... I need it to start in col B.

Code:

Cells(nextrow, 2) = TextBox1.Text

I thought this line would have it start in col B

That line of code DOES insert a value in column B.

dannac · Jan 27, 2011, 06:26 AM

Hi Jerry... I cannot find what's wrong. It still paste data starting in col A.

Code:

'   Make sure Sheet1 is active
    Sheets("2011").Activate
    
'   Determine next empty row
    'Nextrow = _
    'Application.WorksheetFunction.CountA(Range("A:A")) + 1
    
    nextrow = Range("B" & Rows.Count).End(xlUp).Offset(1)
    
'   Transfers the name
    Cells(nextrow, 2) = TextBox1.Text
    Cells(nextrow, 3) = TextBox2.Text
    Cells(nextrow, 4) = TextBox3.Text
    Cells(nextrow, 5) = TextBox4.Text
    Cells(nextrow, 6) = TextBox12.Text

The last line in code above was because I added a textbox after the form had been made a few months... to separate First & Last Names.

JBeaucaire · Jan 27, 2011, 07:00 AM

There's nothing in this code that puts data in column A.

dannac · Jan 27, 2011, 07:36 AM

LOL... wish you could tell my computer that... cause it's not listening to me.

I'm stuck.

dannac · Jan 27, 2011, 08:05 AM

I'm noticing the code is in properties for sheet 2011
And same code is in properties for the userform.

Should it be in both ?

dannac · Jan 27, 2011, 08:17 AM

Now, when clicking add button it gives debug error and highlights the line :

Cells(nextrow, 2) = TextBox1.Text

I include file as attachment.

JBeaucaire · Jan 28, 2011, 11:34 AM

Originally Posted by dannac

I'm noticing the code is in properties for sheet 2011
and same code is in properties for the userform.

Should it be in both ?

Command Button code for a form should only be in the form module.

JBeaucaire · Jan 28, 2011, 11:41 AM

You didn't resolve the NEXTROW as a number:

Code:

'   Determine next empty row
    nextrow = Range("B" & Rows.Count).End(xlUp).Offset(1).Row

After fixing that, I ran the form and it inserted values in columns B:O.

dannac · Jan 28, 2011, 01:25 PM

Thanks Jerry.