advanced filter, goto next line(record)

[Pierre]

Hi experts,

I have a databasesheet called "datadga"

I have a userform with a combobox with unique values from column "W" in the
"datadga" sheet
When a user selects a value from the combobox and klick on a button called
"filter" the "datadga" sheet is filtered on the value in the combobox.

Next, I want all textboxes filled with items from the first row in the
filtered list.
Problem 1:
I can't manage to do so because i keep getting the first row of the entire
database and not the filtered one !
Any suggestions ont this problem would be most welcom !

Problem 2:
I have two extra buttons called "next" and "previous"
clicking on these buttons should result in going to the next row in the
filtered database and displaying all data in the textboxes on the userform.
Here i have the same problem, i can't manage to do so because i keep getting
the first row of the entire database and not the filtered one !

Any help would be greatly appreciated !
Pierre

 

[Incidental]

Hi Pierre

the code below should do what you want, i set up a userform with 3
textboxes a combobox and three buttons (1 to filter and a Next and
Previous button) and used this code to filter data held in the range
A1:C20 and populate the textboxes.

Option Explicit
Dim MyRng As Range

Private Sub CmdFilter()

With Sheets("DataDGA")

Range("A1:C20").Select

Selection.AutoFilter Field:=1, Criteria1:=ComboBox1.Value

Set MyRng = Cells.SpecialCells(xlCellTypeVisible).Cells(1)

Do

Set MyRng = MyRng.Offset(1, 0)

Loop While MyRng.EntireRow.Hidden = True

Call CellsToTextBoxes

End With

End Sub

Private Sub CmdPrev_Click()

Do

Set MyRng = MyRng.Offset(-1, 0)

Loop While MyRng.EntireRow.Hidden = True

Call CellsToTextBoxes

End Sub

Private Sub CmdNext_Click()

Do

Set MyRng = MyRng.Offset(1, 0)

Loop While MyRng.EntireRow.Hidden = True

Call CellsToTextBoxes

End Sub

Sub CellsToTextBoxes()

TextBox1.Value = MyRng.Value

TextBox2.Value = MyRng.Offset(0, 1).Value

TextBox3.Value = MyRng.Offset(0, 1).Value

End Sub

Hope this helps you out

Steven

 

[Pierre]

Hi Steven,

Thanks for your help.
Unfortunately, i do not understand it completely.

Can you explain what i should do at the statement;

Call CellsToTextBoxes

How should i replace that with a code to fill the textboxes ?

I now have the following code to filter and then it counts the number of
lines filtered

Private Sub but_filter_Click()
'FILTER ON
Dim row_count As Double
Dim matched_criteria As Double
Dim check_row As Double
Dim datateller As Double
Columns("w:w").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=zoek_acc.Value
Set filterrange = Cells.SpecialCells(xlCellTypeVisible).Cells(1) 'your
suggestion !
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.Count - 1 ' Count the rows and subtract
the header.
matched_criteria = 0 ' Set variable to
zero
check_row = 0 ' Set variable to zero.
While Not IsEmpty(ActiveCell) ' Check to see if row height is
zero.
ActiveCell.Offset(1, 0).Select
If ActiveCell.RowHeight = 0 Then
check_row = check_row + 1
Else
matched_criteria = matched_criteria + 1
End If
Wend

Next i have the following code for the button "previous" (which does not
work)

Private Sub but_vorige_Click()
Do
Set filterrange = filterrange.Offset(-1, 0)
Loop While filterrange.EntireRow.Hidden = True
Call CellsToTextBoxes
End Sub

This gives an 1004 errormessage on: Loop While filterrange.EntireRow.Hidden
= True

Any suggestions on this?
Thanks,
Pierre

 

[Incidental]

Hi Pierre

I think the problem is coming from where you are declaring your
variables, I always explicitly declare my variables using Option
Explicit which I would recommend for all projects as this will allow
you use your variables in any sub in the module i.e. if you explicitly
declare "Dim FilterRange as Range" you will be able to use it in the
subs for the filter as well as the that of the next and previous
buttons. Also explicitly declaring your variables means that if you
have typed a variable wrong it will be made known to you when you run
the code, which can save hours of debugging time!!!

Just as a point I was wondering why you are using double for your
numerical variables as they will always be a whole number?? A double
is core commonly used for floating point (decimal) numbers, I would
use an integer for these myself.

Next is the "Call CellsToTextBoxes" line this refers to the sub called
CellsToTextBoxes which is being called, I would include this as it
means you will only have to write the code once to put the textbox
values into the cells and you can then call it from your next and prev
buttons which will reduce the size of the code and of course save you
having to type it.

I hope this helps you out but if you have any more questions just let
me know i will try to help.

Steve

 

[Pierre]

Hi Steve,

As you can see, iám a novice in vba....

I now have all variables declared right !(no doubles but integers)
And your code works fine, just one problem;

I cannot fill a combobox with the value from a cell on the "datadga"sheet.

I have the following code;
If Not IsEmpty(filterrange.Offset(0, 3).Value) Then
cbo_mv = filterrange.Offset(0, 3).Value
End If
This gives an error message 380, somthing like cannot use the .value thing

any suggestions on this ?

By the way, i use the if then statement because the listitems of the
combobox do not nessesarily have to be the same as the cell on the datadga
sheet

Can you please help me once more ?
Thanks,
Pierre

 

[Incidental]

Hi Pierre

I'm glad the code is working for you, you may want to search the VBA
help for "Data Type Summary" which should give you a good idea of how
the data types work for instance an integer variable will take up 2
bytes in memory and can be any number between -32,768 to 32,767.

As for the other code with the combobox i checked it and it would seem
that it works fine if the style of the Combobox is set to
FmStyleDropDownCombo as this allows you give a value that is not held
within the combobox list but if you set the style to
FmStyleDropDownList you will get a 380 error as the value is not in
the list.

I hope this helps sort the problem

Steve

 

[Pierre]

Hi Steve,

This helps a lot.
However, there is one last problems;

1.
If i filter the data ican hit the button "next" or "previous". So far so
good.
However, i can keep on hitting these buttons and that should not be
possible.
Hitting the button "previous" should stop when the first row of the
selection is reached.
Hitting the button "next" should stop when the last row of the selection is
reached.

2. I would like to know the number of rows it finds when a filter is set.

I would greatly appreciate your input.
Thanks,
Pierre