Results 1 to 7 of 7

Thread: Inserting Multiple Checkboxes

  1. #1

    Unhappy Inserting Multiple Checkboxes



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello all,

    I am interested in creating a code that creates checkboxes in pre-selected cells, also aligning them in the center horizontally and vertically, and also offsetting the TRUE/FALSE value away from these cells
    So far i have found something but it will not center the checkboxes

    Any help will be greatly appreciated

    Code:
    Sub CellCheckbox()Dim myCell As Range, myRng As Range
    Dim CBX As CheckBox
    
    
        For Each myCell In Selection
            With myCell
                Set CBX = .Parent.CheckBoxes.Add( _
                            Top:=.Top, _
                            Left:=.Left, _
                            Width:=1, _
                            Height:=1)
                CBX.Name = .Address(0, 0)
                CBX.Caption = ""
                CBX.Left = .Left + ((.Width - CBX.Width) / 2)
                CBX.Top = .Top + ((.Height - CBX.Height) / 2)
                CBX.LinkedCell = .Offset(0, 15).Address(external:=True)
                CBX.Value = xlOff
            End With
        Next myCell
    
    
    
    End Sub


  2. #2

    The width of the Checkbox includes surrounding space

    The problem is this is not a case of what you see is the whole story. If you were to go to the added checkbox and select it while in Designer mode you would see the square image is not the whole of the object. With a little experimentation I found that the magic number to use instead of CBX.Width is 17. The following works for me.
    Code:
    Sub CellCheckbox()
        Dim myCell As Range
        Dim myRng As Range
        Dim CBX As CheckBox
    
        For Each myCell In Selection
            With myCell
                Set CBX = .Parent.CheckBoxes.Add( _
                            Top:=.Top, _
                            Left:=.Left, _
                            Width:=1, _
                            Height:=1)
                CBX.Name = .Address(0, 0)
                CBX.Caption = ""
                CBX.Left = .Left + ((.Width - 17) / 2)    '.Left + ((.Width - CBX.Width) / 2)
                CBX.Top = .Top + ((.Height - CBX.Height) / 2)
                CBX.LinkedCell = .Offset(0, 1).Address(external:=True)
                CBX.Value = xlOff
            End With
        Next myCell
    End Sub

  3. #3
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    374
    Articles
    0
    Excel Version
    2020
    Use cells, formatted font: webdings;
    If you enter 'a' you will see the 'check' symbol.

  4. #4
    This code works

    Code:
    Option Explicit
    Sub insertCheckboxes()
     
      Dim myBox As CheckBox
      Dim myCell As Range
     
      Dim cellRange As String
      Dim cboxLabel As String
      Dim linkedColumn As String
     
      cellRange = InputBox(Prompt:="Cell Range", _
        Title:="Cell Range")
     
      linkedColumn = InputBox(Prompt:="Linked Column", _
        Title:="Linked Column")
     
      cboxLabel = InputBox(Prompt:="Checkbox Label", _
        Title:="Checkbox Label")
     
      With ActiveSheet
        For Each myCell In .Range(cellRange).Cells
          With myCell
            Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, _
              Width:=.Width, Left:=.Left, Height:=.Height)
     
            With myBox
              .LinkedCell = linkedColumn & myCell.Row
              .Caption = cboxLabel
              .Name = "checkbox_" & myCell.Address(0, 0)
            End With
     
            .NumberFormat = ";;;"
          End With
     
        Next myCell
      End With
    End Sub

  5. #5
    Also Find Code to remove checkboxes:

    Code:
    Sub RemoveCheckboxes()
    On Error Resume Next
    ActiveSheet.CheckBoxes.Delete
    Selection.FormatConditions.Delete
    End Sub

  6. #6
    @Microhunt, dont know exactly how to use the code you gave me, any advise would be appreciated

    @snb i need the checkboxes to have the FALSE/TRUE variables in order to add the document into a database which recognizes the values

    @Bigger Don your code works out quite smoothly so far that i tested it, it does centre the boxes in the desired pre-selected cells

    i have 135 forms with hundreds of checkboxes to be inserted, in different locations, so far it progresses possitively

  7. #7
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    374
    Articles
    0
    Excel Version
    2020
    See the attachment
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •