Excel VBA Form Add data to a specific range

[JJCam]

Hi, I hope someone can help me out as I am really struggling with adding data from a user form to a specific range. Let's say I have a form (I just made up a real quick one for demonstration):

Where the Add Button basically sends the values of the textboxes into a specific range. If I rewrite a new ID, name and Job I want to fill the values on the following row and so on.

As per my sheet (also for reference), it would look like this and the yellow cells are the range where the values should go. This yellow range would be dynamic at the bottom.

I thought the easiest way would be to set up a starting range and then find the last empty row in column B but the empty row gives me error (I tried from different sources).

The code is:

Private Sub CommandButton1_Click()
Dim emptyRow As Long
Sheet1.Activate
emptyRow = .Range("B" & Rows.Count).End(xlUp).Row

Cells(emptyRow, 2).value = TextBox_ID.value
Cells(emptyRow, 3).value = TextBox_Name.value
Cells(emptyRow, 3).value = TextBox_Job.value
End Sub

I'd appreciate it if someone can shed the light. Thanks!

JJC

 

[dmt32]

Hi,
you have a period (full stop) in front of .Range which is not qualified to a worksheet object in your code.

As your sheet is the active sheet try changing that line for one below & see if resolves your issue

emptyRow = Range("B" & Rows.Count).End(xlUp).Row + 1

Dave

 

[JJCam]

Thanks so much, that did the trick!
I cannot believe how easy it seemed and how much time I spent looping around to find a solution.
Thanks again!