Forum Discussion

theaton56's avatar
theaton56
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 the cell link from say $L$4 to L4.  Click on the drag handle and drag it down - no problems.  However, each checkbox sticks to the original Format control cell link reference !  i.e.  I look at the last of the check boxes and under Format Control it is showing L4. 

I have tried on 2 different machines that are running Office 365 and they do the same. 

Any thoughts of where I am going wrong or how to rectify this please ?

 

Many thanks,

 

Tony

 

excel

9 Replies

  • Zack Barresse's avatar
    Zack Barresse
    Iron Contributor
    Apr 30, 2020
    Form controls don't work like that, so what you're asking isn't possible. You have to assign them all manually.
    • theaton56's avatar
      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




      • Zack Barresse's avatar
        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

         

         

Resources