Filter macro / code builder help?

[StargateFan]

Does someone know of VBA code that will bring up a "find" type of
dialogue box that when the user types in the data, the data is
searched for and if found, the result gets filtered so that only the
row containing that data shows up? I'm hoping that there will be an
error code, too, that will advise user if data not found.

And lastly, was wondering where one can find these types of macro
codes oneself? In WordPerfect we have a command builder type of box
that is so easy to use. I build my own code there and only go to the
ng for help when I run into a snag I don't know how to fix myself,
which doesn't happen often as the code builder help works very well.
I'm hoping the VBA editor has something like this?

Now that I've crossed into VBA more with everyone's kind help, I'm
starting to get a bit of confidence and would like to learn how to do
the coding myself. But I don't know where to start as money is an
issue. I'm sure there are great books out there for sale but it's not
possible right now to buy. The library has books, of course, but
they're all quite old and we can only keep them for a short period of
time, of course.

Thanks!

 

[Earl Kiosterud]

StargateFan,

If you want to look for an entry in a cell (not part of the cell),
Autofilter may be what you want. If you're looking for part of a cell, it's
different.

Are you looking through one column? An entire table? Will there be only
one search hit for your search argument, or more? Say more.

 

[StargateFan]

StargateFan,

If you want to look for an entry in a cell (not part of the cell),
Autofilter may be what you want. If you're looking for part of a cell, it's
different.

Are you looking through one column? An entire table? Will there be only
one search hit for your search argument, or more? Say more.

I didn't think of mentioning that, so sorry.

Strictly for this find function, it'll be one column for the main data
ID that the user will be looking through.

I have over a hundred entries at this point and it's time-consuming
enough to manually filter the column as it stands. By the time I
finish imputting the older data, there will be several hundred entries
to filter so hoping to automate this for the user.

Thanks.

 

[Earl Kiosterud]

StargateFan,

You might be able to use Autofilter, though this can result in a long list
to select from. It only shows 1000 entries.

Are you willing to paste some macro code in? I think I have something you
can use.

Make a textbox from the control toolbox. Put it at the top of the
worksheet. While you're still in design mode, double-click the textbox
(should put you in the sheet module in the VBE) and paste in this code (from
here). Watch for line breaks from the post.

Private Sub TextBox1_Change()
Dim FoundCell As Range
Static FoundRow As Long
' remove yellow from prior marked row:
If Not FoundRow = 0 Then Cells(FoundRow, 1).EntireRow.Interior.ColorIndex =
xlNone
Set FoundCell = Range("A:A").Find(TextBox1.Value)
If Not FoundCell Is Nothing Then ' did we find one?
FoundRow = FoundCell.Row ' save the row for unyellowing
FoundCell.Select ' force scroll to this row
FoundCell.EntireRow.Interior.ColorIndex = 36 ' light yellow
End If
TextBox1.Activate ' go back to text box.
End Sub

Freeze panes to keep the text box onscreen at the top when it scrolls.
Switch off design mode to use it.

This isn't the one that I've successfully used, but let's see if it's what
you want. It marks the row it's found in yellow as you type into the text
box. When you've reached the one you want, press Esc, and the row will be
selected. We can have it hide the other rows.

 

[StargateFanFromWork]

StargateFan,

You might be able to use Autofilter, though this can result in a long list
to select from. It only shows 1000 entries.

I've of course been using Autofilter, something I tend to use quite often.
But you're right, it's a long list of entries to choose from. I imagine
that the number of entries won't be so bad as I'm thinking that after a
certain number of dockets are closed, I should probably just make a copy of
the sheet and start a new series of numbers. Fortunately for us, this file
is just a tracking mechanism and we only need a hard copy once the
ministerial docket has been closed.

Are you willing to paste some macro code in? I think I have something you
can use.

I sure would be willing but I think I'll hold off for a few days till I have

Make a textbox from the control toolbox. Put it at the top of the
worksheet. While you're still in design mode, double-click the textbox
(should put you in the sheet module in the VBE) and paste in this code (from
here). Watch for line breaks from the post.

Do you mean to watch out for word wrap, perhaps? (If so, yes, I've run into
that type of trouble before where the ng post wraps to the next line. It

Private Sub TextBox1_Change()
Dim FoundCell As Range
Static FoundRow As Long
' remove yellow from prior marked row:
If Not FoundRow = 0 Then Cells(FoundRow, 1).EntireRow.Interior.ColorIndex =
xlNone
Set FoundCell = Range("A:A").Find(TextBox1.Value)
If Not FoundCell Is Nothing Then ' did we find one?
FoundRow = FoundCell.Row ' save the row for unyellowing
FoundCell.Select ' force scroll to this row
FoundCell.EntireRow.Interior.ColorIndex = 36 ' light yellow
End If
TextBox1.Activate ' go back to text box.
End Sub

Freeze panes to keep the text box onscreen at the top when it scrolls.
Switch off design mode to use it.

This isn't the one that I've successfully used, but let's see if it's what
you want. It marks the row it's found in yellow as you type into the text
box. When you've reached the one you want, press Esc, and the row will be
selected. We can have it hide the other rows.

Okay, I think I got all this. But a last question, do you mean a TEXT BOX
from the control toolbox? I was just curious re this. I won't use any sort
of button, I guess?

Earl Kiosterud
mvpearl omitthisword at verizon period net

[snip]

 

[Earl Kiosterud]

Star,

Yes. Textbox.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

StargateFan,

You might be able to use Autofilter, though this can result in a long list
to select from. It only shows 1000 entries.

I've of course been using Autofilter, something I tend to use quite often.
But you're right, it's a long list of entries to choose from. I imagine
that the number of entries won't be so bad as I'm thinking that after a
certain number of dockets are closed, I should probably just make a copy of
the sheet and start a new series of numbers. Fortunately for us, this file
is just a tracking mechanism and we only need a hard copy once the
ministerial docket has been closed.

Are you willing to paste some macro code in? I think I have something you
can use.

I sure would be willing but I think I'll hold off for a few days till I have
a free moment to give this a try; a moment where I won't be interrupted.

Make a textbox from the control toolbox. Put it at the top of the
worksheet. While you're still in design mode, double-click the textbox
(should put you in the sheet module in the VBE) and paste in this code (from
here). Watch for line breaks from the post.

Do you mean to watch out for word wrap, perhaps? (If so, yes, I've run into
that type of trouble before where the ng post wraps to the next line. It

Private Sub TextBox1_Change()
Dim FoundCell As Range
Static FoundRow As Long
' remove yellow from prior marked row:
If Not FoundRow = 0 Then Cells(FoundRow,

1).EntireRow.Interior.ColorIndex

=
xlNone
Set FoundCell = Range("A:A").Find(TextBox1.Value)
If Not FoundCell Is Nothing Then ' did we find one?
FoundRow = FoundCell.Row ' save the row for unyellowing
FoundCell.Select ' force scroll to this row
FoundCell.EntireRow.Interior.ColorIndex = 36 ' light yellow
End If
TextBox1.Activate ' go back to text box.
End Sub

Freeze panes to keep the text box onscreen at the top when it scrolls.
Switch off design mode to use it.

This isn't the one that I've successfully used, but let's see if it's what
you want. It marks the row it's found in yellow as you type into the text
box. When you've reached the one you want, press Esc, and the row will be
selected. We can have it hide the other rows.

Okay, I think I got all this. But a last question, do you mean a TEXT BOX
from the control toolbox? I was just curious re this. I won't use any sort
of button, I guess?

Earl Kiosterud
mvpearl omitthisword at verizon period net

[snip]