ListBox内の並び替えで実行エラー

質問

OSはXP、
Excelは2003を使用しています。

ユーザーフォーム内のListBox内で、コマンドボタンをクリックして行を上や下に並び替えたく、
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1041407835
を参考にして、下記の通りに組んだのですが、
下に並び替えるCommandButton2を実行時や、
上に並び替えるCommandButton1を「２回目」に実行時に
「実行時エラー　-2147417848(80010108)　
オートメーションエラーです。
起動されたオブジェクトはクライアントから切断されました。」
となってしまいます。

Private Sub CommandButton1_Click()
Dim i As Variant
Dim j As Integer
Dim k As Integer
Dim myBuf1 As Variant
Dim myBuf2 As Variant

' 選択されている項目を１つ上げる。
i = Me.ListBox1.ListIndex
If i > 0 Then
For j = 1 To 7
myBuf1 = Me.ListBox1.List(i - 1, j)
Me.ListBox1.List(i - 1, j) = Me.ListBox1.List(i, j)
Me.ListBox1.List(i, j) = myBuf1
Me.ListBox1.Selected(i - 1) = True
For k = 0 To 6
myBuf2 = Me.ListBox2.List(i - 1, k)
Me.ListBox2.List(i - 1, k) = Me.ListBox2.List(i, k)
Me.ListBox2.List(i, k) = myBuf2
Me.ListBox2.Selected(i - 1) = True
Next k
Next j
End If
End Sub

Private Sub CommandButton2_Click()

Dim i As Variant
Dim j As Integer
Dim k As Integer
Dim myBuf1 As Variant
Dim myBuf2 As Variant

' 選択されている項目を１つ下げる。
i = Me.ListBox1.ListIndex
If i < Me.ListBox1.ListCount - 1 Then
For j = 1 To 7
myBuf1 = Me.ListBox1.List(i + 1, j)
Me.ListBox1.List(i + 1, j) = Me.ListBox1.List(i, j)
Me.ListBox1.List(i, j) = myBuf1
Me.ListBox1.Selected(i + 1) = True
For k = 0 To 6
myBuf2 = Me.ListBox2.List(i + 1, k)
Me.ListBox2.List(i + 1, k) = Me.ListBox2.List(i, k)
Me.ListBox2.List(i, k) = myBuf2
Me.ListBox2.Selected(i + 1) = True
Next k
Next j
End If
End Sub

説明不足や上記の記述にとんちんかんな間違いがありましたら、ごめんなさい。
どなたか解決方法を教えて頂けますようお願い致します。

回答No.2

cj_mover

こんにちは。＃１、ｃｊです。
ちょっと具合悪くてお休みしてました。遅くなってすみません。

＃１お礼欄、確認しました。
残念ながら、エラーの原因を特定するには至りませんでしたが、
＃１のコードが【原因】でエラーが出るとは考えにくいので、
　【原因】は他にある。＃１のコードは【きっかけ】に過ぎない。
ということは言えそうです。
そうなると、これまでに提示された情報に含まれない部分が
エラーの【原因】ということになるかと思います。

比較的可能性が高いと思われるのは、
同一のUserFormモジュールに
　Private Sub ListBox2_Change()
　Private Sub ListBox2_Click()
などがあって、そちらが悪さしている場合です。
　　.List(nTgIdx - 1, i) = .List(nTgIdx, i)
の行では、_Changeイベント、
　　.Selected(nTgIdx - 1) = True
の行では、_Changeイベントと_Clickイベントが呼び出されます。
もし、これらのイベントプロシージャがある場合は、
一旦、それらをコメントブロックしてから試行して、
結果に違いが出るか確認してみましょう。
それで解決が見えて来たなら以下は読まなくてもいいですが、
依然解決できない場合は以下、、、。

さて、どうやって【原因】を特定するか、という話を書いてみます。

因みに、私自身は、
　「実行時エラー　-2147417848(80010108)　
　オートメーションエラーです。
　起動されたオブジェクトはクライアントから切断されました。」
というエラーの経験はあります。
しかし、UserFormとWorksheetだけで、この手のエラーを見たことはありません。
（経験したのはADOとかWebBrowserとかMSWordなどの外部オブジェクトをExcelVBAから操作するのに
　記述を誤った時（デバッグ前）だったと思います）
そこで、何とか再現できないか、時間を掛けて試行錯誤してみましたが、
私の想像力では無理みたいです。
つまり、今の処、「経験なし」「再現できず」ということになります。
期待していたのならごめんなさい。
代わりに、そんな時私自身はどうするか、という話を書いておきます。

基本的には、通常通りデバッグして、【実際に】何処でエラーになっているか確認を試みます。
　　F8　　　　　　　　　ステップイン
　　Ctrl+ F8　　　　　　カーソル行の前の行まで実行
　　F9　　　　　　　　　ブレークポイントの設定・解除
などを駆使して、ローカルウィンドウ、イミディエイトウィンドウを見れば、
問題点を見つけられることが多いです。
参考に
　VBA基本(デバッグトレースの開始方法)
　http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_030.html
スグには身につかないかも知れませんが、ＶＢＡを扱うなら必須ですので、
今回は無理でも、少しずつ、覚えるようにしてみてください。
イベントプロシージャを扱う時などは特に、
プロシージャでの様々な処理をトリガーにして
他のイベントプロシージャを実行していることに気が付かないこと、もありますから、
実際の処理の流れを確認するだけでも必ず役に立つと思います。

んで、それでもやっぱり見当が付かない場合は、仕方なく、
「入口から出口までひとつひとつ、特異な設定を疑いの目で確認してゆく」
ということになります。

因みに、＃１で提示したコードについては、
　新規のListBoxふたつとCommandButtonふたつをデフォルトのまま用意して
　UserForm読み込み時にListBox1とListBox2に(行数が同じになるように)直接値を設定するようにして
　（.Listプロパティ、.Columnプロパティ、AddItemメソッド、の何れかを用いてList作成）
　UserForm.Show　を実行して
　ListBox1の何れかの行を選択して
　CommandButton1またはCommandButton2をクリック
以上を、作成した環境で、間違いなく、他に余計なことは何もせず、過不足ないコードで、実行する限りでは
どうやってもエラーが出せませんし、ふたつのListBoxのListが正しく並べ替えされます。
それぞれ５０行のサンプルで、全行１００往復以上テストしました。
#　.Selected(nTgIdx - 1) = True
#の部分は本来
#　.ListIndex = nTgIdx - 1
#と書くのが普通ですが、どちらがニーズに合っているのか、こちらで判断できなかったので
#そのまま.Selectedプロパティにしました（試しに書き換えてもいいですが解決策にはならないと思います）。
#その点を除けばかなり保守的な書き方をしたつもりです。

本題ですが、チェック項目として
　１◆Book作成時と現在の実行環境で Application.Version に相違はないか？
　２◆UserFormデザイン時のListBoxプロパティ設定で変更点（.RowSourceや.ControlSourceとか）はないか？
　３◆UserForm読み込み（_Initializeや_Activateなど）時にListBoxのプロパティ設定を変更してないか？
　４◆ListBox1とListBox2のList行数に相違はないか？
　５◆ListBox2を他のListBox（ListBox3とか）と思い違いしていないか？
　６◆CommandButton1_Click CommandButton2_Clickに他の記述を加えていないか？
　７◆CommandButton1 CommandButton2 に他のイベントプロシージャを設定していないか？
　８◆ListBox2に_Changeイベントや_Clickイベントを設定してないか？（前述）
　９◆ListBoxに.ControlSourceプロパティを設定している場合、リンク先シートでWorksheet_Changeを設定してないか？
　◆全体として、同じ処理を別の場所で２度または複数回意図せず繰り返しているような構造になっていないか？
他にも確認すべき事柄があるとは思いますが、おおよそ、こんな感覚で、
とにかく疑ってみる、疑わしいポイントをさぐる、ということです。
エラーの【原因】がある、としたら、【原因】は、ひとつではないような気もしています。
もし、以上のチェックについての答えがすべて「ない」ということだと、私としてはお手上げです。
その場合私なら、【破損】と結論付けて、UserFormを一から作り直すとか、試しにエクスポートしてみるとか、ですかね。

一応少し補足を。
・ListBoxに.RowSourceプロパティを設定した場合、並べ替えは当然エラーになります。
　もっとも通常は、今回問題のエラーとは別のエラーですが。
・＃１の
　　With ListBox2
　　　　For i = 0 To .ColumnCount - 1
　　　　　　myBuf = .List(nTgIdx - 1, i)
　　　　　　.List(nTgIdx - 1, i) = .List(nTgIdx, i)
　　　　　　.List(nTgIdx, i) = myBuf
　　　　Next i
　　　　.Selected(nTgIdx - 1) = True
　　End With
　これ、２回（偶数回）実行すると、見た目上、並べ替えしていないように見えます。
　奇数回実行すると、１回並べ替えした時と同じように見えます。
・イベントプロシージャについては一般的に、例えば、
　ListBox2_Changeイベントで、普通にListBox2.Valueを変更すると（間接的に変更された場合も）、
　再帰的にListBox2_Changeイベントが（理論上）無限に繰り返されることになります。
　都度都度、再描画するようなオブジェクトだと、このような場合、どんなエラーが出ても不思議ではないです。

以上、ヒント、にもなっていないかも知れませんが、、、。
【原因】をこちらから見ることが出来ませんので、どうしても
質問者さんの方で、ある程度選んだ情報を追加提示できないと、遠隔での解決は難しいです。

とりあえず、解決に近づくことを祈念しながら、待ってみますね、

回答No.1

cj_mover

こんにちは。
意図を汲み取れていない点もあるかもしれませんが
試してみて期待通りでなければ、どう違うか言ってくださいね。

　ListBox1選択中の行を１つ上（下）の行と並べ替える
　同時にListBox2も連動して同じ位置の行を並べ替える
　但し、ListBox1の１列目だけは並べ替えをしない

' ' ==================================================

Private Sub CommandButton1_Click() ' okg7818134
　　Dim nTgIdx As Variant
　　Dim myBuf As Variant
　　Dim i As Integer

' ' 選択されている項目を１つ上げる。
　　nTgIdx = ListBox1.ListIndex
　　If nTgIdx < 1 Then Exit Sub

　　With ListBox1
　　　　For i = 1 To .ColumnCount - 1
　　　　　　myBuf = .List(nTgIdx - 1, i)
　　　　　　.List(nTgIdx - 1, i) = .List(nTgIdx, i)
　　　　　　.List(nTgIdx, i) = myBuf
　　　　Next i
　　　　.Selected(nTgIdx - 1) = True
　　End With

　　With ListBox2
　　　　For i = 0 To .ColumnCount - 1
　　　　　　myBuf = .List(nTgIdx - 1, i)
　　　　　　.List(nTgIdx - 1, i) = .List(nTgIdx, i)
　　　　　　.List(nTgIdx, i) = myBuf
　　　　Next i
　　　　.Selected(nTgIdx - 1) = True
　　End With

End Sub

' ' --------------------------------------------------

Private Sub CommandButton2_Click() ' okg7818134
　　Dim nTgIdx As Variant
　　Dim myBuf As Variant
　　Dim i As Integer

' ' 選択されている項目を１つ下げる。
　　nTgIdx = ListBox1.ListIndex
　　If nTgIdx = Me.ListBox1.ListCount - 1 Or nTgIdx = -1 Then Exit Sub

　　With ListBox1
　　　　For i = 1 To .ColumnCount - 1
　　　　　　myBuf = .List(nTgIdx + 1, i)
　　　　　　.List(nTgIdx + 1, i) = .List(nTgIdx, i)
　　　　　　.List(nTgIdx, i) = myBuf
　　　　Next i
　　　　.Selected(nTgIdx + 1) = True
　　End With

　　With ListBox2
　　　　For i = 0 To .ColumnCount - 1
　　　　　　myBuf = .List(nTgIdx + 1, i)
　　　　　　.List(nTgIdx + 1, i) = .List(nTgIdx, i)
　　　　　　.List(nTgIdx, i) = myBuf
　　　　Next i
　　　　.Selected(nTgIdx + 1) = True
　　End With

End Sub

' ' ==================================================

以上のように差し替えて実行して、結果が
お望み通りか、どのように違うか、
しらせてください。

確認事項
　◆.Listのインデックスは必ず０から始まります。
　対して.ListCountや.ColumnCountは必ず１から数えた総数を返します。
ご提示の
> For j = 1 To 7
２列目から８列目に対して処理をしています。
つまり１列目は並べ替えしないという意味になっていますが、
これはお望み通りなのでしょうか。
もし、そうではなくて行全体を並べ替えたいということでしたらば、
上記、私が提示したコードの
　　　　For i = 1 To .ColumnCount - 1
を
　　　　For i = 0 To .ColumnCount - 1
と書き換えてください。
　◆同様に
ご提示の
> For k = 0 To 6
１列目から７列目に対して処理をしています。
でも、
　ListBox2にはより多くの列があって、その内の１列目から７列目だけを処理する
という意味なのか
　ListBox2には７列あって全ての列に対して処理する
という意味なのか
量りかねました。
なので、「１（２）列目から最終列まで」を対象に処理する
という意味で
上記、私が提示したコードでは
　　　　............ To .ColumnCount - 1
という書き方をしています。
直値で指定し直したほうがわかり易いようでしたら
　　　　............ To 7
　　　　............ To 6
などのように
ListBox1、ListBox2、共に適宜指定し直してください。お任せします。
　◆ご提示のコードで難解な点。
> For j = 1 To 7
　　' ListBox1 に対する処理
　　' ...
> For k = 0 To 6
　　' ListBox2 に対する処理
　　' ...
> Next k
> Next j
「ListBox2 に対する処理」が都合４９回行われるようになっていますが
この点は間違いなのかと思います。エラーの原因にも関わっていると思います。
たまたま４９が奇数なので１回の処理と同じ結果になると思いますが、
何か意図があるのでしょうかね？
さしあたり、
　For j = 1 To 7
　　' ListBox1 に対する処理
　　' ...
　Next j
　For k = 0 To 6
　　' ListBox2 に対する処理
　　' ...
　Next k
という意図で書かれたもの、という前提でお応えしました。

　◆.ListIndexはListBox未選択の場合-1を返します。
ご提示の
> If i < Me.ListBox1.ListCount - 1 Then
では未選択の場合も処理に進んでしまいますから
当然エラーになります。
こうした理由で私が提示したコードでは条件分岐を書き換えています。
　◆エラーの原因については
特定出来てはいませんが、エラーの原因となる点を幾つか対処していますので
あとは、実際の環境でどうなるかですね。

とりあえず以上です。

お礼

cj_mover様

ご回答頂きありがとうございました。
そしてお礼と返答が遅くなってしまって申し訳ありません。

色々説明不足だったにも関わらず丁寧に解説して頂き感謝してます。

>ListBox1選択中の行を１つ上（下）の行と並べ替える
>同時にListBox2も連動して同じ位置の行を並べ替える
はい、その通りです。

>但し、ListBox1の１列目だけは並べ替えをしない
1列目はデータがないのです。

>◆.Listのインデックスは必ず０から始まります。
>つまり１列目は並べ替えしないという意味
>ListBox2には７列あって全ての列に対して処理する

行全体を並べ替えたいのですが、
ListBox1は、1列目～8列目までありますが、1列目にはデータが入っていないので、
（同じファイル内の他のユーザーフォームとの兼ね合いで1列目は空いてます。)
1列目を含むと「Listプロパティを設定できません」のエラーになってしまいます。
それで、ListBox1は1から始めるforで設定していました。
ListBox2は1列目～7列目まであり、どの列にもデータが入っています。
書いて下さったコードのままの
ListBox1→i = 1 To .ColumnCount - 1
ListBox2→i = 0 To .ColumnCount - 1
で実行させて頂きました。

☆CommandButton2
まず、2行目を1行目に1つ上げる、や、
3行目を2行目にあげて、更に続けて1行目に上げる、
はエラーにはなりませんでしたが、
ListBox2の方が連動して繰り上がってない状態でした。

☆CommandButton1
次に1行目を1行下に下げるや
2行目を3行目に下げるをを実行しますと、
> With ListBox2
> For i = 0 To .ColumnCount - 1
> myBuf = .List(nTgIdx + 1, i)
>.List(nTgIdx + 1, i) = .List(nTgIdx, i)
>.List(nTgIdx, i) = myBuf
>Next i
>.Selected(nTgIdx + 1) = True　←-----ココです
>End With
「←-----ココです」のところで、
「実行時エラー　-2147417848(80010108)　
オートメーションエラーです。
起動されたオブジェクトはクライアントから切断されました。」
の、エラーメッセージがでます。
そして、ListBox2の方の並べ替えがされていない状態です。

引き続きご教授いただけましたら有り難いです。
よろしくお願い致します。