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

Question

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.

Answer 1

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):