mercredi 23 mars 2016

need to limit checkboxes to one per row using VBA

I am using the following code to create checkboxes next to each row i have. There can be anywhere from 500-2500 rows, so the number of rows need to be dynamic.

I am trying to:

  1. Copy a sheet from one workbook to another workbook
  2. after sheet is copied, add checkboxes next to each row
  3. I am using conditional formatting to strike through all text in a row if K is TRUE, checkboxes are in J.
  4. The problem I am having is, if a row is formatted not identical in height, two checkboxes appear in that row and it affects the subsequent rows.

This is what the code i am using looks like. Please help.

Sub create_new_wb_CHECKLIST()
    Sheets("Jobs by Day").Copy
    Dim ToRow As Long
    Dim LastRow As Long
    Dim MyLeft As Double
    Dim MyTop As Double
    Dim MyHeight As Double
    Dim MyWidth As Double
    LastRow = Range("I20000").End(xlUp).Row
    For ToRow = 2 To LastRow
    If Not IsEmpty(Cells(ToRow, "I")) Then            
        MyLeft = Cells(ToRow, "J").Left
        MyTop = Cells(ToRow, "J").Top
        MyHeight = Cells(ToRow, "J").Height
        MyWidth = MyHeight = Cells(ToRow, "J").Width        
        ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
         With Selection
            .Caption = ""
            .Value = xlOff
            .LinkedCell = "K" & ToRow
            .Display3DShading = False
         End With
      End If
  Next

List item

End Sub




Aucun commentaire:

Enregistrer un commentaire