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]

The AUTOFILTERRESULT show the SEARCHRESULT form. It also has a Listbox and two Textboxes. This user form
displays the COPIED search result on a separate sheet. My only issue is that with that being a copy I have no
way at this point to write back any notes I want to add to any search result item.
But - if I add notes on the MAIN USERFORM in Textbox2 on the form directly, the notes are written back to the GENESIS2 sheet in
COL D on the correct row. It only happens correctly when the changes are made on the Textbox2 of the MAIN USERFORM.
Here is the code that does this:

Private Sub ComboBox1_Click()
'Me.TextBox9.Value = Me.ComboBox1.Value
If Me.ComboBox1.Value = "Gen" Then
   ListBox1.RowSource = "'GENESIS2'!A2:D3066"
   Set wksht = Sheets("GENESIS2")
   sheetName.Value = "GENESIS2"
Else
If Me.ComboBox1.Value = "Dan" Then
   ListBox1.RowSource = "'DANIEL'!A2:D3066"
   Set wksht = Sheets("DANIEL")
   sheetName.Value = "DANIEL"
Else
End If
End If
End Sub

When any book is selected in the combobox dropdown, the code above sets the Listbox1.Rowsource to THAT sheet.
That won't do me any good, because I need thee updated notes written back to the KJV MASTER SHEET.

What I am doing is just breaking down the KJV MASTER sheet into separate sheets, each sheet being a Book. The Notes
change in each book has to be transferred or, written back tot the KJV MASTER.

I 've given the most detail I could to make it as clear as possible about the issue I'm having. I don't like giving too much
information because people will get tired of reading.

Thanks again for all your help. I hope you can help me figure this out.

cr

 

[Akuini]

I can't access your workbook. Probably you have different setting, try:
On your google drive:
Right click the file you want to share > Click Get Link > don't choose "Restricted", choose "Anyone with link" instead > Click Copy Link > now you get the link, just paste the link here.

 

[chazrab]

I can't access your workbook. Probably you have different setting, try:
On your google drive:
Right click the file you want to share > Click Get Link > don't choose "Restricted", choose "Anyone with link" instead > Click Copy Link > now you get the link, just paste the link here.

Loading Google Sheets

Did as directed. Please let me know if you got it this time. cr

 

[Akuini]

Ok, I got it.
I'll get back to this thread tomorrow.

 

[Akuini]

I don't undestand your workbook structure.

What I am doing is just breaking down the KJV MASTER sheet into separate sheets, each sheet being a Book.

1. Are there only 2 Books? i.e. GENESIS2 & DANIEL?
2. Why do you need to split KJV MASTER sheet into separate sheets? Is this the reason?

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.

3. What is the main purpose of the userform? Is it to search for a specific verse & change the notes?
4. Is col A in sheet KJV has unique data (no duplicate)?

 

[chazrab]

I don't undestand your workbook structure.

1. Are there only 2 Books? i.e. GENESIS2 & DANIEL?
2. Why do you need to split KJV MASTER sheet into separate sheets? Is this the reason?

3. What is the main purpose of the userform? Is it to search for a specific verse & change the notes?
4. Is col A in sheet KJV has unique data (no duplicate)?

Question 1: No. Thi is just a start.

2. I don't necessarily have to split the KJV MASTER into separate sheets. John Walkenbach separated the entire KJV Bible by sheet. That is, each sheet is a book. So there are 66 sheets from Genesis to Revelation. I just copied it and reformatted it. I followed John's logic, which initially makes sense. When I did this separation into each book being a separate sheet, things got a bit confusing. I would really PREFER to have only ONE MASTER SHEET, the KJV sheet with the entire 31103 row.

3. The userform has 3 purposes: 1) Viewing verses in an easy-to-read Listbox or textbox format (although I don't see how i'ts possible to separate and index verses in one textbox with that large amount of text) 2) Searching for any word or phrase. My preference is autofilter over FIND (quicker and much less code)
3) Adding notes to any verse. The notes are added, changed, deleted, etc, in a separate Textbox on th userform. That would be COL D (Notes) on the sheet. When notes are made, for a particualr verse in COL C, they are written back to the sheet in the same row as that verse it refers to.

4. COL A is unique. It is the verse reference of each verse.

I hope I've been clear. I really don't need to separate the entire Bible by each book per sheet. Working with only the KJV MASTER seems much less
complicated. Akuini, many thanks for all your help on this journey. cr

 

[Akuini]

Questions:
1. In combobox1 you load the book titles such as Gen & Dan. Is there any particular reason why you don't use the full title i.e Genesis & Daniel?
I mean it would be easier to code if you just use the full name.
2. In Userform3, I select "Gen" then search a value, says "waters", do you want the result from all books or just from Genesis?

 

[chazrab]

Questions:
1. In combobox1 you load the book titles such as Gen & Dan. Is there any particular reason why you don't use the full title i.e Genesis & Daniel?
I mean it would be easier to code if you just use the full name.
2. In Userform3, I select "Gen" then search a value, says "waters", do you want the result from all books or just from Genesis?

1. The reason they're not spelled completely out is because these were copied abbreviations from JW's example. I could easily change this
if it makes things easier.
2. Both. I want to be able to search for waters, or gold or any word and pull up all occurrences in the entire Bible. I also want to have the option
to set a filter range - Genesis to Numbers, ! Kings, Matthew-John, etc. That would just amount to setting a range to search. That can come later. cr

 

[Akuini]

Another question:
In sheet KJV, col B & C basically has the same value, the only difference is in col C there are references to the verse number, such as "Genesis 1:1". Why do you need that?
It's more efficient if you just remove col C. You can get the reference from col A.

 

[Akuini]

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