Link multiple Active X check-boxes with multiple cells

**GPetcu** · 01-08-2020, 06:29 AM

Hello everyone

After some Excel wisdom from someone more experienced than I am.

I have created a spreadsheet (attached) that my team will be using to records lights that have failed in our office building. As the team is not technically inclined I am trying to make it as easy as possible for them to record these.

For each office building there will be a monthly test for lights that needs to be recorded. For ease, I have created multiple checboxes that should be ticked when a light failed. Whilst I have done the first month by manually linking the checkbox to the cell next to it (I am doing this so I can insert conditional formation to highlight the row in red when a light failed) I am after a more "fast" method to do this for the rest of the months. I have attached an example sheet for your visibility.

In small words, I am after a vba that will allow me to link multiple checkboxes (within a defined range) to the cells in the next row. It would make more sense when you will see the document (there is a comment in cell W1).

Thank you for your assistance in advance.

Gabriel P.

**ByteMarks** · 01-09-2020, 07:40 AM

You could try this:
I've referred to the checkboxes by name as they appear to be numbered sequentially and you only want changed the linked cell for some of them.
The row of the linked cell is determined by the position of the checkbox.

Sub LinkCheckBoxes()
Dim chk As OLEObject
Dim iLinkOffsetCol As Integer
Dim iChk As Integer
iLinkOffsetCol = 3  'number of columns to the right for link. 3 = col Z
For iChk = 107 To 210 'most recent checkboxes
    Set chk = ActiveSheet.OLEObjects("Checkbox" & iChk)
    With chk
        .LinkedCell = .TopLeftCell.Offset(0, iLinkOffsetCol).Address 'reassign the link
        .Object.Value = False 'untick the box
    End With
Next
End Sub

Link multiple Active X check-boxes with multiple cells

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Link multiple Active X check-boxes with multiple cells

Re: Link multiple Active X check-boxes with multiple cells

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to link multiple active x checkboxes to cells

[SOLVED] Reading Multiple Check boxes on multiple Sheets

Filtering cells with multiple check boxes and/or radio buttons

[SOLVED] HELP, formula for selecting multiple active x check boxes

[SOLVED] Creating a macro to create multiple check boxes linked to different cells

I need a code for presence check to check multiple text boxes and combo boxes

single group of code to reference multiple sequential names of active x check boxes

Bookmarks

Bookmarks

Posting Permissions