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:
- Copy a sheet from one workbook to another workbook
- after sheet is copied, add checkboxes next to each row
- I am using conditional formatting to strike through all text in a row if K is TRUE, checkboxes are in J.
- 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