Userform Specify Sheet to be used VBA Question

DJMXM

New Member
Joined
Jun 19, 2013
Messages
45
Hello all....

I once again have bumped up against something I am still learning and need a little help. I am putting together a User Form to add new products to an existing list. I followed an example I found but when I go to run it I keep getting and error. I need to specify the sheet where it is going. The details are listed below along with the code that I came up with. Since I am still learning VBA (Mostly by trial and error and lots of reading) I need help from time to time.

Sheet where the information goes is "Start Here Sheet"

Column it needs to start at is "BI" and end in Row 206

I already have some data in these columns so this would just be adding items that don't exist already in the list.

Private Sub CommandButton1_Click()
Dim LastRow As Object

Set LastRow = Sheets("Start Here Sheet").Range("BI206").End(x1Up)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
LastRow.Offset(1, 3).Value = TextBox4.Text
LastRow.Offset(1, 4).Value = TextBox5.Text
LastRow.Offset(1, 5).Value = TextBox6.Text
LastRow.Offset(1, 6).Value = TextBox7.Text

MsgBox "One Item Added To Produce & Dairy List"

response = MsgBox("Do You Want To Add Another Item?", _
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""

TextBox1.SetFocus

Else
Unload Me
End If

End Sub

Private Sub CommandButton2_Click()
End
End Sub

Thank You in advance for your Help - Sorry I don't know how to put the code in a separate box yet either.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Would be easier to troubleshoot if you listed the error message you were getting. Two things off the bat though:

Dim LastRow As Object
should be
Dim LastRow as Range

and
Set LastRow = Sheets("Start Here Sheet").Range("BI206").End(x1Up) <--- you typed x1up, it should be xlup (the letter 'ell', not the number '1')

to post your code to the board, type "code" in brackets to start, then at the end type "/code" also in brackets
 
Upvote 0
Thank You ChrisM... That did the trick for that part... Now I am on to the new part of the challenge!
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top