Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automation Error: Object Disconnected From Clients Sprinks Excel Programming 1 October 24th 06 08:30 PM
Error using SaveAs (object disconnected from clients!) then XL Cra Philip Excel Programming 1 May 12th 05 12:05 AM
help please!!! error "object invoked has disconnected from its cli Valeria Excel Programming 0 November 25th 04 09:49 AM


All times are GMT +1. The time now is 05:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"