Help in updating a checkbox macro

[AusSteelMan]

Hi everyone,

I would like some help on this modification I'd like to make.

Background:
I'm running a footy tipping comp with some friends and family. I'd like to use checkboxes to aid the speed of entry. I have the following code that inserts checkboxes into a specified range.

Sub insertCheckboxes_InRows_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)
 
'modified to insert in ROWs not COLUMNs
 
 
  Dim myBox As CheckBox
  Dim myCell As Range
 
  Dim cellRange As String
  Dim cboxLabel As String
  Dim linkedRow As Integer
 
  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
          .LinkedCell = Cells(linkedRow, myCell.Column).Address
         ' .Caption = cboxLabel
          .Name = "checkbox_" & myCell.Address(0, 0)
        End With
 
        .NumberFormat = ";;;"
      End With
 
    Next myCell
  End With
End Sub

The mod I'd like to make is to basically remove the user entry options. The checkboxes cellRange should be columns D to S in the currently selected row. Thus, the linkedRow is also the current row.
There is no caption (hence the commenting out of the relevant lines of code).
I started to look at this a couple of weeks ago, but had some urgent real work to do and have not had a chance to get back to this task and the footy starts this weekend.

Any help is much appreciated.

Thanks,
Darren

 

[GlennUK]

First comment out the other user input VBA rows. Then make your loop start like this:

   For Each myCell In .Range("D" & ActiveCell.Row & ":S" & ActiveCell.Row).Cells

and change the linkedcell part to be

.LinkedCell = myCell.Address

 

[AusSteelMan]

Thanks very Glenn. I'm sure I had been close and of course what you did is now oblivious, but I just wasn't good enough to get it. I appreciate your help very much.

Darren

 

[GlennUK]

I'm glad that did what you wanted.