Excel -- checkboxes

ExpExchHelp
ExpExchHelp used Ask the Experts™
on
Experts:

Please find attached a sample spreadsheet that utilizes "check boxes" (with their controls linked to associated cells).

I need to find another way that allows me to use checkboxes; however, I don't want to manually update the controls/links for hundreds of checkboxes.

Do you know of another way to mimic this process?   Again, please find attached XLS for more details on the problem/requirement.

Thanks,
EEH
Check-Box-Formulas.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What do you want to "manually update" and would a macro solution be OK?
ExpExchHelpAnalyst

Author

Commented:
Martin:

I'd like to copy/paste row 1 and update, e.g., rows 2 through 300 with checkboxes which have individual controls (vs. all of them linked to the same cell).

EEH
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Sub AddCheckboxes()
Dim lngRow As Long
For lngRow = 2 To 300
    Range("A1:H1").Copy
    Range("A2:H300").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    With ActiveSheet.CheckBoxes.Add(Cells(lngRow, "C").Left + 3, Cells(lngRow, "C").Top - 1, Cells(lngRow, "C").Width, Cells(lngRow, "C").Height - 1)
        .Caption = "Option " & lngRow
        .LinkedCell = Cells(lngRow, "E").Address
    End With
Next
End Sub

Open in new window

ExpExchHelpAnalyst

Author

Commented:
Wow... that's the PERFECT solution!!!!!   Thousand thanks for it!!!  :)
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you
including these two new ones.
Creating your own Excel Formulas and doing the impossible
A Guide to Writing Understandable and Maintainable VBA Code
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial