Auto populating check boxes

[cjgeorge]

How do I auto-populate check boxes when an item is selected in a drop-down menu. I have a drop down menu with a list of parts. Each part is tested for specific criteria. In the excel spreadsheet, if there is an error in the part that needs to be checked in the check box it is listed as a 1.

For example: Test 1 Test 2 Test 3 Test 4
Part 1 _______1______1_____0____1
Part 2 _______1______0_____1____1

After my part is selected in the drop down menu, I want check boxes for each test to be checked off. The 1 shows what I want to be checked in the check box, and the zero indicates what I don't want to be checked off in the interface of this program. Can anyone help me???

 

[pbornemeier]

This answer assumes that
--1) by "drop down menu" you meant control bar menu
--2) the checkboxes are on a worksheet
Put this subroutine in a module

Sub SetCheckBoxes(int1 As Integer, int2 As Integer, int3 As Integer, int4 As Integer)
    Sheet1.CheckBox1.Value = int1
    Sheet1.CheckBox2.Value = int2
    Sheet1.CheckBox3.Value = int3
    Sheet1.CheckBox4.Value = int4
End Sub

When Part 1 is selected, add this line of code to the code that is called when you select the part 1 menu item:

SetCheckBoxes 1, 1, 0, 1

When Part 2 is selected, add this line of code to the code that is called when you select the part 2 menu item:

SetCheckBoxes 1, 0, 1, 1

If the 1 and 0 for each part# test pattern is in a workbook, you can use the vlookup function to extract the value for each test for each part rather than hard-coding it.

 

[cjgeorge]

Thanks for the reply. However, we have over 700 parts, so is there a way to not have to manually put all of that into the code??

 

[pbornemeier]

Do you have an excel table that list the parts and the required tests? If so, please use Excel Jeanie (see footer) or describe the table layout. (sheet name, column layout, etc.) Is there only 4 tests?

 

[cjgeorge]

Unfortunately, I am unable to post the worksheets for my data due to confidentiality issues. There are currently 732 modules in the spreadsheet and each module is tested for 8 criteria errors. Column one has a list of the modules and columns 2-9 have the errors to be tested for. Currently every part is tested for every error. I have broken it down so that it is visible on the spreadsheet to see which module needs to be tested for which error, indicated by the 1's and 0's. The idea of the check boxes on the interface is so that when a part number is selected, only the check boxes of the criteria NEEDED to be tested for will be checked. This is so that the person using the interface will be able to look at the screen and know exactly what they are testing for as opposed to the previous method of testing everything on every module. I am sorry that I cannot post the data for this would be much more convenient, but hopefully this explanation will help. Thanks for your time. -John

 

[pbornemeier]

Assuming:
1) Your data (Column one has a list of the modules and columns 2-9 have the errors to be tested) are in a worksheet named 'Module Data' and that columns 2-9 for each module row are populated with ones and zeros
2) There are column headers in row one of that data page
3) A named range of 'ModuleData' has been defined to cover the data in column 1 of that data, either with a fixed definition

='Module Data'!$A$2:$A$733)

or a variable definition (useful if modules are sometimes added)

=OFFSET('Module Data'!$A$2,0,0,COUNTA('Module Data'!$A:$A)-1,1)

4) On Sheet1 there exists a ComboBox control, ComboBox1, with a ListFillRange set to ModuleData
5) On Sheet1 there exists 8 CheckBox controls, CheckBox1 through CheckBox8
6) The following code is placed on the code page for Sheet1

Private Sub ComboBox1_Change()
    Dim lngModuleRow As Long
    lngModuleRow = WorksheetFunction.Match(Sheet1.ComboBox1.Value, Worksheets("Module Data").Range("ModuleData"), 0) + 1
 
    Sheet1.CheckBox1 = Worksheets("Module Data").Cells(lngModuleRow, 2)
    Sheet1.CheckBox2 = Worksheets("Module Data").Cells(lngModuleRow, 3)
    Sheet1.CheckBox3 = Worksheets("Module Data").Cells(lngModuleRow, 4)
    Sheet1.CheckBox4 = Worksheets("Module Data").Cells(lngModuleRow, 5)
    Sheet1.CheckBox5 = Worksheets("Module Data").Cells(lngModuleRow, 6)
    Sheet1.CheckBox6 = Worksheets("Module Data").Cells(lngModuleRow, 7)
    Sheet1.CheckBox7 = Worksheets("Module Data").Cells(lngModuleRow, 8)
    Sheet1.CheckBox8 = Worksheets("Module Data").Cells(lngModuleRow, 9)
End Sub

When a module is selected in ComboBox1, the 8 checkboxes will update based on values in the Module Data worksheet.

You may want to modify each check box's code as follows to emphasize their states:

Private Sub CheckBox1_Change()
    If Me.CheckBox1 = 1 Then
        Me.CheckBox1.BackColor = vbGreen
    Else
        Me.CheckBox1.BackColor = vbRed
    End If
End Sub