Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox and spinbutton causing object disconnected error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox and spinbutton causing object disconnected error
Just out of curiosity - why would clicking on the spin button trigger the
combobox_change event?? - as seems to be the case here? "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox and spinbutton causing object disconnected error
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automation Error: Object Disconnected From Clients | Excel Programming | |||
Error using SaveAs (object disconnected from clients!) then XL Cra | Excel Programming | |||
help please!!! error "object invoked has disconnected from its cli | Excel Programming |