VBA Fill Textbox from Cell

[ksm283]

Hi there,

I'm currently designing a check-in program for my organization what will do three things:

1. The user will put their ID# into a form and click "submit"
2. VBA code places the ID number in the first available cell of the first available row on Worksheet1. The next 6 cells in that row run Vlookups to access data from the master participant sheet (Worksheet2)
3. VBA code runs an IF statement to see if the participant raised enough money, and then opens either an Accepted or Denied form

What I want to do is have the textboxes on the Accepted form display the information from the 6 vlookup cells (ie, have all that information put FROM the cell TO the textboxes on the form).

This is a snippet of the code I have for the first portion of the process, which places the number from the textbox into the first available row:

Private Sub Submit_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("SignIn")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

Can I somehow manipulate this to have it find the last filled row (maybe by making the 1 a -1)? And then how do I get it to take the information from that row and place it into the right textboxes?

Thanks,
Sean

 

[ksm283]

**BUMP** Anyone have some ideas about this?

 

[mikerickson]

ws.Cells(Rows.Count, 1).End(xlUp) is the last filled cell in column A of ws

 

[ksm283]

Thanks! Now how would I code it to make it take information from a cell in that row, and put it into a textbox on my form? I've tried different variations of this code, but to no avail:

Set Me.txtName.Value = ws.Cells(iRow, 2).Value

I've tried removing and replacing the 'Set' and switching the order that these come in...any thoughts?

 

[mikerickson]

Change the Set to Let and you should be in business.
Set is for objects.
Let (a default not usually expressed) is for values.

 

[ksm283]

...still nothing. Here's my current code:

Private Sub ShowInfo_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("SignIn")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp)

'put cell contents into form textboxes
Let Me.txtName.Value = ws.Cells(iRow, 2).Value

End Sub

For some reason, it giving me error code 1004, and I can't quite seem to figure out what's wrong.

 

[mikerickson]

iRow = ws.Cells(Rows.Count, 1).End(xlUp)

This line is looking at the cell and setting iRow to that cell's (default) property .Value. (From the error, I suspect that that value may be 0.)
Try replacing that line with

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

 

[ksm283]

Yes! That did it!

Thanks so much for your help - you're a lifesaver!