0

I am trying to iterate through activex checkboxes. If the checkbox is checked, it needs to move the information from that line to a different sheet (sub COMPLETE) and then move the lines below up. I am getting a run-time error 1004 on the second time through at the Set cb. How can I fix this?

Private Sub cmbupdate_Click()
Dim i As Long
Dim cb As Object


i = 1
Do While i < 99
    Set cb = ActiveSheet.OLEObjects("CheckBox" & i).Object
    If cb.Value = True Then
        Range("A" & (i + 3), "K" & (i + 3)).Select
        Selection.Copy
        COMPLETE
        Selection.ClearContents
        Range("A" & (i + 3)).Select

        ActiveCell.Offset(1).Select
        Orders.Range(Selection, Selection.Offset(, 10)).Select
        Orders.Range(Selection, Selection.End(xlDown)).Select
        Selection.Cut Range("A" & (i + 3))
        Range("A5", "K101").Select
        AddBorder
        Range("A4").Select
        cb.Value = False
        i = i
    Else
        i = i + 1
    End If
Loop

End Sub
PJ W.
  • 1
  • 3
  • 2
    It will help your VBA experience if you review this post and try to incorporate the guidelines from the answers there: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Tim Williams Oct 24 '19 at 17:26
  • Or put a break point (F9) and start debugging with F8, seeing which worksheet is not being selected. Then write `Worksheet("Name").Select`. Make sure it works. Then try to remove the `Select` and `Activate`, by writing explicitly `Worksheet("NeededName").Range(Range("A" & (i + 3))` in front of any `Range()` in the code. – Vityata Oct 24 '19 at 17:30
  • Have you tried stepping through your code to see what happens? I notice the following: `i = i`. Why go through the loop again with the same value of `i`? it's certainly an unusual thing to do – cybernetic.nomad Oct 24 '19 at 17:31
  • I have been stepping through and watching the locals window, that's how I found that the error is popping at the Set cb line the second time through. I just realized that the i = i won't work for what I want to do. – PJ W. Oct 24 '19 at 17:35

0 Answers0