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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.