Display next and previous rows on userform

[paraffin]

Hi,

I'm having a nightmare trying to find code to display (and edit if needed) the previous and next rows when using my userform.

The userform is simple, includes 7 textbox and 1 combobox. I've managed to get the userform to populate the data sheet on the first blank row but can't figure out how to 'navigate' the rows (to edit if there was any changes to be done etc)

I plan to use the data to fill in a protected viewer friendly worksheet/table, therefore the user can't simply go and edit the data directly.

Heres my code for the insert and cancel buttons;

Private Sub Insert_Click()
Dim CurrentRow As Long
Dim lastRow As Long
Dim ws As Worksheet
Set ws = Worksheets("SDP")

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

'copy the data to the database
With ws
  .Cells(CurrentRow, 1).Value = Me.ComboBox1.Value
  .Cells(CurrentRow, 2).Value = Me.TextBox1.Value
  .Cells(CurrentRow, 3).Value = Me.TextBox2.Value
  .Cells(CurrentRow, 4).Value = Me.TextBox3.Value
  .Cells(CurrentRow, 5).Value = Me.TextBox4.Value
  .Cells(CurrentRow, 6).Value = Me.TextBox5.Value
  .Cells(CurrentRow, 7).Value = Me.TextBox6.Value
  .Cells(CurrentRow, 8).Value = Me.TextBox7.Value
  
End With

End Sub

Private Sub Cancel_Click()
  Unload Me
End Sub

I've already got a Next and Previous button set up on the form

Thanks in advance

 

[SlinkRN]

Hi Paraffin,
What I have done in the past is allow the user to click on one cell in the sheet that will then bring up the userform with that row's data. In other words, on my sheets, it's usually a name in column A that they can click on and then the form with that person's data pops up and then you can use the active cell reference to fill the userform and fill the sheet when the userform closes.
If you wanted to use the Previous and Next buttons instead, I guess you would just have to increment the row by either adding or subtracting 1 from the current row.
If you'd like to go with my first selection, I can give you a sample of the code I was helped with at one time for a worksheet selection event.
HTH, Slink

 

[Marcelo Branco]

Hi,

Follows a link to a very good article about Creating Advanced User Forms

There is a complete example using First, Previous, Next and Last buttons

See
http://msdn.microsoft.com/en-us/library/aa192538(v=office.11).aspx

M.

 

[universalpumps]

Hi SlinkRN
Pls give me the sample code which will allow me to "allow the user to click on one cell in the sheet that will then bring up the userform with that row's data. In other words, on my sheets, it's usually a name in column A that they can click on and then the form with that person's data pops up and then you can use the active cell reference to fill the userform and fill the sheet when the userform closes."
Thanks

Hi Paraffin,
What I have done in the past is allow the user to click on one cell in the sheet that will then bring up the userform with that row's data. In other words, on my sheets, it's usually a name in column A that they can click on and then the form with that person's data pops up and then you can use the active cell reference to fill the userform and fill the sheet when the userform closes.
If you wanted to use the Previous and Next buttons instead, I guess you would just have to increment the row by either adding or subtracting 1 from the current row.
If you'd like to go with my first selection, I can give you a sample of the code I was helped with at one time for a worksheet selection event.
HTH, Slink

 

[SlinkRN]

Hi there,
When you build your userform with textboxes and comboboxes etc, make sure your code contains the correct names for your text boxes etc and then put this code in the userform_activate code behind the form.

Private Sub UserForm_activate()
Dim rw As Integer
rw = ActiveCell.Row
txtLname = Range("b" & rw)
txtFName = Range("c" & rw)
If Range("d" & rw) <> "" Then cbAge = Range("d" & rw)
txtBirthDate = Format(Range("e" & rw), "mm/dd/yyyy")
txtBirthTime = Format(Range("f" & rw), "hh:mm")

End Sub

Then put this code behind your worksheet (right click on the sheet tab and choose "view code" and put this code there):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Target.Count > 1 Then Exit Sub 'so if more than one cell selected, the userform won't pop up
If Not Intersect(Target, Range("A2:A3000")) Is Nothing Then 'ie user has selected a name in column
    frmAdmit.Show   'open the form
End If
continue:


End Sub

Now, when you click on a cell in column A, it will fill your userform with the data that is on that row. Let me know if you have any problems with this.
Slink