Displaying Autofiltered results in a Llstbox

[chazrab]

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:'

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

 

[chazrab]

Ok, this is what I've come up so far.
How it works:

• In the combobox you can select ALL or a specific Book.
• In the textbox you can type multiple keywords separated by a space.
• The search ignores the keywords order, so keywords "ma la" will match "Maryland" and "Alabama".
• Type anything in the Note (textbox3), then hit "INSERT NOTE" button, the note will be inserted into col Note in sheet KJV.

Note:

• In sheet KJV, I removed col C & added col D (BOOK), it's to define the range of each book in the code. Do not sort data in sheet KJV, because it will make the code define the range incorrectly.
• I assume your data in sheet KJV (except col Note) won't change.
• If you want a blank row between every records in the listbox, I can amend the code to do that.

Example:

BIBLEAPPLATEST - try 2

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Hi Akuini: I just opened this this AM 6:52 AM 12/7/21 here, so I'm slowly catching up with your posts above. I followed your suggestions on
full book names. I also included the emphasized row of Genesis 1:5 where a day and time is defined as I put in the notes. This Notes column, D for now,
is the heart of the application as it will allow any user to make any notes on any verse they wish, and they are written back to that row the verse is on in col D.
The other "heart" of this app is the search feature. As mentioned, I prefer an autofilter search over FiND. Ii a user wants to search for any word in the
entire Bible, he/she can. If for a specific range, that can be done also. I thought about this. Specific search ranges could possibly be a set of separate sheets
such as Genesis-Deuteronomy(1 sht) The Major Prophets(another sht), Matthew-John(another sht), etc. The only other way I can think of to use search ranges is with VBA code and an input box or custom mini userform which asks the user to enter a search range from a list.

I do want spaces between each verse. For me, it makes any Listbox on a userorm much easier to read. Last night I tried to insert blank rows in the entire KJV sheet. That sheet, the main entire Bible, has 31103 rows, which are the verses. It didn't work as I expected either doing manually or with VBA code. You will see
the Row No. col I added on the views I've sent. These are the same row numbers in Excel. BTW, I don't need col B on the KJV sht. I just concantenated col A and col B orginally to give the full verse reference at the end of each verse in col C. Thanks again for all your help in this. cr


These are just ideas I have at this point. I've wanted to do this for years, and with your help it's becoming a reality. Please see the images below. I will reread and download the file today. I have a full schedule of teaching and other things but will jump on this after 2 PM CST US time. Once again, thank you so much for helping me work through this. cr

 

[Akuini]

As mentioned, I prefer an autofilter search over FiND. Ii a user wants to search for any word in the
entire Bible, he/she can. If for a specific range, that can be done also.

Did you try the example I uploaded?
To search in the entire Bible just select "ALL" & type the keyword in the textbox, like this:

 

[chazrab]

Did you try the example I uploaded?
To search in the entire Bible just select "ALL" & type the keyword in the textbox, like this:
View attachment 52838

Just downloaded the file. Works perfectly. Outstanding job! Is there a relatively easy way to insert a blank row between each data row
all the way down to 31103 rows either with a fill -sort manual operation or with vba code ?

 

[Akuini]

Try this one:
How it works:

• In the first Listbox you can select multiple Books.
• To select or deselect all, hit "SELECT/DESELECT ALL" button.
• In the textbox you can type multiple keywords separated by a space then hit "SEARCH" button.
• The search ignores the keywords order, so keywords "ma la" will match "Maryland" and "Alabama".
• Type anything in the Note (textbox3), then hit "INSERT NOTE" button, the note will be inserted into col Note in sheet KJV.
• The code inserts a blank row between every records in the listbox.

Note:
The code is a bit complex, so just play with the Userform, see if there are any bugs or lag.
Also, just let me know if there are any feature or behavior in the userform that needs adjustment.

Image:

Example:

BIBLEAPPLATEST - try 4

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[chazrab]

Try this one:
How it works:

• In the first Listbox you can select multiple Books.
• To select or deselect all, hit "SELECT/DESELECT ALL" button.
• In the textbox you can type multiple keywords separated by a space then hit "SEARCH" button.
• The search ignores the keywords order, so keywords "ma la" will match "Maryland" and "Alabama".
• Type anything in the Note (textbox3), then hit "INSERT NOTE" button, the note will be inserted into col Note in sheet KJV.
• The code inserts a blank row between every records in the listbox.

Note:
The code is a bit complex, so just play with the Userform, see if there are any bugs or lag.
Also, just let me know if there are any feature or behavior in the userform that needs adjustment.

Image:
View attachment 52893
Example:

BIBLEAPPLATEST - try 4

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Hi Akuini - will download try 4. As a side note, I looked at all your code. A future challenge for me is to learn how to do
all this complex coding myself to get other things done and other apps devloped. I hate having to depend on others with more experience than me for help, but I can say that I really appreciate it. I have to do some detailed study line by line to understand completely how this code performs these elegant search operations will all these For loops.

An additional feature I would like is when you type in say, 'Daniel' or any book in Combobox1, the MatchEntryComplete Property takes it to 'Daniel 1:1. The book of Daniel has 12 chapters and 714 verses. If I type in 'Daniel 7:24 for example, the ideal combobox solution would take me or any user directly to THAT verse
without having to go through manually scrolling down the combobox after it reaches Daniel 1:1 to that verse once MatchEntryComplete(or your code) takes me to the first instance of 'Daniel', in this case 'Daniel 1:1'. Also, Listbox1 displays both the verse and the full line of text. I only would like to see the verse in Listbox1 and when I click on that line, Textbox1 on the right displays the full verse. Once again, thanks for all your help. cr.

 

[chazrab]

Hi again, Akuini - I greatly appreciate your interest and help in this. I'd like your help on this. here it is:
Is there a way to be able to pull up the surrounding verses in a search result. For example, if the word "eternal" is typed in the search field and the check box for the search range is only Isaiah, only 1 result comes up, Isaiah 60:15. the code should pick up the verses above and below Is. 60:15.

The ideal would be yet another textbox on the mainform showing in this case, Isaiah 60:12-21. Now the userform is getting bigger and bigger with an addional textbox.

What I like is to have smaller "popup" userforms of the surrounding verses linked back to the search verse result on the main form. This is all great in theory, but if the ShowModal property is set to False, jumping back and forth between userforms sends the other forms behind each other which is not cool. Code would, if possible, allow userforms to stay on top of each other and allow making changes to any text on any of the userforms. To
To save space. this would/could entail a new userform "popup" to display Isaiah 60: 12,13,14, 16, 17, 18. 19. 20 and 21.

Added flexibility would allow the user to have a choice of a verse range surrounding this searched verse result.

A side note : understanding the meaning of a term or word in Hebrew or Greek requires looking at the "surrounding text", not just one isolated word or phrase, in order to try to understand the intended meaning. That's why I'd like to add this flexibility. No other standalone Bible software app does that, to my knowledge.

This is a lot of information. - and a lot of complicated code. If I can get the views I want from the verses I search and add notes based on other verses, a picture becomes evident on how the Bible i connected together in events past, present and future. Until now, I did not realize how much power and flexibility Excel has
as a tool to accomplish this task.

Thanks once again for all your help in helping me.

cr
Kingwood, Texas

 

[Akuini]

What I like is to have smaller "popup" userforms of the surrounding verses linked back to the search verse result on the main form. This is all great in theory, but if the ShowModal property is set to False, jumping back and forth between userforms sends the other forms behind each other which is not cool. Code would, if possible, allow userforms to stay on top of each other and allow making changes to any text on any of the userforms

Instead of multiple userform, how about using a Multi-Page UserForm, like this:

Multi-Page UserForm - TeachExcel.com

You can have multiple tabs of data on a single UserForm and this allows you to effectively have multiple pages of data inside the form Here you ll learn how to create this multi page setup how to add ...

www.teachexcel.com

 

[chazrab]

Instead of multiple userform, how about using a Multi-Page UserForm, like this:

Multi-Page UserForm - TeachExcel.com

You can have multiple tabs of data on a single UserForm and this allows you to effectively have multiple pages of data inside the form Here you ll learn how to create this multi page setup how to add ...

www.teachexcel.com

That would work great. cr

 

[chazrab]

I had second thoughts about a multipage control. The big disadvantage in this application is that users have to constantly switch
back and forth to view different pages when trying to compare text notes. The advantage of a popup userform is that it can be viewed directly
along with the main page textbox data. - a separate userform can be viewed at the same time if the Modal properties and code is set up correctly.

With a multipage control, a user has to switch back and forth to view different textboxes on each page. Only one small userform with a textbox
and vertical scrollbar is necessary. The form would have to be Modal unless there is a way to code it so that changes can be made to the Notes
texbox on the main form as well as this small userform, but still keeping it on top.

Thank you again, very much for all your help.
cr

 

[My Aswer Is This]

I have been watching this posting. Multipages do not have to be very small.
I use Multipages all the time.