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]

RE: to the question above about displaying full text of chapter in a search or when form opens. Don't know if this is correct, but see my comment below:

Private Sub ComboBox1_Change_old()
Dim i As Long
'ListBox2.Clear 'commented out as a test, seems to do what I asked. Hope this is correct and OK
With ComboBox1
If .ListIndex <> -1 Then
    For i = 1 To UBound(vList, 1)
        If vList(i, 1) = .Value Then
            ListBox2.AddItem
            va = Application.Index(vList, i)
                For j = 1 To 4
                    ListBox2.List(0, j - 1) = va(j)
                Next

 

[chazrab]

Hi Akuini - The code that finds values in the search box incorrectly pulls up more than what's asked for. A simple search to pull up all the verses in
chapter 6 gives verses from chapter 16. There are only 23 verses in Romans chapter 6 and typing in "Romans 6:" should only pull up the 23 verses in chapter 6.

Its also pulling up every verse in Romans 16. Romans 16 has 27 verses. 23 verses in 6 +27 verses in 16 = 50. For some reason, it pulls up 53 records as
you can see in the textbox at the lower left side of the image below. The middle of the listbox image also shows several verses from Romans 16 as do
the version textboxes on the right.

Can you please have a look when you get a chance and see if you can correct this to make it work right. I'm hesitant about changing your own code
myself, so I really need your help.

Thanks, cr.

Everything else(1 listbox only for all macros) is working great.

Thanks, cr

 

[chazrab]

Hi Akuini - it's been a while since I've heard back from you on a couple of small things I asked for help with above. I get the feeling that you perhaps
do not want to help me any further because I was either bothering you too much or changing your code. I know of no one out there who has the
level of understanding you do = that has carried this application this far for me with your code. Thank you. again.

I totally understand if you want to back away from any further help for me. There are many others out there who can benefit from your experience.
If you don't answer back, I'll take that as a no. No problem. Thanks again for all your help. I could not have gotten this far without your help.


cr
Kingwood Texas

 

[Akuini]

Sorry, I'm busy at the moment and I still haven't found a good method for finding values in all versions of a book.
Your project is getting more complex, I think it would be better if you post a new thread for each specific problem you are facing, so one thread is only for one specific problem, not one thread for the whole project. That will attract more people to help you.
I will monitor all your new threads & I will help if I can.

 

[chazrab]

Hi Akuini
I posted this yesterday. Well, I now decided I wanted to have the selected verse begin at the TOP and have about 30 verses to follow(by value of changing the nv?)
I know you're busy, if you could help with this, I'd really appreciate it What I actually want is for this to appear on a new sheet Textbox(ActiveX control) instead of
putting it on a userform.

The following code block simply takes the verse, in this case Romans 6:1 and places rows of text above and below the selection.
Code:
Sub get_multi_verse()
Dim c As Range
Dim tx As String
Dim va
Dim nv As Long

nv = 30 'how many verses before & after selected verse should be displayed ----> nv can be changed to any number which determines no. of verses above and below selected verse

With wkjv.Range("A:A")
Set c = .FIND(What:=sREF, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
If c.Row - nv < 2 Then
va = .Cells(2).Offset(, 1).Resize(nv * 2 + 1)
Else
va = c.Offset(-nv, 1).Resize(nv * 2 + 1) 'va = c.Offset(-nv, 1).Resize(nv * 2 + 1)
End If

For Each X In va
tx = tx & vbLf & X & vbLf
Next

'text of selected verse + some verses before & after
TextBox1 = tx

Else
MsgBox "Can't find " & sREF
End If
End With
End Sub
the first image below shows the highlighted verse, Romans 6:1, in the middle of the text. I want to change the code and,
instead of having the selected verse appear right in the middle of 60 rows(30 above, 30 below), I want the selected verse to
appear at the beginning of the selection as in image 2 below. I can't figure out how to change the code to do that correctly
Can someone please help with this ?
Thanks, cr

Attachments​

• 
  SELECTED VERSE IS IN THE MIDDLE OF 15 QBOVE AND 15 BELOW.jpg
  126.2 KB · Views: 6
• 
  WANT CODE TO MAKE SELECTED VERSE APPEAR FIRST. .jpg
  126.1 KB · Views: 5

Thanks again for your willingness to help. I usually don't change my mind on things. This app has become so dynamic and reaches far and
above in every Bible App software like Logos, that I had some idea changes in making more useful. The way you were able to pull up each verse and
put it into pargraph mode and separate it by a space as in your code aobe is really what I wanted on a sheet. The Listbox control is OK but it
doesnt't display as neat and well as a textbox because you can't separate verses into paragraphs with a space in between, to my knowledge -
Sorry for being long winded. Thanks again for all your help.

cr

 

[Akuini]

What I actually want is for this to appear on a new sheet Textbox(ActiveX control) instead of
putting it on a userform.

Are you saying you want to put all active x control on the sheet and not using userform at all?
Could you explain why? because I think it will be harder to set it up on the sheet than on userform.

 

[chazrab]

Yes. I got it to work. It is Sheet 3 of the attached file you've been helping me with.
i was also able to solve my other question of having the selected verse appear at the beginning instead of in the middle by changing these lines a little:

'            va = c.Offset(-nv, 1).Resize(nv * 2 + 1) 'va = c.Offset(-nv, 1).Resize(nv * 2 + 1)
             va = c.Offset(, 1).Resize(nv * 2 + 1) 'new try --->This works and puts the first verse a the top and all nv verses below it
         End If

All these changes are the result of trial runs I'm taking this app through.
This app, in essence, is just a reporting tool. My goal was and is to develop something fast, efficient and accurate and give comparative views of the 3 most popular versions.

With your help, that has been achieved. I learned a lot from your coding especially using nv and Resize to manipulate and display textbox text to display in lines, paragraphs, with spaces in between each verse and change the value of nv to display as many verses below as I want.

If you take a look at sheet3, you'll see some of your code lines I used and changed just a few things as mentioned.

One thing I see that a Listbox DOES do, whether on a userform or as an ActiveX control on a sheet, is give the ability to scroll through each verse and have textboxes 3, 4 and 5 on Userform1 display all different versions and update to that specific verse for their version when the Listbox selection is clicked.

This is so important because it allows seeing how different translations can change the entire meaning of a verse with one or two word changes.

Now, what I'd like to do is see if It's possible to update each textbox(4,5 and 6 on Sheet3 to change(synchronize) with the touched or clicked verse in Textbox3(large textbox on the shee t(exactly how the functionality of a Listbox works with)

n = ListBox2.ListIndex
    TextBox1.Value = ListBox2.List(n, 0)

The Listbox control HAS that functionality. But to do this with a Textbox, seems like each verse in the Textbox would have to be indexed in some kind of
way to identify that verse - maybe like a row number of the current row(since each verse is on its own row).

Anyway, this is just an update. Please let me know if you have any ideas on how this can be done with a textbox. Also, in reference to your
previous issue of finding and searching for any word or phrase, i think I solved that problem. In the FIND method I just changed xlWhole to xlPart.
That allows finding any value WITHIN a cell, as opposed to having to match the WHOLE cell(xlWhole).

Thanks again, Akuini, for all your help.

Loading Google Sheets

 

[chazrab]

Hi Akuini. I need your help when you have the time. I put this out on the forum yesterday. No responses that helped.


Is there any way to do this. (Images below)
The images below explain in much less words I can describe. When a user first clicks on this apps combobox(or whatever
this control is, the following occurs in sequence:
1. A
or userform(or whatever this control is) list automatically drops down giving the user a book choice to click on
2. Once a book choice click is made, another smaller userform[table?] drops down giving the user a chapter choice to then select
3. Once book and chapter choices are made(and stored in memory ), a third smaller userform list drops down
giving the user a choice to select a verse. Each selection is stored in memory.

Once a button is clicked, the FIND method should go right to the Book chapter and verse and display that result in a userform textbox i.e. --> Matthew 24:15.

tried this with a userform and Labels and the image MYTRY below. My initial thoughts is that that its crude and mundane with labels
and I really don't want to invest new time in a learning curve with something like Visual Studio or some other app that is better designed for this - but if I have to, I will.
Userform code:

Code:
Private Sub UserForm_Initialize()
Me.Top = 10
Me.Left = 5
End Sub

Private Sub ZECH_Click()
ZECH.BackColor = RGB(0, 200, 255)
Sheets("BIBLETEXT").TextBox1.Value = "Zechariah"
Sheets("BIBLETEXT").TextBox2.Value = "Zechariah"
End Sub
Next steps would be to add code to open two other userforms, one after the other as an itme on each is clicked,
and replicate what the app images does below. The beauty of all this is it involves NO TYPING AT ALL. Only successive mouseclicks.

I would like to do this with a Listbox vs a userform like you did with the menu system, if that's possible

I really need a higher level of experience help on this to accomplish what I have described. Please help if you can.

Many thanks and much appreciated.