Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel limit records userform displays to those filtered 2

Status
Not open for further replies.
MrMode

MrMode

Technical User
Aug 28, 2003
195
GB
I have two userforms.

The first (frmRecords) shows each row as a record with navigation buttons (First, Next, Previous, Last). It also has a text box giving the number of the record (i.e. 1) and label that gives the total number of records (i.e. of 7). This works correctly and allows the user to cycle through all the records in the worksheet, edit and add new ones.

The second (frmSearch) allows filters to be applied to the worksheet - this is working correctly.

When the second closes, the first is reopened, however, it shows all the records, not the filtered recordset!

How do I get frmRecords to only show the filtered records in the worksheet?

I used the Office Dev example to help me construct the userform I am assuming I need to add some qualification in here as it is called when the userform is initialized.

Code: 
Private Sub GetData()
'error handling

'GetData copies the data from the currently active worksheet to the user form.
'After declaring a temporary variable r to hold the current row,
'the routine verifies that the value in the RowNumber control is numeric.

Dim r As Long
LastRow = FindLastRow

If IsNumeric(RowNumber.Text) Then
    r = CLng(RowNumber.Text)
    
'Knowing that RowNumber contains a numeric value, the CLng function is used to convert
'the value in RowNumber into the variable r. The rest of the code merely uses r to
'extract the information from the proper row and copy it to the correct field.

Else
    ClearData
'ClearData routine simply assigns an empty string to each field on the form to clear out
'any values that might have already been displayed on the form.
    
    MsgBox "Illegal row number"
    Exit Sub
    
End If

'Populate all the controls on the userform

    Index.Text = Cells(r, 1)
    Category1.Text = Cells(r, 2)
    Contractref.Text = Cells(r, 3)
    Title.Text = Cells(r, 4)
    Description.Text = Cells(r, 5)
    Contracttype1.Text = Cells(r, 6)
    Status1.Text = Cells(r, 7)
    Targetdate = Cells(r, 8)
    Tenderlist = Cells(r, 9)
    Subcontract = Cells(r, 10)
    Value1 = Cells(r, 11)

    DisableSave

'error handling

End Sub
 
Sort by date Sort by votes
[tt]Cells(r, 1).EntireRow.Hidden[/tt] can be used to test if row r is visible.

combo
 
Upvote 0 Downvote
Please excuse my complete ignorance, where would I use this test, and how? I am not clear on where to put it.
 
Upvote 0 Downvote
Your question was...
How do I get frmRecords to only show the filtered records in the worksheet?
Click to expand...

combo replied...
[highlight #FCE94F]Cells(r, 1)[/highlight].EntireRow.Hidden can be used to test if row r is visible.
Click to expand...

This is where you Populate controls for a row in the worksheet...
Code: 
'Populate all the controls on the userform

    Index.Text = [highlight #FCE94F]Cells(r, 1)[/highlight]
    Category1.Text = Cells(r, 2)
    Contractref.Text = Cells(r, 3)
    Title.Text = Cells(r, 4)
    Description.Text = Cells(r, 5)
    Contracttype1.Text = Cells(r, 6)
    Status1.Text = Cells(r, 7)
    Targetdate = Cells(r, 8)
    Tenderlist = Cells(r, 9)
    Subcontract = Cells(r, 10)
    Value1 = Cells(r, 11)

So here's what you could do...
Code: 
'Populate all the controls on the userform
If Not Cells(r, 1).EntireRow.Hidden Then 
    Index.Text = Cells(r, 1)
    Category1.Text = Cells(r, 2)
    Contractref.Text = Cells(r, 3)
    Title.Text = Cells(r, 4)
    Description.Text = Cells(r, 5)
    Contracttype1.Text = Cells(r, 6)
    Status1.Text = Cells(r, 7)
    Targetdate = Cells(r, 8)
    Tenderlist = Cells(r, 9)
    Subcontract = Cells(r, 10)
    Value1 = Cells(r, 11)
End If
 
Upvote 0 Downvote
As I understood, you use filter to display data in the worksheet and need to have filtered data in the userform. In this case you should test visibility of cells (accesible in vba via EntireRow property) before populating the userform, whatever controls you use. BTW, no easy way with listbox and RowSource property.


combo
 
Upvote 0 Downvote
My 2 cents along with MNSHO, is that Excel is designed for the user to interact DIRECTLY with the data, NOT via forms. If you want to have an application that isolates the user from the data, then use a database application like Access where the users DEFINITELY should NOT interact directly with the data, as it is designed to do.

What you are doing, IMNSHO, is akin to using a butter knife to turn a screw. Yes, it can be done, but its not the correct tool for the job!
 
Upvote 0 Downvote
Fantastic, that is limiting the results to only the filtered records. Thank you!

Another question, the form shows all the records, but where they are filtered the record is 'blank' (It has not copied across any data to populate the controls because they are hidden). I am guessing that it still trying to show all the rows that have records even though they are hidden because of a function that calculates the last row which is called along with GetData when the userform is initialized.

Code: 
Private Sub UserForm_Initialize()

    GetData
    LastRow = FindLastRow
       
End Sub

I am assuming that I will have to add the same test to this in order to only have x records available to the userform...

Code: 
Private Function FindLastRow()

    Dim r As Long

    r = 2
    Do While r < 65536 And Len(Cells(r, 1).Text) > 0
        r = r + 1
    Loop
    
    FindLastRow = r

End Function

 
Upvote 0 Downvote

SkipVought I know, and I could handle that by myself, but I have been specifically asked to do this in Excel - painful.
 
Upvote 0 Downvote
Well if the row in the sheet is hidden, then don't increment the the row counter IN THE FORM.

BTW, MrMode, the tip for which you gave me a star, was combo's tip to you. I just showed you haw to use combo's tip.

combo deserves the star, not me!
 
Upvote 0 Downvote

...also
Code: 
Do While r < 65536

You have hard coded a value that is only valid for some versions of Excel.

At one time it was 16,384, then 65,536, and since 2007, 1,048,576.

In order to make this code so it will not be out of date, so to speak, actually count the rows in the sheet...
Code: 
Do While r < Cells.Rows.Count
 
Upvote 0 Downvote
Your FindLastRow function returns row with first blank cell in col. A, if you need last non-empty cell, deduct 1.
You can simplify this function:
[tt]Private Function FindLastRow()
FindLastRow=Cells(2, 1).End(xlDown).Row
End Function[/tt]



combo
 
Upvote 0 Downvote
Hmm, I am doing something wrong...

I have amended FindLastRecord

Code: 
Private Function FindLastRow()

FindLastRow = Cells(2, 1).End(xlDown).Row

End Function

I have made the change to GetData which populates the records on the form:

Code: 
    If Not Cells(r, 1).EntireRow.Hidden Then
        Index.Text = Cells(r, 1)
        Category1.Text = Cells(r, 2)
        Contractref.Text = Cells(r, 3)
        Title.Text = Cells(r, 4)
        Description.Text = Cells(r, 5)
        Contracttype1.Text = Cells(r, 6)
        Status1.Text = Cells(r, 7)
        Targetdate = Cells(r, 8)
        Tenderlist = Cells(r, 9)
        Subcontract = Cells(r, 10)
        Value1 = Cells(r, 11)
    End If

Both of these are working. Thank you again.

What is not correct is the form still shows blank pages for those rows that are hidden and there is no data in the form... What I need is for the form to only show active records i.e. if 2 records out of 7 (e.g. record on row 2 and record on row 6) are not hidden, then the form will show 2 records, row 2 first and row 6 next.

Do I need something like this to reduce the recordset?

Code: 
    Dim cell As Range, rng As Range
    Dim rng1 As Range
    Set rng = ActiveSheet.AutoFilter.Range.Columns(1)
    Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
    
    On Error Resume Next
    
        Set rng1 = rng.SpecialCells(xlVisible)

'some way of attaching specialcells(xlVisible) to the procedure to populate the form...

If so, where would I use it in here?

Code: 
Private Sub GetData()

Dim r As Long
LastRow = FindLastRow

If IsNumeric(RowNumber.Text) Then
    r = CLng(RowNumber.Text)
    
Else
    ClearData
    
    MsgBox "Illegal row number"
    Exit Sub
    
End If

'Populate all the controls on the userform
    
    If Not Cells(r, 1).EntireRow.Hidden Then
        Index.Text = Cells(r, 1)
        Category1.Text = Cells(r, 2)
        Contractref.Text = Cells(r, 3)
        Title.Text = Cells(r, 4)
        Description.Text = Cells(r, 5)
        Contracttype1.Text = Cells(r, 6)
        Status1.Text = Cells(r, 7)
        Targetdate = Cells(r, 8)
        Tenderlist = Cells(r, 9)
        Subcontract = Cells(r, 10)
        Value1 = Cells(r, 11)
    End If
    
    DisableSave

End Sub
 
Upvote 0 Downvote
I'm assuming that you have r, which is the row number on the sheet and RowNumber, which is the row number on the form?

There is not a 1::1. I previously states, "Well if the row in the sheet is hidden, then don't increment the the row counter IN THE FORM."

Where do you incriment the row number in the form?
 
Upvote 0 Downvote
Ah! Yes, apologies, here is the code for the 4 controls on the form (First, Next, Previous, Last)

Code: 
Private Sub cmdLast_Click()
'Step back through the records

    LastRow = FindLastRow - 1
    RowNumber.Text = FormatNumber(LastRow, 0)

End Sub

Private Sub cmdNext_Click()
'step forward through the records

    Dim r As Long

If IsNumeric(RowNumber.Text) Then
    r = CLng(RowNumber.Text)
    
    r = r + 1
     RowNumber.Text = FormatNumber(r, 0)
    If r > 1 And r <= LastRow Then
        RowNumber.Text = FormatNumber(r, 0)
    End If
    
End If
End Sub
Private Sub cmdFirst_Click()
    
    RowNumber.Text = "2"

End Sub

Private Sub cmdPrevious_Click()
    Dim r As Long
    
    If IsNumeric(RowNumber.Text) Then
        r = CLng(RowNumber.Text)
        
        r = r - 1
         RowNumber.Text = FormatNumber(r, 0)
    End If
End Sub
 
Upvote 0 Downvote
Two remarks:
1) you have [tt]LastRow = FindLastRow - 1[/tt]. This is not the last row in your table,
2) your code can be applied to non-filtered excel table. Filter requires some additional testing when going to first/previous/next/last visible row. You could need additional variable for testing visibility of given row, if not, go to next/previous, test if it is within table range. Only when you find a rof that satisfied conditions, assign it to r and display it.

combo
 
Upvote 0 Downvote
I have been able to 'print' and accurate list of the cells that are visible in the sheet from a control click on the userform (I used a concatenation function to put them in a string so I could easily read them off)

Code: 
    Dim rng As Range
    Set rng = Range("A2", Range("A2").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)
    
    For Each cell In rng
        
    Next
    MsgBox "value " & Concat(rng)

Now that I have some code that accurately identifies the visible cells, I have been trying to use it to qualify visibility and passing that to the different procedures.

Finding the last visible row in the table...

Code: 
Private Function FindLastRow()
'Changed
    FindLastRow = Cells(2, 1).End(xlDown).Row
'to
    FindLastRow = Cells(2, 1).SpecialCells(xlCellTypeVisible).End(xlDown).Row

This seems to work. In the same control, I have added two additional msgboxes to output the values so I can verify they are identifying the correct information

Code: 
'Check that the filter has been clocked by FindLastRow...

    MsgBox "Number of visible rows " & FindLastRow - 1
    MsgBox "LastRow " & FindLastRow

Having applied a filer, this returns

Number of visible rows 5
LastRow 6
value 12345

Which is correct.

Where I am stuck is populating the userform with ONLY the filtered data. I am assuming it is GetData routine, where you have suggested using additional testing for visibility...

Code: 
Private Sub GetData()
'error handling required

Dim r As Long
lastrow = FindLastRow
'need some additional variable testing logic to find the visible cells

    Dim rng As Range
    Set rng = Range("A2", Range("A2").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)
    
    'then assign it to r and display it
    
    For Each cell In rng
        If IsNumeric(RowNumber.Text) Then
        r = CLng(RowNumber.Text)       
        Else
            ClearData
            MsgBox "Illegal row number"
            Exit Sub            
        End If
    Next
    
'Populate all the controls on the userform
    
    If Not Cells(r, 1).EntireRow.Hidden Then
        Index.Text = Cells(r, 1)
        Category1.Text = Cells(r, 2)
        Contractref.Text = Cells(r, 3)
        Title.Text = Cells(r, 4)
        Description.Text = Cells(r, 5)
        Contracttype1.Text = Cells(r, 6)
        Status1.Text = Cells(r, 7)
        Targetdate = Cells(r, 8)
        Tenderlist = Cells(r, 9)
        Subcontract = Cells(r, 10)
        Value1 = Cells(r, 11)
    End If

'error handling

End Sub

And it would be the same loop that I wrap around Next / Previous ?

Code: 
Private Sub cmdNext_Click()
'step forward through the records
    Dim r As Long

'need some additional variable testing logic to find the visible cells

    Dim rng As Range
    Set rng = Range("A2", Range("A2").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)
    
    'then assign it to r and display it
    
    For Each cell In rng
        If IsNumeric(RowNumber.Text) Then
            r = CLng(RowNumber.Text)
        
            r = r + 1
            RowNumber.Text = FormatNumber(r, 0)
        End If
    Next
End Sub
 
Upvote 0 Downvote
I wouldn't work with special cells. If you have no data or no match with filter criteria you will get the last row in the worksheet (BTW, .End(xlDown) jumps only to visible cells, so can be applied for non-filtered table (column, in fact)).
For me, you make things too complicated. Having data in table between second row and found last row of non-filtered data, you have the reference range [tt]Range(Cells(2,1),Cells(FindLastRow,1)[/tt]. You can loop through it either by row numbers [tt]For i=2 To FindLastRow[/tt] and testing visibility of [tt]Cells(i,1).EntireRow[/tt] or directly [tt]For Each c In Range(Cells(2,1),Cells(FindLastRow,1)[/tt] and testing visibility of [tt]c.EntireRow[/tt]. Maybe a helper dynamic array of row indexes visibility or only visible row indexes would be helpful.
I don't understand how you handle r variable. From some of your code it looks like it is a row from which you read data. But in:
[pre]For Each cell In rng
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
ClearData
MsgBox "Illegal row number"
Exit Sub
End If
Next[/pre]
something is missing.

combo
 
Upvote 0 Downvote
I am really sorry but I am totally lost after trying to figure this out all day...

If I understand you correctly, I need to make 'r' loop through an array rather than use RowNumber.Text in the GetData sub to call the record from the worksheet - this is why there are blank records in the userform as the data is suppressed, but the navigation is still showing all the rows in the worksheet.

I am able to debug.print only the visible row numbers using the code you suggested:
Code: 
       For Each c In Range(Cells(2, 1), Cells(FindLastRow, 1))
            If Not Cells(c, 1).EntireRow.Hidden Then
                Debug.Print c
            End If
        Next

I have tried to create an array - as you suggested, to enable me to provide the information for r to loop through.

I have been able to create an array, but not limit it to visible records - not sure how to.
Code: 
Sub ArrayPrint()
Dim LastRow
Dim c

LastRow = FindLastRow

       ' Declare a Variant variable called MyArray.
       Dim MyArray As Variant

       ' Declare looping variable.
       Dim Counter As Integer

       ' Redimension MyArray for elements as type Integer.
       ReDim MyArray(2 To LastRow) As Integer
       
       'test for visibility and loop to the end of the recordset...
            For Each c In Range(Cells(2, 1), Cells(FindLastRow, 1))
            If Not Cells(c, 1).EntireRow.Hidden Then
                For Counter = 2 To LastRow
                MyArray(Counter) = Counter
                debug.print Counter
                Next Counter
                Debug.Print c
            End If
        Next


End Sub

And I still cannot figure out how to make r in the GetData sub only loop through the visible records ARGH!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

huiettcm
  • Locked
  • Question
Excel UserForm Window 'Focus' Event Listener 1
Replies
14
Views
576
huiettcm
huiettcm
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng
Andrzejek
  • Locked
  • Question
Excel CopyFromRecordset limit 1
Replies
3
Views
535
combo
combo
MattGreer
  • Locked
  • Question
Excel - Copy Filtered Rows from a table to a new blank worksheet
Replies
7
Views
293
combo
combo
AncientTiger
  • Locked
  • Question
How to screenshot a userform and save the capture as JPG 1
Replies
7
Views
966
AncientTiger
AncientTiger

Part and Inventory Search

Sponsor

Back
Top