Hi All,

I am tasked to generate report templates in excel and I am new to excel, VBA and still learning.

A little more information on my task, the information for the report will be extracted from different sheets in the workbook which contains a whole list of calculation and description thus, within the same excel file I have created another sheet, sheet2 with the report format and linked the necessary information needed from sheet1.

In sheet1, there are checkboxes(control form) that states the type of materials used and upon being checked, the material ID should be reflected in sheet2 in a table format and the total number of rows for the table should be dependent on the total number of ticked checkboxes. I would like to know how should I go about checking the total number of checkboxes ticked to set the total number of rows for the report table and reflect the material ID?


You can use a loop to scroll through the check boxes. The code below can be modified, just change the frm.Controls to your workbook name...

Dim Control As Control, xCount As Integer

xCount = 0

For Each Control In frm.Controls
       If TypeOf Control Is Checkbox Then
           xCount = xCount + 1
       End If

Next Control