Convert sub Insert COLUMN of checkboxes to Insert ROW of checkboxes

[AusSteelMan]

Hi all,

I found the following code that inserts a single column, user entered range of checkboxes. (It works very well and saves much time for those interested)

Sub insertCheckboxes_OnCentre()
'Found via Chandoo.org at:
'http://www.terminally-incoherent.com/blog/2008/09/04/excel-adding-checkboxes-the-easy-way/
 
'modified to position the checkbox close to on centre horizontally of the cell
'with a width of 35 pixels and height of 17 pixels (standard height)
 
  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 - (.Height / 6), _
            Width:=.Width, Left:=.Left + (.Width / 5.5), Height:=.Height / 4)
 
        With myBox
          [COLOR=red].LinkedCell = linkedColumn & myCell.Row[/COLOR]
          .Caption = cboxLabel
          .Name = "checkbox_" & myCell.Address(0, 0)
        End With
 
        .NumberFormat = ";;;"
      End With
 
    Next myCell
  End With
End Sub

As shown the LinkedCell is a concatenation of the user enter Column letter and the current row number (myCell.Row)

What I would like to do is insert a ROW of checkboxes. So I modified the code to this:

Sub insertCheckboxes_OnCentre_InRows_FixedColAndRow()
'Found via Chandoo.org at:
'http://www.terminally-incoherent.com/blog/2008/09/04/excel-adding-checkboxes-the-easy-way/
 
'modified to position the checkbox close to on centre horizontally of the cell
'with a width of 35 pixels and height of 17 pixels (standard height)
 
'modified (trying too!!) to produce checkboxes in rows instead of columns
 
  Dim myBox As CheckBox
  Dim myCell As Range
  Dim cellRange As String
  Dim cboxLabel As String
  Dim linkedRow As Integer  [COLOR=red]'have also tried this as String, but did no work either[/COLOR]
 
  cellRange = InputBox(Prompt:="Cell Range", _
    Title:="Cell Range")
 
  linkedRow = InputBox(Prompt:="Linked Row", _
    Title:="Linked Row")
 
  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 - (.Height / 6), _
            Width:=.Width, Left:=.Left + (.Width / 5.5), Height:=.Height / 4)
 
        With myBox
          [COLOR=red].LinkedCell = Cells(linkedRow, myCell.Column)[/COLOR]
          .Caption = cboxLabel
          .Name = "checkbox_" & myCell.Address(0, 0)
        End With
 
        '.NumberFormat = ";;;"
      End With
 
    Next myCell
  End With
End Sub

Prodincing the actual checkboxes in rows is working ok.

The problem is in generating the linked cell address. I obliviously cannot concatenate the row number and column number directly as it makes no sense. The

myCell.Column

gives the correct column number, but I need the letter so it work (or I need more knowledge on using the column number, either way)

So I tried using Cells(Row, Col) as shown above but still get LinkedCell="" (seen while stepping through code).

Now I don't think I know where to go. I appreciate any help anyone can provide to help me out.

Much thanks,
Darren

 

[AusSteelMan]

I fixed it.

        With myBox
          .LinkedCell = Cells(linkedRow, myCell.Column)[COLOR=red].Address[/COLOR]
          .Caption = cboxLabel
          .Name = "checkbox_" & myCell.Address(0, 0)
        End With

Thanks to everyone who considered this problem anyway.

I'm still happy to take any improvement suggestions.

Regards,
Darren