Inserting Multiple Checkboxes

P

punfs

New member
Joined
Nov 14, 2014
Messages
2
Reaction score
0
Points
0
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: 
[/COLOR]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[COLOR=#333333]
 
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
 
Use cells, formatted font: webdings;
If you enter 'a' you will see the 'check' symbol.
 
This code works

Code: 
 [/COLOR]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[COLOR=#333333]
 
Also Find Code to remove checkboxes:

Code: 
 [/COLOR]Sub RemoveCheckboxes()
On Error Resume Next
ActiveSheet.CheckBoxes.Delete
Selection.FormatConditions.Delete
End Sub[COLOR=#333333]
 
@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
 
See the attachment
 

Attachments

  • __true_false snb.xlsb
    7.9 KB · Views: 19
You must log in or register to reply here.
Back
Top