Forum Discussion

Copper Contributor

Apr 30, 2020

Excel 365 Checkboxes

Afternoon all, I wonder if someone could assist me with what I hop is a glitch with me ! Inserting a checkbox is fine, assigning the Format Control to a cell to the right of it is fine. Modifying t...

excel

theaton56

Copper Contributor

Apr 30, 2020

Spoiler

Thanks Zack, had a nasty feeling that may be the case but was hoping otherwise.

That's is going to be a pain in the posterior and quite time consuming.

Oh well, the joys of excel.

Tony

Thanks Zack, had a nasty feeling that may be the case but was hoping otherwise.That's is going to be a pain in the posterior and quite time consuming.Oh well, the joys of excel.Tony

Zack Barresse

Iron Contributor

Apr 30, 2020

Sounds like there may be copious amounts of checkboxes? If so, you can always just use a little bit of VBA to do it automatically. 🙂

Sub LinkCheckboxes()
    
    Dim CheckBoxItem As CheckBox
    
    For Each CheckBoxItem In ActiveSheet.CheckBoxes
        CheckBoxItem.LinkedCell = CheckBoxItem.TopLeftCell.Offset(0, 1).Address
    Next CheckBoxItem
    
End Sub

theaton56
Copper Contributor
Apr 30, 2020
Thanks Zack,

I will give that a try, was hoping not to use VBA as the end user may screw it up !

Will let you know tomorrow how I get on,

Tony
- Zack Barresse
  Iron Contributor
  Apr 30, 2020
  Yeah, I hear you. In this case it's a one-time use. Run the code once, link checkboxes, remove code. Or just do it by hand. It's definitely a downside to using forms checkboxes. Good luck!
  - theaton56
    Copper Contributor
    May 01, 2020
    Hi Zack,
    
    Must be having a mind melt at the moment. I'll be honest, I am not versed yet in VBA and not sure where I am going wrong.
    
    I copied your code into a new module in the excel sheet and hit F5 to run it but not a lot happened.
    
    I'll try a totally blank sheet and see what happens from there.
    
    Thanks,
    
    tony