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]

Hi Akuini
I have part of the entire text of the NIV version in COL K of sheet KJV(rather than putting each version on its own new sheet).,
and added new Textbox5 below Textbox2 in Userform1, the main userform, to show each verse the NIV version of each verse in Listbo2 as it is clicked. But the code gives me an error message. Isn't col K 10 starting with 0 in col A, ? Can't figure out why it's not displaying verse ins in col K
Can you help me figure this out? I have 5,633 of 31103 rows of the NIV already copied in col K on the same exact rows as the verse in col B of sheet KJV.

Thanks for all your help.
cr

Loading Google Sheets

 

[chazrab]

message as above. Images and file attached.
cr

 

[Akuini]

1. You need only a single listbox for the menu bar. You can set it up ilke this:

Private Sub UserForm_Initialize()
With ListBox3
    .Visible = False
    .IntegralHeight = True
    .Width = 180
    .Font.Size = 11
    .Font = "Calibri"
End With
End Sub

Private Sub UserForm_Click()
ListBox3.Visible = False

End Sub

Sub set_menu_listbox(p As Long, nm As String)
Dim n As Long
With ListBox3
    .Visible = True
    .Top = 19
     n = Sheets("Sheet1").Cells(100, p).End(xlUp).Row
    .List = Sheets("Sheet1").Cells(2, p).Resize(n).Value
    .Height = .ListCount * (.Font.Size + 3)
    .Left = Me.Controls(nm).Left
End With

End Sub

Private Sub Label1_Click()
Call set_menu_listbox(2, "Label1")  'col 2 in sheet1
End Sub

Private Sub Label2_Click()
Call set_menu_listbox(3, "Label2")  'col 3 in sheet1
End Sub

And to run specific procedure when clicking a listbox item:
I set the first 2 items to call a procedure:

Private Sub ListBox3_Click()
'run specific procedure when clicking a listbox item
Select Case ListBox3.Value
Case "Using This Application"
    Call try1
Case "Commentaries List"
    Call try2
'... add the other items
End Select
End Sub


Sub try1()
MsgBox "Try1"
End Sub

Sub try2()
MsgBox "Try2"
End Sub

Example:
Workbook

2. Listbox2 is populated only with col A:D, so to get value in col K you can use Find method like this:

Private Sub ListBox2_Click()
Dim n As Long
Dim c As Range

    n = ListBox2.ListIndex
    TextBox1.Value = ListBox2.List(n, 0)
    TextBox2.Value = ListBox2.List(n, 1)
    TextBox3.Value = ListBox2.List(n, 2)

    Set c = wKJV.Range("A:A").Find(What:=TextBox1.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        TextBox5.Value = c.Offset(, 10).Value  'get col K
    End If

End Sub

 

[chazrab]

Hi Akuini - I finally completed adding all the other versions in columns on the same sheet(KJV).
They are
the NIV version = COL K2:K31103,
the NASB version = COL L2:L31103
the RSV version = M2:M31103

The problem is now, that the search code only searches on COL B of the KJV sheet.

!. Is there a way to code the search to search for a word or phrase in each column instead of only col B? To me, the code would have to search from cols B, L and M.
I put these new versions in columns on the SAME sheet KJV thinking it would be much easier to search and find a value(s) instead of putting these new versions all
on separate new sheets.
2. Once values are found, I would like to copy them to a new Sheet called SEARCHRESULTS and display the results in a userform.

In case you need it, the updated workbook is attached.

Once again, thanks for all your help. This has become a very powerful tool now that I have all of the versions.

Loading Google Sheets

 

[chazrab]

12.28.21 5:12 PM CST US
Hi Akuini. I created a new FIND button on Userform1. The big green button at top right. I also grouped cols KJV, NIV, NASB and RSV on Sheet2
to make the search range continuous from F2:I31103. With the test word "embrace" searching down only on the KJV produces 14 results. Using the
range of F2:i31103 produces 53 results, some of which are duplicates,, i.e., Genesis 29:13, 3 x. Don't know why its doing this. There are no duplicate
verses in any of the 4 versions and the last used row of each version is 31103, which is s it should be.

I'm trying to do some of this myself to take the workload off you. Updated workbook and images of new forms and code are attached.

Please have a look and let me know what you think.

Once again, thanks for all your help.

cr

Loading Google Sheets

 

[chazrab]

Updated application with a few additions. Comments and docs as above. cr

 

[Akuini]

Sorry for the late reply.
I'm still trying to figure out the most efficient & reliable method to search value in all book version.
I understand that you are trying to use the Find method to search directly in the sheet. But I think it will be a bit slow.
I'll comeback if I think I have a better method.

 

[chazrab]

Hi Akuini. Latest changes uploaded. The FIND Method took 1 second to find the word "omitted" over a range of 5 cols and 31103 rows(With Worksheets("Sheet2").Range("E2:I31103"). I haven't tried phrases of words. If you have a better way, this is plenty fast enough for me.

If you click on the big green button that says Find Value, the FINDVALUE form appears. Type "omitted" in the textbox. It should pul up 33 results. If you then click on the Form view button it displays the SEARCHRESULTS form. If you'll notice, for some reason, most are duplicates appearing in the listbox1 on the left. Can you possibly figure out why its giving these duplicates and let me know.

The 5 images below reproduce the SEARCHRESULTS userform that has 4 Textboxes. Any changes can be added to any Textbox and must be saved back to the correct Sheet cell. Please have a look when you get a chance and see (1) how or if duplicates can be eliminated(may not be able to since the word "omitted" in example or any word will always be on same row as reference i.e., Matthew 17:21 and (2) how to save the changes. back to the correct cells in Sheet2.

If there's a better way to do this, please also let me know.

Once gain as before, thanks for all your help. cr

Loading Google Sheets

 

[chazrab]

Hi Akuini - everything is working great. I changed the menu bar to one listbox and did the rest. Much much simpler.
There is one important thing that I would like to ask your help with. When Userform1 is first run, Listbox2 populates with verses of Geneisis1
all the way down. See image below. When I type in a book, say "Daniel" either in Listbox1 or in combobx1, I want it to display the entire
chapter of that book in Listbox2 with verses of Daniel chapter1 just like it does with Genesis Chpter 1 when Userform1 is run.

. If I type in "Daniel 2" or any other book with the chapter number 1, 2, 3, etc., it will populate Listbox2 with verses from that chapter all the way down.

The way it works now, is that when I type in Daniel 2 for example, it only displays the first verse of Daniel or any other book in Listbox2. The rest
of Listbox2 is blank.

I know this is a very simply fix. But I don't want to experiment with changing your code in case it may not work right. I'd rather you do that
so it will be right.

Thanks again for all your help. The app works beautifully. These are just final refinements.
cr

 

[chazrab]

as above