Need design help with checkboxes and array of values

[Galley]

My user form will have a series of Check Box Form Controls. They will be used for displaying results from a matrix (array) on a separate worksheet. The user will make a selection from a validated list. The list is a document category. For each category, there are several associated departments that must sign off on the document. The user must be able to select additional departments, but may not deselect any pre-selected departments. The pre-selected items will be disabled via VBA.

I am using conditional formatting to indicate one of three states each checkbox may have. I am not using the checkbox's caption, but instead am using the adjacent cell for that purpose. Each checkbox will have an addditonal cell used to store the Enabled state of the checkbox.

The three states are as follows:
Unselected (tan background), Checkbox's linked cell value (G2) is FALSE, Enabled State cell value (H2) is "".
Pre-selected (purple background), Checkbox's linked cell value (G2) is TRUE, Enabled State cell value (H2) is FALSE.
User-selected (green background), Checkbox's linked cell value (G2) is TRUE, Enabled State cell value (H2) is "".

For testing purposes only, I have added two command buttons. Button3 selects the checkbox and disables it. Button4 deselects and re-enables. The code and conditional formatting work as intended.

Sub Button3_Click()
'Selects the checkbox
ActiveSheet.Range("G2") = True

'Disables the checkbox
Sheets("Sheet1").CheckBoxes("Check Box 1").Enabled = False

ActiveSheet.Range("H2") = False
End Sub

Sub Button4_Click()
'Deselects the checkbox
ActiveSheet.Range("G2") = False

'Re-enables the checkbox
Sheets("Sheet1").CheckBoxes("Check Box 1").Enabled = True

ActiveSheet.Range("H2") = ""
End Sub

So my question is, What is the best way of looping through a series of 20-25 of these checkboxes? As the selection changes in the validated list, so will the associated checkboxes that must be pre-selected and disabled, based on the values in the matrix. I will also need to loop through the selected items and transfer the list to a report. I have an example workbook with the matrix and a single checkbox, but am unable to post attachments.

Thanks!

 

[Galley]

I just wanted to add that previously I had tried using an ActiveX Listbox. I was able to loop through the list and retrieve the values, but I don't believe it is possible to disable a single item in the list.

 

[AlphaFrog]

This is an example of how you might loop through 25 form-type checkboxes by name, but I don't really follow your worksheet configuration and what exactly you want done.

[COLOR=darkblue]Sub[/COLOR] Button3_Click()
    
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] 25 [COLOR=green]'loop through checkboxes 1 to 25[/COLOR]
        
        [COLOR=green]'Selects the checkbox[/COLOR]
        ActiveSheet.Range("G2").Offset(i - 1) = [COLOR=darkblue]True[/COLOR]
        
        [COLOR=green]'Disables the checkbox[/COLOR]
        Sheets("Sheet1").CheckBoxes("Check Box " & i).Enabled = [COLOR=darkblue]False[/COLOR]
        
        ActiveSheet.Range("H2").Offset(i - 1) = [COLOR=darkblue]False[/COLOR]
        
    [COLOR=darkblue]Next[/COLOR] i
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Galley]

This is an example of how you might loop through 25 form-type checkboxes by name, but I don't really follow your worksheet configuration and what exactly you want done.

[COLOR=darkblue]Sub[/COLOR] Button3_Click()
    
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] 25 [COLOR=green]'loop through checkboxes 1 to 25[/COLOR]
        
        [COLOR=green]'Selects the checkbox[/COLOR]
        ActiveSheet.Range("G2").Offset(i - 1) = [COLOR=darkblue]True[/COLOR]
        
        [COLOR=green]'Disables the checkbox[/COLOR]
        Sheets("Sheet1").CheckBoxes("Check Box " & i).Enabled = [COLOR=darkblue]False[/COLOR]
        
        ActiveSheet.Range("H2").Offset(i - 1) = [COLOR=darkblue]False[/COLOR]
        
    [COLOR=darkblue]Next[/COLOR] i
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Thanks, that actually helped me out on another section of the form that has a series of checkboxes. I am using the following code to use Checkbox 2 as a toggle button for selecting all or none.

Sub CheckBox2_Click()
If Range("N48") = True Then
Dim i As Long
    
    For i = 2 To 21 'loop through checkboxes 2 to 21
        
        'Selects all checkboxes in the Responsible Areas section
        ActiveSheet.Range("N48").Offset(i - 1) = True
        
    Next i
Else
Dim j As Long
    
    For j = 2 To 21 'loop through checkboxes 2 to 21
        
        'Deselects all checkboxes in the Responsible Areas section
        ActiveSheet.Range("N48").Offset(j - 1) = False
   
   Next j
   
End If
    
End Sub

What I need to do, however, is to only select certain checkboxes based on a drop-down menu selection. I'm thinking for that I will need to use the MATCH, INDEX, VLOOKUP functions.

 

[AlphaFrog]

to only select certain checkboxes based on a drop-down menu selection.

Can you give specifice examples?

This is your all\none code cleaned up a bit

[color=darkblue]Sub[/color] CheckBox2_Click()
    
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color], bN48 [color=darkblue]As[/color] [color=darkblue]Boolean[/color]
    bN48 = Range("N48").Value
    
    [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] 21 [color=green]'loop through checkboxes 2 to 21[/color]
        
        [color=green]'Selects all checkboxes in the Responsible Areas section[/color]
        ActiveSheet.Range("N48").Offset(i - 1) = bN48
        
    [color=darkblue]Next[/color] i
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Galley]

My array has 25 rows and 20 columns. The row headers (Document Category) are used to populate the Category validated list. The column headers (Departments) each have a checkbox associated with them. The array is marked with an X to indicate which department must sign off on each document category.

A previous version of this form used a combobox form control and the INDEX function to look up the departments, but the results were displayed in a row of unprotected cells.

So my question is, how do I loop through a single row, and for each X, select the corresponding checkbox and disable it?

As an example, In row 14 of the array, there are X's in columns 5, 11 and 12. Only the three checkboxes associated with those columns should be selected and disabled.

Let's say there are 20 checkboxes, linked to cells S1:S20. Assume array column 1 corresponds to Checkbox cell S1. The porcedure would need to set the value of S5, S11 and S12 as "TRUE" and T5, T11 and T12 as FALSE. Does that make sense?

 

[Galley]

This is probably not the most elegant solution, but sometimes I like to do things in stages, which I find easier to understand the logic and spot errors.

Here's what's I have so far:
Checkboxes are numbered 24 - 48.
The linked cells for each checkbox are in S47:S71.
I am storing the "Enabled" state for each checkbox in T47:T71.
I am storing the "Default" state for each checkbox in U47:U71.

I am using the MATCH function in I47 to find the corresponding row number in the array, based on the selection made in the validated list.

I am using the INDEX function to find the X's in the row. If there is an X is in the cell, the Default state is TRUE.

For testing purposes, I have added Button 52, which selects and disables the first checkbox. An additional button deselects and re-enables the first checkbox. The three states, and their related conditional formatting are working as intended.

Sub Button52_Click()

'Default State
If Range("U47") = True Then

'Checked State
Range("S47") = True

'Enabled State (for conditional formatting purposes only)
Range("T47") = False

'Disables the checkbox
Sheets("Electronic Form").CheckBoxes("Check Box 24").Enabled = False

End If

End Sub

What I need to do now is loop through U47:U71 and set the Checked State and Enabled State for each where the Default State is true.

I'm hoping to add this to a Private Sub Worksheet_Change(ByVal Target As Range) change event, to avoid having to use a command button. If so, I imagine I would have to clear the checkboxes first.

I would appreciate any input. Thanks

 

[Galley]

My loops seem to be working, as the checkboxes are being selected and deselected as necessary, (based on the selection of the validated list). The code to disable checkboxes is not working, however. It will disable Check Box 24, but will not loop through the rest. I supsect that I need a separate loop for that?

Sub Button52_Click()

Dim i As Long
For i = 1 To 20

'Clears all checkboxes in the "Approval required by" section
ActiveSheet.Range("S47").Offset(i - 1) = False

'Sets the Enabled State to ""
ActiveSheet.Range("T47").Offset(i - 1) = ""

Next i

Dim j As Long
For j = 1 To 20

'Reads the Default State
If Range("U47").Offset(j - 1) = True Then

'Sets the Checked State
Range("S47").Offset(j - 1) = True

'Sets the Enabled State (for conditional formatting purposes only)
Range("T47").Offset(j - 1) = False

'Disables the checkbox
Sheets("Electronic Form").CheckBoxes("Check Box 24").Enabled = False

End If

Next j

End Sub

 

[AlphaFrog]

You could use the same loop if the checkbox names are sequentially numbered 24 to 43

[COLOR=green]'Disables the checkbox[/COLOR]
Sheets("Electronic Form").CheckBoxes([COLOR=#ff0000]"Check Box " & 24 + j - 1[/COLOR]).Enabled = [COLOR=darkblue]False[/COLOR]

 

[Galley]

You could use the same loop if the checkbox names are sequentially numbered 24 to 43

[COLOR=green]'Disables the checkbox[/COLOR]
Sheets("Electronic Form").CheckBoxes([COLOR=#ff0000]"Check Box " & 24 + j - 1[/COLOR]).Enabled = [COLOR=darkblue]False[/COLOR]

WooHoo! Everything is working as intended. I also added the bit of code to the "clear" section, to re-enable all checkboxes.

Yes, all checkboxes are in sequence as I had made several mockups, where I was trying listboxes and checkboxes to find the best method. I decided to use an "Update" and "Clear" button associated with the validated list.

I will next need to read the checkboxes to export the items to the printed form, but I've already got a pretty good idea how I can acomplish that. Thanks so much for your help!