Displaying Autofiltered results in a Llstbox

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
877
Office Version
  1. 365
Platform
  1. Windows
Sheet1 is autofiltered to produce all people with grade A. I don't want a copy of the filtered result.
I would like the listbox to display the visible rows, not a copy of the visible rows. That way, I can make changes
directly to the results in the main sheet and write them back to the appropriate sheet row and column.
I tried this code but can't figure out how to display only the visible rows in the listbox. This doesn't seem to work:'
Code:
Private Sub cmdSearchForValue_Click()
Dim ws As Worksheet
Dim s As String
Set ws = Worksheets("Sheet1")
ws.Activate
    With Sheets("Sheet1").Range("A1")
     .AutoFilter Field:=2, Criteria1:="A" 
     .SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1") ; originally I was copying to another(REPORT) sheet.  
     .AutoFilter
    End With
Uerform1.Show

I was copying to another sheet originally. I don't t want a copy. I want to use the same filtered visible cells
to add and edit data.
Please see below.
Can someone please guide me on what I am doing wrong?
Thanks for anyone's help.
cr
FILTERED DATA.jpg
FILTERED DATA.jpg
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
So what is wrong with what your showing in your Userform Listbox. ?

Or is this what you want to show.
 
Upvote 0
So what is wrong with what your showing in your Userform Listbox. ?

Or is this what you want to show.
Hi MyAnswer. The Userform with Listbox1 is the result I want to show. As you can see, it contains the filtered visible cells of Ben, bill, Larry and Dave
all getting A's. This is the filtered data, filtered in place on Sheet1, not a copy.
 
Upvote 0
You Know if you load these values into A Userform ListBox
It may be difficult modifying the values in the listbox.
Have you ever done this before?
You said:
That way, I can make changes
 
Upvote 0
You Know if you load these values into A Userform ListBox
It may be difficult modifying the values in the listbox.
Have you ever done this before?
You said:
That way, I can make changes
Yes I have. The sample I sent you was generic. The complete userform shows Textbox1 which is just an expanded view of the verse at the top.
Textbox2 is the real chocolte chip cookie. By that I mean, I can add any notes I want to that particular verse because COL D. So, when changes are
made in textbox2 on the userform(Userform3), the changes are written back to the sheet(GENESIS2) for that particular verse. The code to
do this is simple enough:
Code:
Private Sub ListBox1_Change()
UserForm3.TextBox1 = UserForm3.ListBox1.List(UserForm3.ListBox1.ListIndex, 2) _

UserForm3.TextBox2 = UserForm3.ListBox1.List(UserForm3.ListBox1.ListIndex, 3) _

UserForm3.TextBox8 = UserForm3.ListBox1.List(UserForm3.ListBox1.ListIndex, 0)

UserForm3.TextBox6 = UserForm3.ListBox1.List(UserForm3.ListBox1.ListIndex, 1)

End Sub
As you know, the umbers to the right of each .Listindex above are the column numbers, starting with 0 because its Listbox code.

All that said, this is just performing this operation with a static situation - that is, the Sheet Rowsource is the sheet written back to.
However, in a search the traditional method of displaying a filtered listbox does not allow writing any new notes back to the original sheet
because I'm working with a COPY at this point - not the underlying original sheet(GENESIS2) which it needs to be.

That's why I asked for your and anyone's help in displaying the visible results IN PLACE of an autofilter - not copying it to another sheet.
That way, the main orginal rowsource of the main underlying sheet can be correctly written back to

Tried to make this as simple as possible. This is a copying issue with a Listbox results display. A copy won't write the Notes Textbox2 data
back to the original Sheet row and column.
MAINFORM WITH NOTES TEXTBOX .jpg

To me, it should be possible to display filtered data of the original sheet in a listbox without a pseudo copy. Not to overtalk this, but the poiint
is to stay working with and performing writeback operations to the original sheet.
Thanks for all your help. At least you're trying to help and I really appreciate it. cr
 

Attachments

  • MAINFORM SHOWING ADDED NOTES.jpg
    MAINFORM SHOWING ADDED NOTES.jpg
    152.9 KB · Views: 12
Upvote 0
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
 
Upvote 0
@chazrab
My suggestion:
1. Do not use rowsource to populate the listbox, use an array instead.
2. Apply the "filter" only on the listbox, not on sheet's data.
3. You already has a column that has unique value i.e col A, use Find method to get the correct row when you change an entry.

Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
Also explain in more detail about what you're trying to do.
 
Upvote 0
@chazrab
My suggestion:
1. Do not use rowsource to populate the listbox, use an array instead.
2. Apply the "filter" only on the listbox, not on sheet's data.
3. You already has a column that has unique value i.e col A, use Find method to get the correct row when you change an entry.

Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
Also explain in more detail about what you're trying to do.
I copied the Workbook to MyDrive in Google Drive. I can't figure out how to link or share this file or upload the workbook ro you. from there. There's a link icon above but all that does is tell you to insert a link. It doesn't give a Browse button option to go out and upload a file. Thank for helping. cr
 
Upvote 0
On your google drive:
Right click the file you want to share > Click Get Link > Click Copy Link > now you get the link, just paste the link here.
 
Upvote 0

Well, how easy was that! = Here is detail. The workbook I sent has 4 visible tabs:
KJV - This is the entire King James version. 31103 rows. Each row is a verse.
GENESIS2 - The Book of Genesis sheet. 3066 rows(verses). Blank rows are numbered.
DANIEL - The Book of Daniel (714 rows(verses). Blank rows are numbered.
- This is the sheet that this code copies the autofiltered results of a search for
any word:
Code:
Private Sub cmdSearchForValue_Click()
Dim ws As Worksheet
Dim s As String
s = Me.TextBox3.Value
Set ws = Worksheets("KJV") 'searches main Bible
ws.Activate
    With Sheets("KJV").Range("A1")
     .AutoFilter Field:=3, Criteria1:="*" & s & "*" 'nxt code line to go to verse
     .SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1")
     .AutoFilter
    End With
Me.TextBox6.Value = Sheets("REPORT").Cells(1, 6).Value
SEARCHRESULTS.Show
This code finds any word I want and it worKs every time, although it takes a few seconds to
search down 31000 rows.
The sheet is the KJVMASTER sheet. This is where searches are performed and displayed in the MAINUSERFORM below.
The Large box to the left on the userform is Listbox1. You see spaces between the rows when it is displayed on the form when the form is active. i just created as many blank rows as verses at the end of the GENESSI2 and DANIEL sheets and did an A-Z sort to insert a blank space between each data row. It makes for much easier reading of a Listbox on a userform and also has its own unique row number.

Then you see Textbox1 (Full verse view) on the top right and Textbox2(Notes) on the lower right.
This Textbox2(Notes) is the jewel in the crown. Meaning to say, that it is the Notes column in
the KJVMASTER below. It is column D. Here's what happens when any word is entered in the
search field:
KJVMASTERSHEET.jpg
 

Attachments

  • MAIN USERFORM.jpg
    MAIN USERFORM.jpg
    126.6 KB · Views: 11
  • A WORD IS ENTERED.jpg
    A WORD IS ENTERED.jpg
    30.8 KB · Views: 11
  • AUTOFILTERRESULT.jpg
    AUTOFILTERRESULT.jpg
    198.9 KB · Views: 11
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top