Linking Checkmarks across multiple sheets, one workbook

[ahurst105]

Hi there,

I have a form that I run for a customer every month that uses checkmarks to denote the specific stores, of which there are 4. This form is used to display the deals being run at these grocery stores for each product designation (granola bars, cereal, etc.). I send these over in one workbook and some months there might be 15 tabs and others there could be 30, it just depends on how many deals we're offering. What I've been doing is setting up my multi-sheet workbook and then changing the checkmarks and copying the workbook. This is really tedious, especially when there are 30 sheets of checkboxes I have to check x 4 stores. I have the below piece of code that I found where you can link a checkbox so that when it's clicked in the first sheet it'll be checked across the next designated sheets, but it's set up for just one sheet and I need code for multiple sheets that vary in count.

Option Explicit

Private Sub CheckBox1_Click()
If CheckBox1 Then
Sheet2.CheckBox1 = True
ElseIf Not (CheckBox1) And Not (CheckBox2) Then
Sheet2.CheckBox1 = False
End If
End Sub

Does my request make sense? I basically want the Sheet2 to reflect a range of potentially endless sheets. Can someone please modify this and help? I'd send an example but it's confidential information on the form.

Thanks! (using Excel 2010)

[AlphaFrog]

When you check\uncheck CheckBox1, this will loop through all the other worksheets. If they have a CheckBox1, it will set it to the same checked or unchecked setting.

Private Sub CheckBox1_Click()
    Dim ws As Worksheet
    On Error Resume Next
    For Each ws In Worksheets
        If Not ws Is Me Then
            ws.OLEObjects("CheckBox1").Object.Value = Me.CheckBox1.Value
        End If
    Next ws
End Sub

Is that what you want?

[ahurst105]

This is perfect!! I'm assuming if I have multiple checkboxes I could just repeat this and rename the checkbox, yes?

[AlphaFrog]

Yes. .