RE: USERFORM TO SEARCH FOR VERSES, OR WORDS WITHIN VERSES IN A BIBLE SPREADSHEET

[chazrab]

Has anyone out there created a userform to search for verses and more importantly, words contained within verses in a
Sheet(KJV)? I downloaded the entire Bible from a site called Sourceforge.net. As you might imagine, it has 31,103 rows
from Genesis to Revelation.

Bible software apps use Boolean operators, quotes, etc. for input into the search fields an the userform would have to incorporate
that to search for "love" OR "God AND love"

Just wondering if anyone knows if there is a userform app somebody has developed that will enable searching for verses, word strings, etc.
I'm in the beginning of doing some initial work on this and I just need a little guidance. Here's what I have so far:

Thanks for anyone's help on this.
cr

 

[Trebor76]

Hi chazrab,

The following is based on an userform having a listbox called "lbxVerses" and a text box called "txtVerseSearch" (this is the control you would enter the verse (or part thereof) into):

Option Explicit
Private Sub UserForm_Initialize()

    'Userform screen placement

    With Me
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    End With
    
End Sub
Private Sub UserForm_Activate()

    Dim wsSrc As Worksheet
    Dim lngMyRow As Long
    Dim lngLastRow As Long
    Dim lngStartRow As Long
    
    
    Set wsSrc = ThisWorkbook.Sheets("KJV")
    lngStartRow = 2
    
    lngLastRow = wsSrc.Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For lngMyRow = lngStartRow To lngLastRow
        With Me.lbxVerses 'Name of form listbox.  Must have three columns.
            .AddItem wsSrc.Range("A" & lngMyRow)
            .List(lngMyRow - lngStartRow, 1) = wsSrc.Range("B" & lngMyRow)
            .List(lngMyRow - lngStartRow, 2) = wsSrc.Range("C" & lngMyRow)
        End With
    Next lngMyRow
    
    Me.txtVerseSearch.SetFocus 'Name of text box where the user would enter the text into to search for a matching the verse(s) into.

End Sub
Private Sub txtVerseSearch_Change()

    Dim lngLBIndex As Long

    With Me.lbxVerses
        .ListIndex = -1 'Remove any current selection(s)
        For lngLBIndex = 0 To .ListCount - 1
            Debug.Print .List(lngLBIndex, 2)
            If "*" & StrConv(.List(lngLBIndex, 2), vbUpperCase) & "*" Like "*" & StrConv(Me.txtVerseSearch, vbUpperCase) & "*" Then
                .TopIndex = lngLBIndex
                .Selected(lngLBIndex) = True
                Exit For
            End If
        Next lngLBIndex
    End With

End Sub

Regards,

Robert