PDA

View Full Version : [SOLVED] Userform with doubleclick filled with values of autofilter



PNI001
08-09-2014, 12:25 PM
Dear fellow forummembers,

I am new on this forum, have a little experience with VBA and would like ask for some help. I have found an very interesting solution of Charlize, article subject: Userform with doubleclick filled with values

This works fine, but when I use an autofilter and the criteria reduces the number of rows, the Userform shows the hidden rows as well as the filtered rows. How could it be made that the Userform only steps through the visible rows?

I have searched the internet for several weeks but I can't find any solution. The sheet is added and I hope there will be an answer. Already many thanks for putting efford on this issue.

Greetings, Peter

westconn1
08-09-2014, 04:30 PM
try like

Private Sub NextRecord_Click()
'Proceed to next record
RowNum = RowNum + 1

If Cells(RowNum, 1).Value <> vbNullString And Cells(RowNum, 1).Height > 0 Then
GetData Cells(RowNum, 1)
Me.Caption = "Rijnummer: " & RowNum
Else
'It's not allowed to skip last record
Beep
RowNum = RowNum
Me.Caption = "Laatste rij met gegevens in deze lijst !!!"
End If


End Sub

Private Sub PreviousRecord_Click()
'Go back one record
If RowNum = 2 Or Cells(RowNum, 1).Height = 0 Then
RowNum = 2
Beep
Me.Caption = "Eerste rij met gegevens in deze lijst !!!"
Else
RowNum = RowNum - 1
Me.Caption = "Rijnummer: " & RowNum
End If

GetData Cells(RowNum, 1)

End Sub

PNI001
08-10-2014, 12:20 PM
Hi Westconn1,
Thank you for responding so quickly!

I am glad with your solution, for me it's a important step forward. But there remains one issue. If the rownumbers in the filtered range are non-continuous, for example 1,2,5,6 I have to click three times to be able to see the contents of row 5. Do you also have a solution for this?
Greetings, Peter

westconn1
08-10-2014, 02:25 PM
you can test

Private Sub NextRecord_Click()
'Proceed to next record
RowNum = RowNum + 1
Do Until Cells(RowNum, 1).Height > 0 Or IsEmpty(Cells(RowNum, 1))
RowNum = RowNum + 1
Loop

If Cells(RowNum, 1).Value <> vbNullString Then
GetData Cells(RowNum, 1)
Me.Caption = "Rijnummer: " & RowNum
Else
'It's not allowed to skip last record
Beep
RowNum = RowNum
Me.Caption = "Laatste rij met gegevens in deze lijst !!!"
End If


End Sub

Private Sub PreviousRecord_Click()
'Go back one record
RowNum = RowNum - 1
Do Until RowNum = 2 Or Cells(RowNum, 1).Height > 0
RowNum = RowNum - 1
Loop
If RowNum = 1 Then
RowNum = 2
Beep
Me.Caption = "Eerste rij met gegevens in deze lijst !!!"
Else
Me.Caption = "Rijnummer: " & RowNum
GetData Cells(RowNum, 1)
End If


End Sub

PNI001
08-11-2014, 03:20 AM
Hi westconn1,

Many thanks for your response in such a little time. It is exactly what I asked for. I am very pleased with your support in this forum!

Best regards,
Peter Nijsten