vbams-wordword-2007word-2003

Scroll to view buttons where there are a varying number of buttons


I have selected a Sentence.

1) The sentence can vary.

2) I have split each word of the sentence.

The code below creates a list of Word array from Selection.

Sub Seperate_Words()

    Dim WrdArray() As String

    WrdArray() = Split(Selection)

    For i = LBound(WrdArray) To UBound(WrdArray)
        strg = strg & vbNewLine & WrdArray(i)
    Next i

    MsgBox strg

End Sub

Now I want to add a Search button in front of each word.

In every situation, the length of a sentence would change and Userforms are Pre-specified that's why I can't use them.

Following Image shows how output should be

Now problem I am facing is adding a scroll bar in frame which dynamically changes if needed.


Solution

  • I have found a very interesting solution to this:

    Create a UserForm (I have named mine "frmSearchForm")

    Create a Frame on it (mine is "framTest")

    Create a Classmodule and name it "clsUserFormEvents"

    Add this Code to it:

    Public WithEvents mButtonGroup As msforms.CommandButton
    
    Private Sub mButtonGroup_Click()
    'This is where you add your routine to do something when the button is pressed
    MsgBox mButtonGroup.Caption & " has been pressed" 'Just Example Code
    End Sub
    

    Then in the ThisDocument Module, add this code:

    Dim mcolEvents As New Collection
    
    Sub createButtonsOnForm()
    
    Dim Cmd As msforms.CommandButton
    
    'create instance of class
    Dim cBtnEvents As clsUserFormEvents
    
    'array for selection
    Dim wordArr() As String
    
    'get selection into array
    wordArr = Split(Selection, " ")
    
    Dim i As Integer
    
    'counter for the top position of buttons
    Dim topcounter As Integer
    
    topcounter = 10
    
    'loop through array
    For i = LBound(wordArr) To UBound(wordArr) Step 1
    
        'create button
        Set Cmd = frmSearchForm.framTest.Controls.Add("Forms.CommandButton.1", "Test")
    
        'Adjust properties of it
        With Cmd
            .Caption = wordArr(i)
            .Left = 100
            .Top = topcounter
            .Width = 50
            .Height = 20
        End With
    
        'Instantiate Class
        Set cBtnEvents = New clsUserFormEvents
    
        'Add cmd to event in class
        Set cBtnEvents.mButtonGroup = Cmd
    
        'Add buttonevent to collection so it won't get deleted in next iteration of the loop
        mcolEvents.Add cBtnEvents
    
        'increase the top position
        topcounter = topcounter + 25
    Next i
    
    'show userform
    frmSearchForm.Show
    End Sub
    

    Then if you run this sub, the selection gets splitted into the array, a button for every element is created(selection part as caption) and if you press the button, the method inside the class gets called, where you can use the mButtonGroup.Caption propery to get the value of button.

    Example:

    I have selected the Words "Test1" and "Test2", now when I run the Sub, the Form opens with 2 Buttons(Test1 and Test2): ExampleImg