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:
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:
VBA Code:
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