Combobox and spinbutton causing object disconnected error

[Guest]

Hello

I am wanting to move items up and down the rows in a table by selecting the
item via an index number (from a combobox) and then moving the item up or
down with a spin button, and then renumbering the items into the correct
order.

The sorting and renumbering work fine, selecting the item from the cobmobox
works fine, however when I select the item from the combobox and then try to
move it using the spinne, I instantly get the 'The object invoked has
disconnected from its clients' error.

Can anybody please put me out of my misery? -( or failing that show me where
I am going wrong!?) I have listed a simplified version below which still
generates the error.

Regards and thanks,

Z

Code behind userform

Private Sub ComboBox1_Change()
Range("SortList").Find(ComboBox1).Select ' Select the item in the list
of index numbers
End Sub

Private Sub SpinButton1_SpinDown()
Dim TempVal As Byte
TempVal = ActiveCell.Value
ActiveCell.Value = ActiveCell.Value + 1.1 ' Chenge the index number to
just above the next item's index number
Call ReSortList ' Sort the list into order based on new index numbers
Call ReNumberSequence ' Renumber the items into whole number order
Range("SortList").Find(TempVal).Select ' Sortlist is the column
containing the index numbers
On Error Resume Next ' In case the number is not in the list range
Range("SortList").Find(TempVal + 1).Select
On Error GoTo 0
End Sub

Private Sub SpinButton1_SpinUp()
Dim TempVal As Byte
TempVal = ActiveCell.Value
ActiveCell.Value = ActiveCell.Value - 1.1
Call SortList
Call ReNumberSequence
Range("SortList").Find(TempVal).Select
On Error Resume Next
Range("SortList").Find(TempVal - 1).Select
On Error GoTo 0
End Sub

Sorting and renumbering code:

Sub ReNumberSequence()

Dim CurrentCell
Dim RowNum As Byte

For Each CurrentCell In Range("SortList")
CurrentCell.Value = RowNum + 1
RowNum = RowNum + 1
Next
UserForm1.ComboBox1 = Round(Val(UserForm1.ComboBox1)) '(so don't show
decimal number in combobox)
End Sub

Sub ReSortList()

Application.Goto Reference:="SOrtTable"
Selection.Sort Key1:=Range("G11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("g11").Activate

End Sub

 

[Guest]

Just out of curiosity - why would clicking on the spin button trigger the
combobox_change event?? - as seems to be the case here?

 

[Guest]

OK, I have found the cause. It's down to me being sloppy and not listening to
Stephen Bullen's advice when he says not to use properties that bind controls
to worksheet cells to specify their contents. As I was doing a quick mock up,
I had used the rowsource property to specify the range containing the
dropdown list contents.

In my code, I modified a value in the rowsource range - this triggered the
change event for the bound control, which caused the error.

The fix was to use a boolean flag which is set just before the code that
modifies the value in the rowsource range. This can then be tested in the
change event procedure which is skipped if the flag is set. i.e.

Private Sub SpinButton1_SpinDown()

mbStopEvents = True

' Process code that modifies value in rowsource range here

mbStopEvents = False

End Sub

Private Sub ComboBox1_Change()

If mbStopEvents Then Exit Sub

' Change event code here

End Sub

Regards,

Z