Send data from userform to specific cell on specific sheet?

[JennLee]

Can someone PLEASE tell me how to send data from the userform to a specific
cell on a specific worksheet? I have most entries going to each row via the
example code found on Microsoft.com and it works great:

Dim LastRow As Object
Set LastRow = Sheet2.Range("a65536").end(xlUp)

LastRow.Offset(1, 0).Value = TextBox7.Text
LastRow.Offset(1, 1).Value = ComboBox1.Text etc., etc, etc.

BUT I need for a couple of textboxes and a combo box on the userform to go
to a specific cell on Sheet2. PLEASE HELP!!!!! This has stressed me out
tremendously today!!

 

[Gary''s Student]

do you mean??

Sheets("Sheet2").Range("Z100") = Box.Text

 

[Vergel Adriano]

As an example, let's say you wanted to put the text in TextBox1 to cell A1 in
Sheet2:

Sheet2.Range("A1").Value = TextBox1.Text

There are many ways to refer to a cell in a worksheet, but I think the one
above is the easiest to follow.

 

[brianllaird]

Can someone PLEASE tell me how to send data from the userform to a specific
cell on a specific worksheet? I have most entries going to each row via the
example code found on Microsoft.com and it works great:

Dim LastRow As Object
Set LastRow = Sheet2.Range("a65536").end(xlUp)

LastRow.Offset(1, 0).Value = TextBox7.Text
LastRow.Offset(1, 1).Value = ComboBox1.Text etc., etc, etc.

BUT I need for a couple of textboxes and a combo box on the userform to go
to a specific cell on Sheet2. PLEASE HELP!!!!! This has stressed me out
tremendously today!!

Jennifer,

Try:

sheets(2).Cells(1,1).value = TextBox7.text

where cells(1,1) = 1st row, 1st column (adjust as you want)

 

[JennLee]

Thanks so much - it worked as far as sending that data to the cell on the
worksheet BUT I didn't know where in the code to put that line of code so I
typed it under all of those LastRow codes. That made all those entries that
WERE going into row 2, row 3, etc to NOW going up under the B25 where I
wanted that specific data to go to. PLEASE HELP AGAIN!!! So much
appreciated!

 

[Vergel Adriano]

Where you put the code that I (and the others) gave you depends on when you
want it to execute. Since you're using a form, I would assume you want it to
happen at the click of a button. If so, what you need to do is open the user
form in the VBA IDE and double click the button that will trigger this
action. You should now be in the code window, with your cursor inside a sub
procedure. That is where you would put the code that you want to execute
when the button is closed.

As for the other lines of code that you have, where do you have them right
now? And, do you need them to do what they're doing? If not, simply delete
or comment them out.

 

[JennLee]

I DO need the code to continue to insert the other data at last empty row. I
realized that I had added a row at the BOTTOM of my sheet that had formulas
to calculate the data that is sent from the userform. So that's why when I
entered the code you gave me, it WORKED by putting that specific data from
the form into B25 but then the other data went under it since that was last
empty row!! Clear as mud?!! SO I moved that formula row to the top
(instead of row 25 and it worked fine) I typed under the commandbutton1 Dim
Sheet As Object then the code you gave me, adding Set in front of it and it
worked. Then I added the same Set code for the additional two textboxes that
I wanted in specific cells and it hung up. Can you tell what I might have
done wrong?
Thanks so much for your help.

 

[Vergel Adriano]

I think I follow what you're trying to describe, but I think I'm still
missing something... Perhaps if you post here the code that you ended up
with, we'll spot the problem...

 

[JennLee]

Ok - here's my original code and it works fine -
need to add that line of code somewhere that you gave me -
Sheet2.Range("B2").Value = ComboBox5.Text
so that three particular textboxes and 1 combobox values goes to B2, D2, F2,
and H2 AND the other go just like I have it below - ALL FROM THAT ONE COMMAND
BUTTON!!

Private Sub CommandButton1_Click()
Dim LastRow As Object

Set LastRow = Sheet2.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox7.Text
LastRow.Offset(1, 1).Value = ComboBox1.Text
LastRow.Offset(1, 2).Value = ComboBox2.Text
LastRow.Offset(1, 3).Value = ComboBox3.Text
LastRow.Offset(1, 4).Value = TextBox1.Text
LastRow.Offset(1, 5).Value = TextBox2.Text
LastRow.Offset(1, 6).Value = TextBox3.Text
LastRow.Offset(1, 7).Value = ComboBox4.Text
LastRow.Offset(1, 8).Value = TextBox4.Text
LastRow.Offset(1, 9).Value = TextBox5.Text
LastRow.Offset(1, 10).Value = TextBox6.Text

MsgBox "One record written to Monday's Sheet"

response = MsgBox("Do you want to enter another record for this date?",
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
ComboBox1.Text = ""
ComboBox2.Text = ""
ComboBox3.Text = ""
ComboBox4.Text = ""

ComboBox1.SetFocus

Else
Unload Me
End If

End Sub



Private Sub CommandButton7_Click()
End
End Sub



Private Sub UserForm_Initialize()
ComboBox1.AddItem "Enroll"
ComboBox1.AddItem "Initial Assessment"
ComboBox1.AddItem "Bi-A /A Assessment"
ComboBox1.AddItem "Maintenance/Symptom"
ComboBox1.AddItem "Other (TENS)"
ComboBox1.ListIndex = 0

ComboBox2.AddItem "Pre-Scheduled"
ComboBox2.AddItem "Locate"
ComboBox2.ListIndex = 0

ComboBox3.AddItem "Attempt"
ComboBox3.AddItem "Incomplete"
ComboBox3.AddItem "Complete"
ComboBox3.ListIndex = 0

ComboBox4.AddItem "Yes"
ComboBox4.AddItem "No"
ComboBox4.ListIndex = 0

ComboBox5.AddItem "Bratcher, Sherry"
ComboBox5.AddItem "Clark, Glenda"
ComboBox5.ListIndex = 0

End Sub

 

[Vergel Adriano]

Does the code that puts data into B2, D2, F2, and H2 need to happen every
time a new record is added, or only when the form closes?

If it needs to happen everytime a new record is added, then you would want
to put them before the line that shows a messagebox saying that a record has
been inserted.

If it needs to happen only when the form closes, then you'll need to put
those lines before the line that says "Unload Me".

 

[JennLee]

That did it!!! Thank you so much - you're a life-saver!