Excel 2010 Userform with Dynamically updating checkboxes

[Alexandroid]

Hey everyone, sorry if this answer is already out there, I have been searching for a week now without any luck.

What I would like to do is have a UserForm that displays a check box to each item in a list, and if the column next to the item has a "Y", then the checkbox would appear already checked and if it contains "N" then it would not be checked.

Example:

Column A | Column B

Apple | Y
Pear | N
Orange | Y
Lime | N
etc...

In the above example, when the Userform is displayed, Apple and Orange would already have the checkbox filled and the Pear and Lime would have the checkbox empty.

I'm looking to apply this to multiple workbooks so the list from Column A would very in length. I'm picturing that once the list reaches a certain amount on the userform, it would widen the userform and start a second column of checkboxes with the rest.

What I'm looking for though is how to get the Checkbox labels to automatically pull the names from Column A as well as pre"Check" the checkboxes depending on the Y or N answer in column B. If there are any changes to the form, they would need to then be written to Column B as well.

Thank you very much for your help and all the help this forum has provided!

Alex

 

[Darren Bartrup]

Give your worksheet a codename of Sheet1.

Add a new class module - call it Class1.
Add this code to the class module:

Option Explicit

Public WithEvents cBox As MSForms.CheckBox


Private Sub cBox_Click()
    Select Case cBox.Value
        Case True
            Sheet1.Cells(cBox.Tag, 2) = "Y"
        Case False
            Sheet1.Cells(cBox.Tag, 2) = "N"
        Case Else
            'Throw an error message.
    End Select
End Sub

Create a form called UserForm1. Place a frame control on the form, call it Frame1 - make sure the ScrollBars property is set to 2 - frmScrollBarsVertical

Add this code to the userform:

Option Explicit


Dim chkBoxColl As Collection


Private Sub UserForm_Initialize()


    Dim chkBoxEvent As Class1
    Dim chkBox As Control
    Dim txtBox As Control


    Dim lLastRow As Long
    Dim x As Long
    
    lLastRow = Sheet1.Rows(Sheet1.Rows.Count).End(xlUp).Row
    
    Set chkBoxColl = New Collection
    
    For x = 2 To lLastRow
        With Me
            Set txtBox = .Frame1.Controls.Add("Forms.TextBox.1", "TxtBox" & x)
            Set chkBox = .Frame1.Controls.Add("Forms.CheckBox.1", "ChkBox" & x)
            txtBox.Left = 6
            txtBox.Height = 18
            txtBox.Width = 150
            txtBox.Top = (x - 2) * 18 + 5 '+5 is distance from top of frame.
            txtBox = Sheet1.Cells(x, 1).Value
            
            chkBox.Left = 156 + 10
            chkBox.Height = 18
            chkBox.Width = 150
            chkBox.Top = (x - 2) * 18 + 5
            chkBox = IIf(Sheet1.Cells(x, 2).Value = "Y", 1, 0)
            chkBox.Tag = x 'Remember the row number the value came from.
        End With
        
        'Add the controls to the collection.
        Set chkBoxEvent = New Class1
        Set chkBoxEvent.cBox = chkBox
        chkBoxColl.Add chkBoxEvent
        
    Next x
     
    Me.Frame1.ScrollHeight = x * 18 + 5 'Set the scroll height.


End Sub

This will show all your column A values in a text box, and the Y/N value in a checkbox. Clicking the checkbox will update the value on the sheet.

Hope it works.

 

[Norie]

Here's an alternative to multiple textboxes/checkboxes.

Create a userform with a listbox on it and add this code.

Private Sub UserForm_Initialize()
Dim cl As Range

    ListBox1.ListStyle = fmListStyleOption
    ListBox1.MultiSelect = fmMultiSelectMulti

    With Sheet1
        For Each cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
            ListBox1.AddItem cl.Value
            ListBox1.Selected(ListBox1.ListCount - 1) = cl.Offset(, 1) = "Y"
        Next cl
    End With

End Sub

 

[Alexandroid]

Give your worksheet a codename of Sheet1.

Add a new class module - call it Class1.
Add this code to the class module:

Option Explicit

Public WithEvents cBox As MSForms.CheckBox


Private Sub cBox_Click()
    Select Case cBox.Value
        Case True
            Sheet1.Cells(cBox.Tag, 2) = "Y"
        Case False
            Sheet1.Cells(cBox.Tag, 2) = "N"
        Case Else
            'Throw an error message.
    End Select
End Sub

Create a form called UserForm1. Place a frame control on the form, call it Frame1 - make sure the ScrollBars property is set to 2 - frmScrollBarsVertical

Add this code to the userform:

Option Explicit


Dim chkBoxColl As Collection


Private Sub UserForm_Initialize()


    Dim chkBoxEvent As Class1
    Dim chkBox As Control
    Dim txtBox As Control


    Dim lLastRow As Long
    Dim x As Long
    
    lLastRow = Sheet1.Rows(Sheet1.Rows.Count).End(xlUp).Row
    
    Set chkBoxColl = New Collection
    
    For x = 2 To lLastRow
        With Me
            Set txtBox = .Frame1.Controls.Add("Forms.TextBox.1", "TxtBox" & x)
            Set chkBox = .Frame1.Controls.Add("Forms.CheckBox.1", "ChkBox" & x)
            txtBox.Left = 6
            txtBox.Height = 18
            txtBox.Width = 150
            txtBox.Top = (x - 2) * 18 + 5 '+5 is distance from top of frame.
            txtBox = Sheet1.Cells(x, 1).Value
            
            chkBox.Left = 156 + 10
            chkBox.Height = 18
            chkBox.Width = 150
            chkBox.Top = (x - 2) * 18 + 5
            chkBox = IIf(Sheet1.Cells(x, 2).Value = "Y", 1, 0)
            chkBox.Tag = x 'Remember the row number the value came from.
        End With
        
        'Add the controls to the collection.
        Set chkBoxEvent = New Class1
        Set chkBoxEvent.cBox = chkBox
        chkBoxColl.Add chkBoxEvent
        
    Next x
     
    Me.Frame1.ScrollHeight = x * 18 + 5 'Set the scroll height.


End Sub

This will show all your column A values in a text box, and the Y/N value in a checkbox. Clicking the checkbox will update the value on the sheet.

Hope it works.

Thank you Darren (And Norie as well) for your alternate code! It's working great and I've been able to tweak it so that it fits perfectly in my document! Is there a way to label this question as answered?