Enable checkbox in Userform based on cell value

[RockandGrohl]

Hi guys, hopefully a quick one where I am just getting syntax confused here.

In a column I have a list of string entries on each line, such as "A1" on one line, A2 in the line below, C1 in the line below, etc.

I also have a userform that has a lot of checkboxes, and I'm trying to enable them based on the strings contained in this column.

For example, my checkbox names are handily like "A1_Checkbox", "A2_Checkbox" etc.

So I was thinking, I will rattle down the list of strings and disable all checkboxes on userform initialization, then enable them one by one based on what I find.

Private Sub UserForm_Initialize()

Dim regcol As Long
Dim lastrow As Long
Dim i As Long
Dim chkBox As MSForms.CheckBox

CritPanel.A1_Checkbox.Value = False
CritPanel.A2_Checkbox.Value = False
CritPanel.A3_Checkbox.Value = False
CritPanel.A4_Checkbox.Value = False
CritPanel.A5_Checkbox.Value = False
CritPanel.A6_Checkbox.Value = False
CritPanel.B1_Checkbox.Value = False
CritPanel.B2_Checkbox.Value = False
CritPanel.B3_Checkbox.Value = False
CritPanel.C1_Checkbox.Value = False
CritPanel.C2_Checkbox.Value = False
CritPanel.C3_Checkbox.Value = False
CritPanel.D1_Checkbox.Value = False
CritPanel.D2_Checkbox.Value = False


Set ppl = Worksheets("Price Panel Lines")

ppl.Activate

regcol = 29
lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To lastrow
    CritPanel." & cells(i,regcol).value & "_Checkbox".value = true
Next i

End Sub

RegCol is the column where the strings are held.

Hopefully you see the kind of logic I was going for there haha. Whether it works or not is another matter.

 

[diddi]

    CritPanel.Controls(Cells(i,regcol).Value & "_Checkbox").Value = true

 

[RockandGrohl]

    CritPanel.Controls(Cells(i,regcol).Value & "_Checkbox").Value = true

Excellent, this looks like it works.

I think I've got confused though, what this does is auto-tick each box that appears, which it does. What I wanted is for it to enable each box, meaning the ones that are missing are greyed out, and the ones that do appear are unticked. Is this possible? Thanks.

Also, how do I handle blanks or non-valid lines? Lets say the column has an incorrect string in like #N/A or blank?

 

[diddi]

For Each Ctrl in Me.Controls
    If TypeName(ctrl) = "CheckBox" then
        Ctrl.enabled = false
    End If
Next Ctrl

For i = 1 To lastrow
    If InStr("A1A2A3A4A5A6B1B2B3C1C2C3D1D2", Cells(i,regcol).Value)>0 then
        CritPanel.Controls(Cells(i,regcol).Value & "_Checkbox").Enabled = true
    End if
Next i

 

[RockandGrohl]

For Each Ctrl in Me.Controls
    If TypeName(ctrl) = "CheckBox" then
        Ctrl.enabled = false
    End If
Next Ctrl

For i = 1 To lastrow
    If InStr("A1A2A3A4A5A6B1B2B3C1C2C3D1D2", Cells(i,regcol).Value)>0 then
        CritPanel.Controls(Cells(i,regcol).Value & "_Checkbox").Enabled = true
    End if
Next i

Wrong end of the stick

Appreciate it, but this is what I was after:

Private Sub UserForm_Initialize()

Dim regcol As Long
Dim lastrow As Long
Dim i As Long
Dim chkBox As MSForms.CheckBox

CritPanel.A1_Checkbox.Enabled = False
CritPanel.A2_Checkbox.Enabled = False
CritPanel.A3_Checkbox.Enabled = False
CritPanel.A4_Checkbox.Enabled = False
CritPanel.A5_Checkbox.Enabled = False
CritPanel.A6_Checkbox.Enabled = False
CritPanel.B1_Checkbox.Enabled = False
CritPanel.B2_Checkbox.Enabled = False
CritPanel.B3_Checkbox.Enabled = False
CritPanel.C1_Checkbox.Enabled = False
CritPanel.C2_Checkbox.Enabled = False
CritPanel.C3_Checkbox.Enabled = False
CritPanel.D1_Checkbox.Enabled = False
CritPanel.D2_Checkbox.Enabled = False


Set ppl = Worksheets("Price Panel Lines")

ppl.Activate

regcol = 29
lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 3 To lastrow
    CritPanel.Controls(Cells(i, regcol).Value & "_Checkbox").Enabled = True
Next i


End Sub

As for the non-blanks or non-correct criteria, I'll have a look at what you wrote, cheers!

 

[diddi]

the rest deals with your second request

 

[RockandGrohl]

the rest deal with your second request

Yeah I accidentally hit submit and then my internet dropped out, when it came back on I had edited what I wrote, just looking at that second part now thanks

 

[diddi]

Private Sub UserForm_Initialize()

    Dim regcol As Long
    Dim lastrow As Long
    Dim i As Long
    Dim Ctrl As MSForms.Control

    Worksheets("Price Panel Lines").Activate
    
    regcol = 29
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "CheckBox" Then
            Ctrl.Enabled = False
        End If
    Next Ctrl
    
    For i = 1 To lastrow
        If InStr("A1A2A3A4A5A6B1B2B3C1C2C3D1D2", Cells(i, regcol).Value) > 0 Then
            CritPanel.Controls(Cells(i, regcol).Value & "_Checkbox").Enabled = True
        End If
    Next i
End Sub