Frame scrollbar resets when clicking on/in frame

[Nikata]

Dear all,

I've been trying to make a Userform which contains:
- Textbox, used as search input
- Search button
- Load button (not coded yet)
- Frame:
- Checkboxes (dynamically made)
- Scrollbar

When I've used the search function, the scrollbar moves to the found value (image 2), however when I try to tick that value, the scrollbar resets to the top (image 3).
It doesn't matter if I click on the checkboxes or anywhere else in the frame, the scrollbar resets.

Does anyone know how to fix this? Thank you in advance!
My current code is:

Private scrollPos As Single

Private Sub Userform_Initialize()
      
    ' Set worksheet reference
    Dim wsSettings As Worksheet
    Dim methodsTable As ListObject
    Dim M As String
    Dim chkName As String
    Dim Mnum As MSForms.CheckBox
    Dim j As Integer
    Dim topPosition As Single
    Dim leftPosition As Single
    Dim columnCount As Integer
    Dim maxHeight As Single
    Dim frameHeight As Single
    Dim itemsPerColumn As Integer
   
    Set wsSettings = ThisWorkbook.Worksheets("Settings")
    Set methodsTable = wsSettings.ListObjects("M")
   
    ' Clear existing checkboxes in the Meth frame
    ClearFrameControls MethodPlanner.Meth
   
    ' Initial positions for the first checkbox
    topPosition = 0
    leftPosition = 6
    columnCount = 0
    maxHeight = 0
   
    itemsPerColumn = methodsTable.ListRows.Count / 3 ' Number of items per column
   
    ' Loop through methods and create checkboxes dynamically
    For j = 1 To methodsTable.ListRows.Count
        M = methodsTable.ListColumns("M").DataBodyRange(j, 1).Value
               
        ' Construct checkbox name dynamically
        chkName = "Checkbox" & j
       
        ' Create a new checkbox
        Set Mnum = MethodPlanner.Meth.Controls.Add("Forms.CheckBox.1", chkName, True)
       
        ' Set properties of the checkbox
        With Mnum
            .Top = topPosition
            .Left = leftPosition
            .Width = 100
            .Height = 20
            .Caption = M
        End With
       
        ' Update maxHeight with the maximum height of the current checkbox
        If Mnum.Height > maxHeight Then
            maxHeight = Mnum.Height
        End If
       
        ' Increment top position for the next checkbox
        topPosition = topPosition + 20
       
        ' Check if we need to start a new column
        If j Mod itemsPerColumn = 0 Then
            ' Adjust left position and reset top position for a new column
            leftPosition = leftPosition + 110
            topPosition = 0
        End If
    Next j
   
    ' Set the ScrollHeight of the frame to accommodate all checkboxes
    frameHeight = (itemsPerColumn + 1) * (maxHeight)
    MethodPlanner.Meth.ScrollHeight = frameHeight
End Sub

Private Sub ClearFrameControls(ByRef frame As MSForms.frame) 'Removes all checkboxes to make a new list.
    Dim ctrl As Control
    Dim i As Integer

    ' Loop through the controls in reverse order to avoid issues with removing controls while iterating
    For i = frame.Controls.Count - 1 To 0 Step -1
        If TypeName(frame.Controls(i)) = "CheckBox" Then
            frame.Controls.Remove frame.Controls(i).Name
        End If
    Next i
End Sub

Private Sub Search_Enter()
    Dim ctrl As Control
    Dim searchTerm As String
    Dim found As Boolean
   
    searchTerm = Searchbox.Text
    found = False
    scrollPos = 0
   
    ' Reset the ForeColor of all checkboxes to default and clear last found checkbox reference
    For Each ctrl In Me.Meth.Controls
        If TypeName(ctrl) = "CheckBox" Then
            ctrl.ForeColor = vbBlack
        End If
    Next ctrl
   
    ' Loop through all controls in the frame Meth
    For Each ctrl In Me.Meth.Controls
        If TypeName(ctrl) = "CheckBox" Then
            If InStr(1, ctrl.Caption, searchTerm, vbTextCompare) > 0 Then
                ' Match found
                found = True
                scrollPos = ctrl.Top
                ' Highlight the found checkbox
                ctrl.ForeColor = vbBlue
                Set lastFoundCheckbox = ctrl
                Exit For
            End If
        End If
    Next ctrl
   
    If found Then
        Me.Meth.ScrollTop = scrollPos
    Else
        MsgBox "No matching checkbox found.", vbExclamation
    End If
End Sub

 

[Micron]

You actually have all of these controls in a frame? The value of the checkbox would pass to the frame, so my guess is that the frame is either taking the focus or becoming the active object, thus the view moves to the top of the frame object. I presume there is no checkbox event code because you didn't show any, but if there is I'd also look there.

I was going to ask what the point that design is since your code doesn't address passing the cb value to the frame but first I googled it. Surprised to learn that apparently, Excel doesn't do what Access does. How odd that you have to code for that. But I do wonder why you just don't use a listbox, which will maintain its scroll position (at least I think it would in Excel).

 

[Nikata]

Thank you for your reply Micron.
The Load button would be the way to check which boxes were ticked so no checkbox events coded.
I did have a code for checkbox handlers but that didn't (seem to) do anything to fix this problem.

Dim ctrl As Control
Dim chkHandler As clsCheckBoxHandler

' Initialize the collection
Set chkBoxHandlers = New Collection

' Loop through each control in the frame "Meth"
For Each ctrl In MethodPlanner.Meth.Controls
      If TypeOf ctrl Is MSForms.CheckBox Then
            ' Create a new handler for each checkbox
            Set chkHandler = New clsCheckBoxHandler
            ' Initialize the handler with the checkbox and its parent frame
            chkHandler.Initialize ctrl, MethodPlanner.Meth
            ' Add the handler to the collection to keep it in scope
            chkBoxHandlers.Add chkHandler
      End If
Next ctrl

The reason I've not used a listbox is that there are almost 200 methods listed in the frame, making it a huge list. With the checkboxes I can make a wider frame, making it more user friendly to find all the required methods.

 

[Micron]

Not sure I agree. You're using a find feature anyway, so why not jump to the list item that matches the found value? I'd try copying the form and removing the frame. I still don't get why you need the frame if it's not visible anyway (at least I can't see it in your pictures).

 

[Nikata]

This is actually what was the problem.

The value of the checkbox would pass to the frame, so my guess is that the frame is either taking the focus or becoming the active object, thus the view moves to the top of the frame object.

    If found Then
        'I've added this row and now it works!
        Me.Meth.SetFocus
        Me.Meth.ScrollTop = scrollPos
    Else
        MsgBox "No matching checkbox found.", vbExclamation
    End If

I thought just setting the .ScrollTop would be sufficient, but it needed to be .SetFocus first.

 

[Micron]

Glad that you figured it out.