Ok try agian. User form LastRow

[Jennifer]

Hi Hi Hi-
Ok, I am still trying to get my user form to work. Since the help earlier
just confused me even more I decided to start with a clean workbook and start
from scratch.
I have a user form that I want the user to be able to enter data or look at
data already in the database using <First> <Last><Next> and <Previous>
buttons and it show the record. Soooooo the following is as far as I have
gotten and have hit a wall. The cbofirst button works but I can't seem to get
the cboprev button to work. Thank you, Jennfier

Remember I am very green at this I need a lot of hand holding!

Private Sub cmdFirst_Click()
RowNumber.Text = "2"
End Sub

Private Sub cmdPrev_Click()

Dim r As Long

If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)

r = r - 1
If r > 1 And r <= LastRow Then
RowNumber.Text = FormatNumber(r, 0)

End If
End If
End Sub

Private Sub cmdSave_Click()

End Sub

Private Sub RowNumber_Change()
GetData
End Sub
Private Sub DisableSave()
cmdSave.Enabled = False
cmdClose.Enabled = False
End Sub


Though daily learning, I LOVE EXCEL!
Jennifer

 

[Patrick Molloy]

make the following the code behind the userform. I assume that the active
sheet has data in column A starting at row 2.
I also assume the form has four command buttons ( cmdFirst, cmdPrev,
cmdNext, cmdLast) as one textbox called RowNumber.
I called a long variable LastRow, and the use the form's Initialise event to
get this from the table and set the RowNumber textbox to the first row.


Option Explicit
Private LastRow As Long
Private Sub UserForm_Initialize()
LastRow = Range("A2").End(xlDown).Row
RowNumber = 2
End Sub

Private Sub cmdFirst_Click()
RowNumber.Text = "2"
GetData
End Sub

Private Sub cmdPrev_Click()
Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
r = r - 1
If r > 1 And r <= LastRow Then
RowNumber.Text = FormatNumber(r, 0)
End If
End If
GetData
End Sub
Private Sub cmdNext_Click()
Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = RowNumber
If r < LastRow Then
r = r + 1
Else
r = LastRow
End If
RowNumber = r
GetData
End If
End Sub
Private Sub cmdLast_Click()
RowNumber = LastRow
GetData
End Sub
Private Sub cmdSave_Click()

End Sub

Private Sub RowNumber_Change()
GetData
End Sub
Private Sub DisableSave()
cmdSave.Enabled = False
cmdClose.Enabled = False
End Sub
Private Function GetData()
' some code here to populate controls
End Function

 

[Jennifer]

Patrick,
As usual your awesome. It works like a charm! To bad you weren't around 8hrs
ago. Good learning experiences I guess. Thank you!

 

[Patrick Molloy]

shucks, blush

Patrick,
As usual your awesome. It works like a charm! To bad you weren't around 8hrs
ago. Good learning experiences I guess. Thank you!

 

[Charlie]

This is VERY helpful to me, also. One more thing I can't quite figured out:
I Column A contains Names, and I have a textbox on the form called
textboxName, what code would I add to have the name displayed in this textbox
this pertains to the chosen row number?
thank you,
Slowly Learning

 

[carlo]

Hi Charlie

you could add following code:

me.textboxName.value = worksheets("myWorksheet").cells(RowNumber,
1).value

hth

Carlo

 

[Charlie]

GREAT! I came up with something, but it's way cumbersome (as usual), and
I'll need to apply this code to numerous textboxes..
....have another question, but maybe I'm suposed to start a new thread?
.....so will do so.
thanks.