dynamically creating check boxes

  • Thread starter Thread starter Erin
  • Start date Start date
E

Erin

I want to have a column in my spreadsheet that contains a
check box. I need to dynamically create this once the
spreadsheet is populated from the database. I am doing
this by copying a dummy one I have on a hidden sheet with
the following code:

Sub CreateCheckBoxes()
NumRows = Range("B4").CurrentRegion.Rows.Count + 4
For x = 5 To NumRows
Sheets("Input Data").Shapes("chkView").Copy
Range("J" & x).Select
ActiveSheet.Paste
Selection.Name = "chkView" & Range("A" & x).Value
Next x
End Sub

I want the check box to be in each row of the J column.
But, it doesn't place it here.
Anyone know how to force the check box to do into a
particular cell without hard-coding the position
coordinates?
 
You say without hard code the position settings, but why not? Why struggle?

Sub CreateCheckBoxes()
NumRows = Range("B4").CurrentRegion.Rows.Count + 4
For x = 5 To NumRows
Sheets("Input Data").Shapes("chkView").Copy
Range("J" & x).Select
ActiveSheet.Paste
Selection.Name = "chkView" & Range("A" & x).Value
With Range("J" & x)
selection.Top = .Top
selection.Left = .Left
' and possibly
'selection.Height = .Height
'selection.Width = .Width
End With
Next x
End Sub
 
Your solution is perfect, and just what I needed to do.
And I don't consider this hard-coding at all, since it is
dynamically placed at the cell coordinates of the
appropriate cell.

THANKS!
 
Back
Top