Forum Discussion

_hgr_excelhelp's avatar
_hgr_excelhelp
Copper Contributor
Jan 16, 2020

Format ALL checkboxes to link to adjacent cells w/o having to Format Control all individually?

There are 96 spreadsheets. Each spreadsheet has 60 checkboxes. I need to find all of the boxes that were checked and calculate various things from there. But first step's first, link each checkbox with the cell next to them to create TRUE/FALSE values. I understand the latter portion and how to deal with the data once the TRUE/FALSE values are there. What I'm struggling with is how long and tedious it is to link each cell individually with the one next to it.

My current process:

1) Create a new column next to the checkbox cells

2) Right click a checkbox and click Format Control

3) Link to the cell next to it

 

This is taking forever! I feel like I'm not doing this efficiently, and maybe there is a much more straightforward way.

 

I know NOTHING about coding. Is there some code I could use for each spreadsheet that makes it so all the cells link to the cells next to them, without having to do each one individually?

 

Thank you in advance! New to all of this.

excel
Formulas and Functions
Macros and VBA
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jan 16, 2020

    _hgr_excelhelp 

    My example sized the checkboxes at 30 pixels high, starting at (0,0) i.e. top left of the sheet, and with a row height of 30 as well. Play around a little with the parameters to make it match your requirements.

    • _hgr_excelhelp's avatar
      _hgr_excelhelp
      Copper Contributor
      Jan 16, 2020

      Thank you Riny_van_Eekelen! I think this will work. It will be faster to just do this and manually recheck them than having to individually format. Just one question. It looks like the checkboxes skip a line, but the linked cells do not, so they separate. Is there anyway I can adjust the cells to keep them next to each other? (See image below.) Thanks again for your help. 

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Jan 16, 2020

        _hgr_excelhelp 

        My example sized the checkboxes at 30 pixels high, starting at (0,0) i.e. top left of the sheet, and with a row height of 30 as well. Play around a little with the parameters to make it match your requirements.

  • HasarUndusu's avatar
    HasarUndusu
    Copper Contributor
    Sep 02, 2024

    try this code

     

     

     

     

     

     

     

     

     

     

     

     

    Sub CreateCheckboxes()
    Dim cell As Range
    Dim chkBox As CheckBox
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    
    For Each cell In Selection
        ' Calculate the position of the checkbox
        Dim leftPosition As Double
        Dim topPosition As Double
        Dim chkWidth As Double
        Dim chkHeight As Double
        
        
        chkWidth = 15
        chkHeight = cell.Height / 2
        leftPosition = cell.Left + cell.Width / 2 - chkWidth / 2
        topPosition = cell.Top + (cell.Height / 2) - (chkHeight / 2)
        
        ' Add checkbox and set its properties
        Set chkBox = ws.CheckBoxes.Add(leftPosition, topPosition, chkWidth, chkHeight)

        With chkBox
            .Caption = ""
            .Value = xlOff
            .LinkedCell = cell.Offset(0, 0).Address
            .Display3DShading = False
        End With
    ' Hide the value in the linked cell
    cell.Offset(0, 0).Value = "False"
    cell.Offset(0, 0).NumberFormat = ";;;"
    Next cell
End Sub

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    you can see the result, the cells you select is added with checkboxes, and the checkboxes is linked to each of the  cells, and the value(it should be "true" or "false") of the cells is hide. 

    the result

     

    linked to the original cells, and the value of cells is hide

Resources