VBA Syntax Issues with Placing Checkboxes

[phelbin]

I found some nifty code that 90% solves my problem, but I need to tweak it a bit. This code brings up message boxes to define the range and linked column and then places checkboxes. In my situation I want to set the range and linked column and not use the message boxes, but I just can't get the syntax to work.


Any ideas?

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

Thanks!!








BTW, I found it here:
Excel: Adding Checkboxes the Easy Way | Terminally Incoherent

 

[phelbin]

Nevermind...it works now:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Sub AddCheckboxes()

Dim myBox As CheckBox
Dim myCell As Range

Dim cellRange As Range
Dim cboxLabel As String
Dim linkedColumn As String

    Set cellRange = Range("A1:A10")

    linkedColumn = "D"

    cboxLabel = "MyCheckbox"

    With ActiveSheet
        For Each myCell In cellRange
            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

</code>

 

[L. Howard]

If you should need to have unique label names, try something like this.

Howard

Sub AddCheckboxes()

Dim myBox As CheckBox
Dim myCell As Range

Dim cellRange As Range
Dim cboxLabel As String
Dim linkedColumn As String
Dim bxNo As Long

    Set cellRange = Range("A1:A10")

    linkedColumn = "D"

    cboxLabel = "MyCheckbox"
    
bxNo = 0

    With ActiveSheet
        For Each myCell In cellRange
            With myCell
                Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, _
                                                   Width:=.Width, Left:=.Left, Height:=.Height)

                With myBox
                    .LinkedCell = linkedColumn & myCell.Row
                    .Caption = cboxLabel & bxNo + 1
                    .Name = "checkbox_" & myCell.Address(0, 0)
                   
                End With

                .NumberFormat = ";;;"
            End With
            
bxNo = bxNo + 1

        Next myCell
    End With
    
End Sub