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

 

[Akuini]

Notes:
First of all, I swapped the name of Listbox1 & Listbox2 because it's rather confusing, so now Listbox1 is the first listbox (the left one).
I removed sheet BOOK and added some data in col G:I sheet KJV.

Here's how it works:

Combobox1:
to search verse (with fmMatchEntryComplete)

Textbox2 (verse):

• double click Textbox2 > userform3 shows up displaying selected verse + some verses before & after
• editing isn't allowed.

Textbox3 (Note):

• double click Textbox3 > userform4 shows up displaying the note
• editing is allowed.
• closing userform4 will sent the edited note to textbox3
• the note will be sent to the sheet when you hit Insert Note button OR move the focus out of textbox3 OR close Userform1.

Textbox4
you can type multiple keywords separated by a space then hit "SEARCH" button OR hit ENTER.

The file:

BIBLEAPPLATEST - try 5b

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

 

[Akuini]

Sorry, there's a bug, when I select all Books and hit Search, the code run into error.
Use this one instead:

BIBLEAPPLATEST - try 5c

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]

Sorry, there's a bug, when I select all Books and hit Search, the code run into error.
Use this one instead:

BIBLEAPPLATEST - try 5c

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

Akuini;
Outstanding job!. Does everything I asked for exactly the way I wanted it. Traditional Bible software apps do not have this level of viewing
ability as well as complete unlimited documentation on any verse. It is not yet a 100% complete Excel based self- contained Bible software
application. There are two more things that will make this a complete system:
1) The ability to view verses in multiple versions(translations). This would be a side-by-side view of several versions selected from a
listbox or combobox. The side-by-side view would be on another userform( I don't want the main userform to get too big or'
too cluttered. This KJV version was first published in 1611 in England by order of King James of Scotland. I need to find and download
the NIV
the NASB
the RSV
I haven't checked yet but these should be available on the web to download, so I;ll begin looking for them next week
2) Downloading 2-3 commentaries for a reference library. Commentaries are available as well. These would all be in separate
sheets in this one workbook.

I envy your advanced coding skills. I have been trying to go through every line you wrote to understand how the code is doing
what it does. Some statements are very easy to understand, some not so easy. Some portions are complicated.

Akuini - thanks again for all your help. I will stay in touch if I have any questions.

cr

 

[Akuini]

1) The ability to view verses in multiple versions(translations).

They are different versions but have the same Books & verse number, right?

2. If what we've got so far is on the right track, I'll add more comments to the code so you can understand it more easily.

3. I also suggest to add a feature, i.e. to search by note. You can choose it via option button, something like this:

so if you pick "Note" the code will search the keyword on col Note and show the result in Listbox2 whilst the verse text will be hidden (but the verse text is still shown in the textbox2)

 

[My Aswer Is This]

Wow. I'm so glad you have this nearly all worked out. Glad Akuini. Was able to work this all out for you.

 

[chazrab]

They are different versions but have the same Books & verse number, right?

2. If what we've got so far is on the right track, I'll add more comments to the code so you can understand it more easily.

3. I also suggest to add a feature, i.e. to search by note. You can choose it via option button, something like this:
View attachment 53147
so if you pick "Note" the code will search the keyword on col Note and show the result in Listbox2 whilst the verse text will be hidden (but the verse text is still shown in the textbox2)

2. Yes. All versions have exactly the same verse number in the same Book name, i.e., Matthew 24:15 in the KJV = Matthew 24:15 in the NIV, HSB, RSV, etc.
What's different are the wording translators chose to use throughout the centuries to describe or interpret what "they thought" the original intended meaning was(is).

With the Parallel View I was referring to, anyone can quickly see discrepancies in the wording. when comparing a side-by-side view. One changed
word in a verse of a version can change the entire meaning of the verse. To those who search for truth in the scripture, this can be an extremely valuable viewing tool. If you're interested in helping me further on this component of the application, I, as mentioned, need to download all of these versions and put each in a separate sheet.

Again, we're talking about maybe 4 new sheets each with about 31103 rows of data, each row being a verse. The KJV has 31103 verses from Gen. to the end of Rev.
3. Absolutely, I would like to be able to search by note. Using the option buttons would be an ideal way to do this, if you could do that.

Please see images below. Not to confuse things further, Listbox2 is blank when the userform is opened and your code clears it whenever a change is made in Listbox1. I want to be able to view the entire KJV in Listbox2 with spaces between rows of data when Userform1 opens.

I've done this with another KJV sheet on an earlier try shown below, copied it to your latest "try 5" and renamed it KJV2.

But that won't work because then i'd be working with two separate sets of KJV data. Want to perform viewing, search and note taking
all on the same KJV version.

The way I see to do this is simply have the userform initially open to viewing Listbox2 with spaces between data, not blank. To me adding blank rows between data
shouldn't destroy your code search and edit operations. When a search is made selecting a range and typing in a value using Listbox1, the change event in
Listbox1 clears Listbox2 and the operation is exactly done the way you have it in try 5. Your code:

Private Sub ListBox1_Change()
    txB = ""
    TextBox4 = ""
    xList = Empty
    ListBox2.Clear
    Label1.Caption = ""
End Sub

A button "VIEW TEXT" would return the spaced view of KJV in Listbox2 after the search is completed, and the notes Textbox is edited.
This is just a way I thought would be easy to do without changing any or much of what you already have.
Summarizing, I'm just wanting to add spaced row viewing capability of the KJV sheet in Listbox2. That's it.

I really do like the option button Note searching idea. I definitely want to have that .

Once again, Akuini, and as always, thank you so much for your help. This is becoming a real Christmas present for me.

cr

 

[chazrab]

Replied. As above.

 

[Akuini]

Please see images below.

Where is the image?

The way I see to do this is simply have the userform initially open to viewing Listbox2 with spaces between data, not blank.

I don't understand "with spaces between data, not blank."
In listbox2 there are blank rows between each data, but you want a space not blank row? what space?

 

[chazrab]

Where is the image?

I don't understand "with spaces between data, not blank."
In listbox2 there are blank rows between each data, but you want a space not blank row? what space?

Sorry - poor choice of terms. Wrote and sent this at 2 AM last night and was not coherent. I just meant a blank row between verse data from Gen to Rev in the KJV sheet. Below is an image of how Listbox2 on Userform1 should look when the form is opened from the USERFORM button on the KJV sheet. You can see the
Rowsource property is set to =KJV2!A2:C31103. In your original KJV sheet below there are no blank rows and the Rowsource property is left blank.

I tried this is with a copy of the KJV sheet(KJV2) in the image, with blank rows inserted between the verse data rows.
When i ran the form, it spaced the Combobox1 items as well, and threw off a few other things. So, inserting blank rows in the entire KJV sheet affected other things in how the code performed as well.

Thanks again for helping me to work through this. - Just want to view Listbox2 rows with blank rows(or spaces, meaning the same thing) when
Userform1 opens. Everything else on Userform1 that the code runs is absolutely perfect and I'm looking forward to see how the option button
selection for Notes you suggested works when I give it a test run. The images should be self-explanatory.
cr

 

[chazrab]

Hi Akuini - Been playing around and experimenting with latest try 5 userform1.
Another cool feature would be using a slider control to adjust the font size of specified textboxes. Excel has the VBA ability to do that right now
with this code:

Private Sub cmdFontSize_Click()
Me.ListBox1.Font.Size = 10
Me.TextBox2.Font.Size = 10
Me.TextBox3.Font.Size = 10
End Sub

which is great but only offers a fixed font size adjustment of textbox text. The yellow button in the image below allows resizing of the
text to 10. I added this code to maximize the userform to full screen. When that happens, the fonts are also enlarged, not just the
userform, and the fonts become way too big:

Private Sub cmdMaxVIew_Click()
With Application
.WindowState = xlMaximized
Zoom = Int(.Width / Me.Width * 100)
Width = .Width
Height = .Height
End With
End Sub

The blue Maximize button in the second image below has this code in the click event.

We've all seen slider bars in other apps and being able to dynamically resize the font of any textbox in run mode
would be a great benefit. Right now, the only solution I see would be a drop- down combo with several macros
with the above code but different sizes, which is way less elegant than a horizontal sliding bar. After the other things,
let me know if that's possible.

When all these features are completed, I want to compose a library of code procedures I can refer to for other application
development that does all these things. This has been a good learning exercise for me, and hopefully others who
are following this thread posts.

Once again, thanks for all your help.

cr