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
"Zarch" wrote:
> 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
|
Similar Threads
