Object invoked disconnected from client

[ZenRaven]

I have a combobox that I move around on a worksheet based on which cell is selected. I have a named range that I use for the control's listfillrange. I update this range based on the value previously selected (the cell directly to the left of current selected). Here is the code I use to update the autofilter and copy the results to another sheet to update the range in the calling sub:

Public Sub UpdateFiltered(sCriteria As String)
Dim wsFiltered As Worksheet
Dim rCat As Range

Set wsFiltered = Worksheets("Filtered")
Set rCat = Range("Categories")

rCat.AutoFilter field:=2, Criteria1:=sCriteria

With wsFiltered
  .UsedRange.Clear
  rCat.Copy Destination:=.Cells(1, 1)
  .Rows(1).Delete
End With

rCat.AutoFilter

End Sub

I also have a command button on my primary sheet that when clicked, inserts a copy of the current selection's row directly below itself. Here's the code for it:

Private Sub btnDuplicate_Click()
Dim c As Integer
Dim r As Integer
Dim wsSel As Worksheet

c = Selection.Column
r = Selection.Row
If Cells(r, 1) = "" Then Exit Sub

btnDuplicate.Enabled = False

Duplicated = True

  cbGLN.Visible = False
  cbCategory.Visible = False

Application.ScreenUpdating = False

Set wsSel = Worksheets("Selections")

With ActiveSheet.Cells(r, c)
  .EntireRow.Copy
  .Offset(1, 0).EntireRow.Insert shift:=xlDown
  .Offset(1, 0).Select
  .Select
End With

With wsSel.Cells(r, c)
  .EntireRow.Copy
  .Offset(1, 0).EntireRow.Insert shift:=xlShiftDown
End With

Duplicated = False

Cells(r + 1, 2).Clear
Cells(r + 1, 4).Clear
Cells(r + 1, 5).Clear

Application.CutCopyMode = False

Application.ScreenUpdating = True

btnDuplicate.Enabled = True

End Sub

Now, I can click the button 100 times with no issues but after I play with the dynamic combobox which updates the autofilter and then click the button, I sometimes get the aforementioned error. It's very strange because it only seems to happen once my combo selections get so many levels deep.

Example:
I start on the 1st line by selecting 2 levels deep. I then use the button to copy that line 20 times. I then pick one of those lines and change the levels to be 6 levels deep. When I click the button, I get the error.

I thought it might have something to do with the number of times the filter was updated but if I keep all rows 2 levels deep (select level 1/level 2, click, new level 2, click, new level2, click... and repeat), no error. The autofilter updates every time a selection is made, btw, because focus is shifted to the next cell after the current level is selected.

Here's the line it dies on:

  .Offset(1, 0).EntireRow.Insert shift:=xlDown

I verified my hunch that the autofilter was causing the issue by bypassing it with the following change. The error never comes up using this.

Sub UpdateFiltered(sCriteria As String)
Dim wsFiltered As Worksheet
Dim rCat As Range
Dim c As Integer
Dim startrow As Integer
Dim endrow As Integer

c = Selection.Column

startrow = c * 10
endrow = startrow + 10

Set wsFiltered = Worksheets("Filtered")

Names.Add Name:="Categories", RefersToR1C1:="=Categories!R" & startrow & "C1:R" & endrow & "C3"

Set rCat = Range("Categories")

With wsFiltered
  .UsedRange.Clear
  rCat.Copy Destination:=.Cells(1, 1)
  .Rows(1).Delete
End With

End Sub

I've looked all over the web at articles and postings for this error and modified my code accordingly but none seem to help. The code I've provided has been reverted back to the original since no other changes yielded any better results.

 

[combo]

I'm not sure if this will help, but you could fully qualify objects. For instance:

Me.btnDuplicate.Enabled = False
instead of
btnDuplicate.Enabled = False

The same for combo boxes if they are on the same sheet, otherwise code names for proper worksheets.

combo

 

[ZenRaven]

Thanks for the suggestion. That was one of the 1st things I tried after scouring other posts for the error. It didn;t affect the behavior at all.

Something interesting I just found out though. I changed

endrow = startrow + 10

to

endrow = startrow + 10 - (c - 6)

in my "bypass filter" test mentioned above to simulate the number of options minimizing with each level and I can reproduce the error if I select down to the very last level on the first line and click the button.

The funny thing about that is that if I select down to the very last level on the first line of the current code, there's no error. It only happens on subsequent rows.

It's almost like I have to go past 5 levels and "bottom out" to no more selections within the row . I'll try to deduce that furthere if I can.

I feel like I'm getting somewhere but I'm still not sure where that somewhere is, lol.

 

[combo]

Are cbGLN and cbCategory real objects or variable names? Do you copy Rows with controls? If so, try to set them to free floating (in design mode format them, properties tab).

combo

 

[ZenRaven]

cbGLN and cbCategory are the actual combobox controls on the sheet. No copying of rows, they just display selection options and then pass the selections to the underlying sheet.

I think I just made some headway. It seems that the error comes up when the filtered list is blank (reached bottom level for the row) and the previous filtered list is less than 3 rows. It's obviously got something to do with the range sizes and in what order they are assigned but I'm still not sure why.

 

[ZenRaven]

I think I just made some headway. It seems that the error comes up when the filtered list is blank (reached bottom level for the row) and the previous filtered list is less than 3 rows. It's obviously got something to do with the range sizes and in what order they are assigned but I'm still not sure why.

Make that less than 4 rows

 

[ZenRaven]

It seems that if at any given time the named range referred to by the listfillrange of my combobox control is less than 3 rows (Filtered!$A$1:$C$2 or Filtered$A$1:$C$1) something happens that is only evident once the button is clicked. Looks like I've isolated the issue but it still doesn't make any sense to me why it happens. Any ideas?

 

[ZenRaven]

I still have no idea why it's acting the way it is but my workaround was to take out this line

  .Rows(1).Delete

which would remove the header from the filtered list and change any code that updated the named range to start on row 2.