Help with VBA user form data

[rgreyb]

How do I link data input to a UserForm TextBox to a specific cell in my worksheet?

I am trying to create a data input form.
Thanks

 

[bertie]

Hi and welcome to the forum.

Say you had a form with two text boxes and one command button.

To place the text box contents into a specific cell you would use something like:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()
   [COLOR=darkblue]With[/COLOR] Sheets("Sheet1")
      .Range("A1").Value = TextBox1.Value
      .Range("B1").Value = TextBox2.Value
      [COLOR=green]'etc[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

If you want to find the next available row to input the data try this:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton2_Click()
   [COLOR=green]'===========================[/COLOR]
   [COLOR=green]'find the next available row[/COLOR]
   [COLOR=green]'===========================[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] [COLOR=Red]rw [/COLOR][COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   
   [COLOR=darkblue]With[/COLOR] Sheets("sheet1")
      [COLOR=green]'find the next available row for input[/COLOR]
      [COLOR=Red]rw [/COLOR]= .Range("A" & .Rows.Count).End(xlUp).Row + 1
      .Range("A" &[COLOR=Red] rw[/COLOR]).Value = TextBox1.Value
      .Range("B" &[COLOR=Red] rw[/COLOR]).Value = TextBox2.Value
      [COLOR=green]'etc[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
   
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[rgreyb]

Bertie, you're brilliant. Thanks so much! I am just starting with VBA, and it was amazing how such a simple procedure could be so vexing.