For～Nextルーチンで最初の１回しか処理してくれません(Excelマクロ)

ご覧いただきありがとうございます。

Excelで下記のようなマクロを書いたのですが、思ったような処理をしてくれません。色々なWebページや参考書に当たってみましたが、どうしてもわかりません。どの点が間違っているのか、どう直したらよいか、ご教示いただけませんでしょうか。

なお、意図している処理は次のようなものです。
　・セルB2:B21のデータをリストボックスに表示（この部分は別途作成済みです）
　・リストボックスに表示されている項目をユーザーが複数選択する
　・選択後コマンドボタン２をクリックすると、選択された項目を含む行を削除する

以上ですが、選択した項目のうち最初のものだけを削除しただけで終了しています。お手数をおかけいたしますが、よろしくお願いいたします。

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim I As Integer

If ListBox1.ListIndex = -1 Then
MsgBox "選択されていません"
Exit Sub
End If

Dim myStr(19) As Variant
Dim myCell(19) As Variant

With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
MsgBox .List(i)
myStr(i) = .List(i)
Set myCell(i) = Workbooks("PERSONAL.XLS").Sheets(1).Range("B2:B21").Find(myStr(I), , xlValues, xlWhole)
ThisWorkbook.Activate
myCell(i).EntireRow.Delete
End If
Next i
End With

Unload Me
Application.ScreenUpdating = True
End Sub

No.11 回答者： Wendy02 回答日時： 2007/04/12 13:35

こんにちは。

Wendy02です。

トラブルの発生も治まりました。今、試してみたら、全部アップロードできるようです。今回は、オリジナルには整形のインデントはついていますが、全角空白はナシにします。私個人のルールとしては、フリーというには、範囲は超えていますが、今回、私自身が共用できるものであることを条件にしました。しかし、配布をお考えの場合は、アドイン型のほうがよいです。アドイン型は、ThisWorkbookに、少し、手を加えなくてはならないことがあります。（そのときは、また、ここでもよいし、別途お尋ねになってもよいのですが、詳しい人は、あまりいません。また、今後は、COMアドインに移行していく関係で詳しい資料がだんだん少なくなっているようです。）

もし、仕様の段階で、それは話が違うという場合は、アップロードの前にお知らせください。仕様変更します。トラブルがない限りは、近くアップロードします。（確信犯的にアップロードしますので、少なくともWeekDayは避けたいです。）

それをする前に仕様書を書いておきます。

メニュー形式で、基本的には、メニューのヘルプの右となりに出てきます。

メニューは、

・ファイルリスト(L)
[コンボボックス]

　ここをクリックすると、ファイルのリストが出てきます。
並びは降順です。昇順にするオプションはついていませんが、プログラム的には可能です。
もともと、AddItem は、昇順に入るようになっているので、逆さに加えられています。
開けられなかったファイルは、[*]がついて、おそらく次回には消えてしまうはずです。仕様書を書いている時点では、正しく確認は取れていません。

・リストの編集

　UserFormで行っていたものを、テンポラリブック[MyFileList.xls] に出力されて、オプションボタンで処理するようにします。終了は、次のメニューをクリックしてください。手動で終了する場合は、メニューのファイル-閉じるを使わないとできません。

　これは、スクロールロックが掛かっていて、右側が見れないようになっていますが、その右側に、本来のパス付きのフルのファイル名が入っています。単なる取り込みのために、ファイル名が出ているだけで、ファイル名を削除しても、オプションボタンをオフにして、修正しても同じです。見かけだけに過ぎません。

・リストの編集終了

　これは、クリックして、そのブックを閉じれば取り込まれます。なお、そのテンポラリブックは、削除されてしまいます。すべて失った際に、再利用することも考えましたが、手動での取り込みは非常に難しいのでやめました。

・リストの全消去

　リストは、時にごみのように溜まって処理できなくなることを恐れ、全部、クリアにしてしまうオプションを付けました。また、時々、そのようなリクエストも経験的にあります。

・メニューの消去

　このメニュー自体は、テンポラリ属性は、False になっています。つまり、恒久的にメニューに組み込まれるものとされています。(個々のExcel.xlb ファイルの中）
　
ただし、このマクロを取り付けたExcelのPERSONAL.XLSに限ります。ですから、他のExcelでは現れません。場合によって、不必要になったときに、このモードを取り消す方法は、最初は分かっても、時が経つと、作った当人でも、できなくなります。ただ、今回は、特別にTAGを付けてありますので、自分で加工したり調べたりするときに、ひじょうに便利です。
Set CBBox1 = CommandBars.FindControl(, , "CB1")
'CB1' というのが、TAG です。

親のメニューを削除すれば、その子のメニューも消えますが、マクロ画面にしないで削除できるようにしてあります。当然、メニューを復活しても、ファイルリストは消えています。

　なお、私個人は、「固定モード（更新しない）」が必要だと思っています。私の場合は、数種類のものを、常に使っているからです。また、本来は、Outlook を使うと良いのですが、なぜか、Outlook2003 には、そういうOffice ファイル検索モードが見当たりません。

初期設定としては、
Private Const LIMIT_NUM As Integer = 10
ここだけですが、リストの数を設定していただくことになります。これは、表示が10までです。

それから、余談になりますが、マクロのVBAは、今後、どうなっていくのか、はっきりしたことは言えませんが、私個人としては、もうBasic 系はやめて新しい言語の勉強を始めてしまっています。今のところは、MS系からは離れられませんが、MSに振り回されるのに辟易としてしまいました。過去の資料が少ないし、現在の仕様が、その過去のものによるものが大きいのに、資料が手に入らない可能性が強いのです。VBAもそのひとつです。主に、アスキーから、日経になったことが大きな原因です。私は、オークション等でこまめに集めました。しかし、やっていることは前向きではありません。

今回、コードの個々の部分はあまり難しいものではありませんが、コードが集まると、共有変数の問題とか、いろいろ発生して、しばらくやっていないと、まごついてしまうことがありました。それでは、週末までお待ちください。

この回答へのお礼

お世話になります。大変お手数をおかけします。

仕様を拝見しましたが、全く異存はございません。細かい点、後々のことまで配慮していただき、ありがとうございます。また、リストの表示順について書き忘れていたにも関わらず、最新のものが上に来るようにしていただき、大変ありがたいです。

>フリーというには、範囲は超えていますが
おっしゃるとおりで、本来でしたら相応の代価が必要な仕事をしていただいたと承知しております。その点についてはわたしも心苦しく思っております。申し訳ありません。

>なお、私個人は、「固定モード（更新しない）」が必要だと思っています。
今思えば、こちらで質問させていただいたきっかけは、まさにそのような運用を行いたいというものでした。
一定期間同一の業務が続くということがよくあり、その場合、常にリストに残っていてほしいファイルがいくつかでてきます。そういうファイルを残してあとのファイルをリストから複数選択し、For～Nextループで一括消去したい、でもうまく動かないというのが、質問の発端でした。「固定モード」という考え方は、全く発想の外でしたので、質問にはそのようなことは書いておりませんが…
ただ、この点については、「リストの編集」メニューを用意してくださいましたので、充分運用できるのでは、と思っています。

完成したマクロをアップロードしていただけるのを心待ちにしております。よろしくお願いいたします。

お礼日時：2007/04/12 22:16

No.10 回答者： Wendy02 回答日時： 2007/04/11 13:43

Wendy02です。

もう一度、最初からやり直しの決定をしました。理由は二つあって、データの出し入れで、データがなくなるのです。（たぶん、UserFormでも行っても同様のトラブルの発生の可能性はあります）　また、イベントの最中に不安定になることに気がつきました。早い話が、個人用マクロブックでも、どこでも同じなのですが、ブックを開く時、閉じる時に、一番加重が掛かるのです。一部のブックのBeforeClose イベントで、開いている部分のモジュールのタイプが、すげ変わってしまうという、致命的なバグがあり解消できません。単に、それを避けるだけなら、エラー・トラップを設ければよいのですが、それは、結果的、どこかでまたエラーが発生する原因になります。私が、「個人用マクロブック」の難しさを書いていた、ひとつなのだと思います。

今までのUserForm の方法でも良いので、それに切り替えようか、昨日夜迷いましたが、今の方針自体は間違っていないと思いなおし、もう少し続けてみようと思います。

そういう私も、この機にExcelというものを勉強しなおしているようなものです。（掲示板で公開してしまったら、何だそんなことかで、おしまいですが、）やはり新しいテクニックを開発しているわけです。小手先のテクニックではどうしようもなく、自分の開発経験の浅さに嘆きつつも、もう少しがんばってみます。お時間掛けてすみません。

この回答へのお礼

こんばんは。お世話になります。

No.9でお示しいただいたコードについて、それぞれの命令文がどのような意味を持っているのか、Webで調べたりしています。まだまだわからない部分も多いのですが、とても勉強になります。
わたしなどには到底想像もできないようなハイレベルなマクロをお考えくださっていることと思います。

>お時間掛けてすみません。
とんでもないです。わたしのほうこそなにもできずに申し訳ありません。

お礼日時：2007/04/11 21:54

No.9 回答者： Wendy02 回答日時： 2007/04/10 01:28

こんばんは。

Wendy02です。

最初に作ったものは、ボツにしましたので、本日、別な方法でつくり、やっと、今、9割できたようですが、しばらく使って、バグを探している最中です。何年ぶりかで、本格的なものを作ったのです。

しかし、ここの書き込み時に注意がありますが、

#回答は全角800字（半角1600字）以内です。これを超える長文の分割投稿はご遠慮ください。

ここに掲示するためには、そのローカルルールに触れるような気がします。行数で、500行ぐらいになってしまいます。半角文字数換算で、8,000字　ぐらいになってしまうようです。私は、ここのローカルルールに触れそうになるのは、初めてなのです。

本来は、直接、お渡しする内容のようです。
ちょっと、困ってしまいました。やってみるしかありませんね。しょうがありません。

結局、別のブックを使用して書き込みすることにしましたが、いろいろ試してみて、ブックの使用中は、書き込みをしないようにしました。そして、メモリ上にデータを置くのはやめて、必ず、隠しコンボボックス内のリストに置くという方法を立てました。これは、隠し技のひとつになると思います。（EXCELのメモリは不安定で失いやすいからです）

以下が、見える側のコンボボックスです。

　　 Set MyCB = Application.CommandBars("WorkSheet Menu Bar")
　　 cnt = MyCB.Controls.Count　
　'ファイルリスト親メニュー
　Set MyCBCtrl = MyCB.Controls.Add(Type:=msoControlPopup, _
　　　　　 Before:=cnt + 1, Temporary:=True)
　　　　
　With MyCBCtrl
　　.Caption = "ファイルリスト(&L)"
　　.Tag = "FL"
　End With


小出しにするつもりではないのですが、前書きとして、大事な部分を書いておきます。
'-----------------------------------------

'Class1 設定

Private WithEvents NewApp As Application
Public Property Set App(ByVal myApp As Application)
'カプセル化
　Set NewApp = myApp
End Property


Private Sub NewApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
If ThisWorkbook.Name <> Wb.Name And _
　 Not (Wb.Name Like "Book#*") And _
　 Wb.IsAddin = False And _
　 StrComp(Wb.Name, INI_FILE, vbBinaryCompare) Then
　LatestFileName = Wb.FullName
　Call Item_Add(LatestFileName)
ElseIf StrComp(Wb.Name, INI_FILE, vbBinaryCompare) = 0 And StartFlg = False Then
　Call MakingFileNameLists
End If
End Sub

'---------------------------------------------
'標準モジュール
Public myApp As Class1
Public LatestFileName As String 'Class からの出力
Public StartFlg As Boolean

Sub Auto_Open()
　'起動時の自動実行
　Call SetMyApp
End Sub

Sub SetMyApp()
'起動時のApplicationインスタンス
　StartFlg = True
　Set myApp = New Class1
　Set myApp.App = Application
　
End Sub

このコードは、ファイルを終了したときに、ファイルの名前が、出力されるためのイベントです。変数の　LatesFileName が出力されます。なお、一部変更する可能性があるのは、StartFlg のブーリアン値を逆にするかもしれません。(以上約1750字)

この回答への補足

Wendy02さん
お世話になりましてありがとうございます。
>私は、ここのローカルルールに触れそうになるのは、初めてなのです。
わたしのせいでご迷惑をおかけしまして申し訳ありません。大変な労力を割いていただき、本当になんとお礼を申し上げたらよいかわかりません。

それから、ご報告ですが、No.6でお教えいただいたコードを職場で試してみたところ、Wendy02さんが予想されたとおり、リストを表示してくれませんでした。

わたしがいうのもヘンですが、あまり根を詰めていただいてお体に障るようなことのないよう、お気をつけください。

補足日時：2007/04/10 09:06

No.8 回答者： Wendy02 回答日時： 2007/04/08 02:35

こんばんは。

最初に、まだ、マクロは出来そうにもありません。

あまり、複雑なものを考えすぎたのかもしれません。「PERSONAL.XLS」に書き込まないで、ログを取ることは、問題はないのですが、そのファイル名の保存とリストとの表示のタイミングが、今、見えてこないのです。たぶん、保存するときが、本来、ベストではないかとは考えて作っているのですが、まだ、書き終わっていないのです。かなりコードが、長文になってしまいました。こういうケースは、いままでなかったのです。今のようなペースだと、だいたい、失敗のケースが多いです。

ただ、今回の事情を聞かせていただき、これは何とかしなくてはいけない、と思いました。中途半端なものではいけないですね。もしかしたら、アドインのほうが良いかもしれません。個人の問題ではないのではないのでしょうから。

>管理者にもきいたのですが、全社的にわざわざそういう設定にしてあるとのことでした（ファイル履歴が使えるとセキュリティ的にどんな不都合があるのかは、面倒だったのか、それとも秘密なのか、説明してもらえませんでしたが）。

自分と会社との立場の関係があるから、それは簡単にはいかないでしょうけれども、akeem2003さんが今おやりになっているような、VBAを動かすことが許可されているなら、はっきり言えば、何とかなります。会社によっては、VBA禁止令が出ているところもありますから。

また、C:\File Programes/C:\Windows フォルダの書き込み、閲覧禁止というところはあります。もしかしたら、IE を殺してあるとか？単に、Office の履歴だけを取れないようにする意味はないので、他の複合的な問題かもしれません。

私は、前回に書いた、「PERSONAL.XLS」に書き込みをしないという原則も、撤回したほうがよいかもしれません。もう、背に腹は変えられないというか。

（事情が分からなかったので、一方的な言い分を書いて、大変申し訳ありません。年に1度のトラブルもないものに、あれこれ言っても、履歴を見れないほうが遥かに問題は大きいです!）いろいろ、思考錯誤してみます。

この回答への補足

Wendy02さま

大変お手数をおかけしまして申し訳ありません。ここまで親身になっていただいて、本当にありがたく思っております。おおげさではなく、感激で涙が出そうです。

>C:\File Programes/C:\Windows フォルダの書き込み、閲覧禁止というところはあります。もしかしたら、IE を殺してあるとか？
確かに、c:ドライブは一般ユーザーには閲覧すら許可されていません。IEは、セキュリティ関係の設定をいじれないようにはされていますが、使用はできます。

面倒なことにおつきあいいただき申し訳ありませんが、なにとぞよろしくお願い申し上げます。

補足日時：2007/04/08 09:09

No.7 回答者： Wendy02 回答日時： 2007/04/07 16:19

こんにちは。

>Excelの ツール－オプション－最近使用したファイルの一覧 というメニューも、グレーになっていて操作できないようにされています。

>何やらセキュリティの関係だとかで、エクスプローラーやオフィスソフトのファイル履歴が表示されないのです。

それは、かなり使いづらいですね。実は、その「グレー」になって操作できない現象が、PERSONAL.XLS か、EXCEL.XLB ファイルの反乱を起こして、「グレー」になることがあるのです。その二つのファイルを削除すると、デフォルトに戻ると同時に、機能は復活するはずなのですが。（PERSONAL.XLS のマクロは、一旦、エクスポートして、新たに作ったPERSONAL.XLS にインポートします）

本当に、「設定されたもの？」か、管理者さんに確認されたほうがよいのではありませんか？エクスプローラはともかくとして、Excelでは、トラブル以外は、そんな話は聞いたことがありません。

それから、私の書いたマクロは、そういう状態では使えないはずです。以前も、似たような相談を受けてだめだった経験があります。

今は、ファイル名は、手書きか何かをされているわけですね。

ファイル名のログを取るマクロというのは、今すぐ出来ませんが、ある程度のイメージはあります。そういう種類のマクロは、半端じゃなく、難しそうです。しばらく考えて、もし、だめだったら、ダメだったという書き込みを入れます。

順を追って、リストのファイル名が下に追いやられるように作らなくてはならないことですね。手動で削除する方法も入れておかなくてはなりません。

この回答への補足

こんにちは、大変お世話になりましてありがとうございます。

>本当に、「設定されたもの？」か、管理者さんに確認されたほうがよいのではありませんか？
管理者にもきいたのですが、全社的にわざわざそういう設定にしてあるとのことでした（ファイル履歴が使えるとセキュリティ的にどんな不都合があるのかは、面倒だったのか、それとも秘密なのか、説明してもらえませんでしたが）。同僚達もあきらめて使っています。(;_;)　ファイルの場所は記憶に頼っていますが、さっき使ったファイルでも、上司から急に「○○の資料すぐ出して」などといわれて慌てると、全然わからなくなったりします。

>そういう種類のマクロは、半端じゃなく、難しそうです。
まさかそんなこととは予想もせず、わたしは無謀な挑戦をしていたのですね。冷や汗ものです。

>しばらく考えて、もし、だめだったら、ダメだったという書き込みを入れます。
ご好意に甘えっぱなしでまことに恐縮ですが、なにとぞよろしくお願いいたします。

補足日時：2007/04/07 18:48

No.6 回答者： Wendy02 回答日時： 2007/04/07 13:53

#4の補足の回答をさせていただきます。

>気のせいか、処理速度が上がったように思います。
人の感知するレベルでの速度は上がっていないはずですが、RowSource の使い方は、少し、イレギュラーかもしれません。トラブルはないとはいえないのですが、アクセスが速いです。

>どこかのWebページで、「PERSONAL.XLSを記録用に使っている」
たぶん、あえて、サイトの場所は示しませんが、私の知っている有名な場所でしたら、そこの内容のレベルは、あまり高くありませんし、直す点がかなりあるようです。PERSONAL.XLS は、主に、Excelのツールボタンのマクロに登録するのが一般的です。

特に、あまりPERSONAL.XLS に詰め込みますと、起動時間が遅くなったり、ツール－オプションのデフォルト設定に影響を受けることがあったり、最悪は、起動しなくなったりします。年に1度あるトラブルでもありませんが、絶対にないトラブルとは言えないのです。理由は、XLSというバイナリ・ファイルの構造そのものに原因があります。

「最近使ったエクセルファイルを記録して、リストボックスで一覧表示し、リストから選んで再びそのファイルを開く」

ご質問のコードとご説明と内容が違うようですし、既存のメニュー－ファイルの中に、その機能はあるとは思います。

既存の機能の中で、

○ツール－オプション－全般－最近使用したファイルの一覧　

で、数を指定することが出来ますから、それで呼び出すだけでよいはずです。

>PERSONAL.XLSを使わない方法、まだちょっと思いつかないのですが、考え方のヒントだけでもお教えいただけないでしょうか。よろしくお願いいたします。

PERSONAL.XLS を使わないというよりも、出し入れを頻繁にしなければよいのです。Excel Applicationを終了したときに、変更の保存を催促されなければよいわけです。

参考までに、以下のような方法でもよいかと思います。イメージ的にはかなり違うかもしれませんが、PERSONAL.XLSには書き込みはしていません。既存の機能から呼び出しています。

ただ、本来は、こういうスタイルの場合、ツールバーやメニューバーに組み込んで、コンボボックス(CommandBarComboBox)にしたほうがよいです。少し、内容が高度になります。今は、便宜的に、UserFormにしてあります。（なお、二つのスタイルを作ってみて、これに決まりました。）

なお、以下は、ListBox1 のMultiSelct 0-fmMultiSelectSingle になっています。ファイルを同時に開けることはありませんから。

Private Sub UserForm_Initialize()
Dim myPath(20, 1)
Dim i As Integer
Dim j As Integer
Dim buf As String
On Error Resume Next
Do While Err.Number = 0
　Err.Clear
　i = i + 1
　j = InStrRev(Application.RecentFiles.Item(i).Name, "\")
　If j > 0 Then
　　buf = Mid$(Application.RecentFiles.Item(i).Name, j + 1)
　Else
　　　 buf = Application.RecentFiles.Item(i).Name
　End If
　myPath(i - 1, 0) = buf 'ファイル名
　myPath(i - 1, 1) = Application.RecentFiles.Item(i).Path
Loop
ListBox1.List = myPath
End Sub


Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim wb As Variant
Dim flg As Boolean
Dim fName As String
Dim i As Integer

　flg = False
　For Each wb In Workbooks
　　If ListBox1.Text = wb.Name Then
　　　MsgBox "すでに" & ListBox1.BoundValue & "は開いています。": Exit Sub
　　End If
　Next wb
　On Error GoTo ErrHandler
　For i = 0 To ListBox1.ListCount - 1
　　If ListBox1.Selected(i) = True Then
　　　fName = ListBox1.List(i, 1)
　　　'ファイルを開く
　　　Workbooks.Open fName
　　End If
　Next i
Exit Sub
ErrHandler:
　MsgBox Err.Number & " :" & Err.Description
End Sub

p.s.私も別のプログラミングの初歩の初歩を勉強していますが、分からないことは、先回しです。先に行けば必ず分かるようになるというのが、VBAを学んだ経験から確信が得られました。1年後かもしれないし、2年後かもしれません。人間は、知らないことは、どうしても知りたいという本能もありますが、それをじっとこらえることも大事かなって思います。（^^;

この回答への補足

素晴らしいコードをお考えいただき本当にありがとうございます。１行ずつ読み解いて勉強させていただきます。そのうち、CommandBarComboBoxを使った処理にも挑戦したいと思います。

>ご質問のコードとご説明と内容が違うようです
おっしゃるとおりです。改めて読み返すと、説明になっていませんでした。はじめに書いた「最近使ったエクセルファイルを記録して、リストボックスで一覧表示し、リストから選んで再びそのファイルを開く」の他に、「リスト中不要なファイル名は削除する」ということも考えておりまして、書き込んだコードは、後者の処理のためのものでした。訳のわからないことを書きまして失礼いたしました。

>既存のメニュー－ファイルの中に、その機能はあるとは思います。
私がこのマクロを使いたいと思っているのが、職場のパソコン上においてなのですが、何やらセキュリティの関係だとかで、エクスプローラーやオフィスソフトのファイル履歴が表示されないのです。Excelの ツール－オプション－最近使用したファイルの一覧 というメニューも、グレーになっていて操作できないようにされています。
忙しいときには、昨日（あるいはついさっき）使ったファイルの所在をいちいち探す作業にストレスを感じてしまいますので、なんとか自力で代替の機能を用意できないかと試しているところなのです。No.6でお示しいただいたコードが自宅のExcelと同様に職場でも動くかどうか、確認したいと思います。（うまくいけば最高なのですが…）

それにしても、PERSONAL.XLS を不用意に操作することでExcelが起動しなくなることがあるとは思いもよりませんでした。これからは気をつけます。

補足日時：2007/04/07 14:55

No.5 回答者： Wendy02 回答日時： 2007/04/07 01:57

>数あるExcelマクロ書籍の中で、基本から今回お教えいただいたようなTipsまで

#4 の私から、お勧めするのは、大村あつし氏の『かんたんプログラミングシリーズのExcel　VBA』（3部）がよろしいと思います。全体的には、細かすぎる傾向があるのと、基礎編が概念的というか、大村氏独特の解釈の仕方は、ある程度、知っているものには、鼻に付くものであり、また、初心者には理解しにくいものがあるかと思います。そういう部分は、無視して良いと思います。しょせん、そんなことは、一回では覚えるわけではありませんし、後々になれば分かります。あまり根をつめて読まなければよいと思います。体系的というよりも、段階的に覚えていくのがよいです。今回の内容は、主に、2部のコントロール編で済みます。3部の応用編の技術は必要ないです。

とにかく、手を動かすこと。入力すること。プロシージャを最低でも500個ぐらい作って、「実行」すれば覚えられます。

本来は、純粋に、VBAから入ると良いのですが、そうすると、独習者は、飽きてしまうような気がします。ただ、そのほうが学習スピードは早いはずです。

参考URL：http://www.amazon.co.jp/exec/obidos/ASIN/4774120 …

この回答へのお礼

Wendy02さん

ご推薦ありがとうございます。とてもよさそうな本ですね。これできちんと勉強し直そうと思います。

>あまり根をつめて読まなければよいと思います。

はい。気楽に気長にやっていきます(^^)

お礼日時：2007/04/07 08:24

No.4 回答者： Wendy02 回答日時： 2007/04/07 00:54

こんばんは。

全体的に、コードがヘンです。

データだけを書き入れるなら、少なくとも、myCell は、配列変数だから、その値は、数値か文字列だけです。それを、PERSONAL.XLSに書く必要性はないですね。数千個とか出なければ、最初から、配列関数で配列変数に入れればよいです。

ThisWorkbook.Activate　が、PERSONAL.XLS でないことを信じたいのですが、PERSONAL.XLS を書いたり消したりするのは、誤動作に元になりますから、絶対に、やめたほうがよいです。PERSONAL.XLS は、非表示シートですから、普段、書き入れたり、削除したりはしないようにします。

RowSource をPERSONAL.XLSから取っているのでしょうか？

それを検索して、PERSONAL.XLSのRangeオブジェクトをとってもしょうがないと思います。

Set myCell(i) =

そして、配列変数で宣言してあるのに、ここで、Rangeオブジェクトとして取っているわけです。エラーにはならないけれど、Rangeオブジェクト自体の情報は入らないはずです。

もし、Rangeオブジェクトにするなら、そこは、コレクション変数になるのですが、そんなワザが必要があるとも思えません。ひとつずつ消せばよいのですからね。 以下の場合は、Unionにしてあります。上から取っても、下から取っても、RowSource では、うまくいくとは思えないからです。

>　・セルB2:B21のデータをリストボックスに表示（この部分は別途作成済みです）

だから、それを、Findメソッドで探すこともヘンです。

元のご質問の内容を全部読みきれてはいないのですが、参考までに書かせていただきました。以下は、直接、RowSource でとって、その範囲に対して、行の削除をするようになっています。リストの数が少ないから可能です。

なお、本来は、シートのSelect やActivate なりで、指定したほうが誤って行うことが少ないかとは思います。
'----------------------------------------------------

'ListBox: MultiSelect = 1-fmMultiSelectMulti

Private Sub UserForm_Initialize()
'セルB2:B21のデータをリストボックス
　　ListBox1.RowSource = Range("B2:B21").Address
End Sub

Private Sub CommandButton2_Click()
Dim i As Integer
Dim u As Range
　　Application.ScreenUpdating = False
　　If ListBox1.ListIndex = -1 Then
　　　　Exit Sub
　　End If
　　
　　With ListBox1
　　For i = 0 To .ListCount - 1
　　　If .Selected(i) = True Then
　　　　If u Is Nothing Then
　　　　Set u = Range("B2:B21").Cells(i + 1).EntireRow
　　　　Else
　　　　Set u = Union(u, Range("B2:B21").Cells(i + 1)).EntireRow
　　　　End If
　　　End If
　　Next i
　　End With
　　If Not u Is Nothing Then
　　　　u.Delete
　　End If
　　Application.ScreenUpdating = True
　　Unload Me
End Sub

この回答への補足

こんばんは。大変丁寧な解説とコードをお示しいただきまして、ありがとうございます。Wendy02さんのコードを試させていただきましたところ、気のせいか、処理速度が上がったように思います。きちんと書かれたコードの力というものでしょうか。

>ThisWorkbook.Activate　が、PERSONAL.XLS でないことを信じたいのですが、

…実は、PERSONAL.XLS を使って、記録や削除を行っています。
そもそも全体的に何をしたいのかをきちんとご説明申し上げていませんでしたが、「最近使ったエクセルファイルを記録して、リストボックスで一覧表示し、リストから選んで再びそのファイルを開く」ということができるようにしようとしています。（Windowsの「最近使ったファイル」が使えないようにされていて、設定の変更もできないためです）

開いたファイル名をもれなく記録していくためには、常に一番最初に開かれるPERSONAL.XLSのThisWorkbookに、自動で起動するマクロを登録しておけばいいのではないかと考え、PERSONAL.XLSを利用することにした次第です。また、どこかのWebページで、「PERSONAL.XLSを記録用に使っている」という記述を見かけたため、そういう使い方もあるのだなと思い、PERSONAL.XLSに書き込みや削除を行っておりました。

>誤動作に元になりますから、絶対に、やめたほうがよいです。

よくわかりました。

PERSONAL.XLSを使わない方法、まだちょっと思いつかないのですが、考え方のヒントだけでもお教えいただけないでしょうか。よろしくお願いいたします。

補足日時：2007/04/07 01:27

No.3 回答者： M-SOFT#2 回答日時： 2007/04/06 22:56

No.2です。

追加です。
行を削除するときは下から削除するのが基本です。
削除すると上に行が移動するので、行がずれて違う行が削除される音になります。

For i = .ListCount -1 To 0 Step -1　とした方がいいでしょう。

この回答への補足

>行を削除するときは下から削除するのが基本です。

恐れ入ります(^^;
色々な本やWeb上の情報を拾い読みしているような状態で、基本が身に付いておらず、恥ずかしい限りです。

ここは心を入れ替えて、きちんと体系的に勉強してみようと思います。それで、さらにお尋ねさせていただきたいのですが、数あるExcelマクロ書籍の中で、基本から今回お教えいただいたようなTipsまでを習得できるようなものをご存じないでしょうか。厚かましいお願いですが、お教えいただければ幸いです。

補足日時：2007/04/07 00:03

No.2 回答者： M-SOFT#2 回答日時： 2007/04/06 22:51

こんばんは。

ListBoxのList設定はRowSourceでやっていませんか?
Listが再設定される(変わると選択が解除されます。
RowSourceでの設定だと、行を削除すると内容が変わるので選択が解除されます。
このために最初の1件しか削除されないのでしょう。

RowSourceをやめてAddItemで設定するといいでしょう。
ただし、Listはそのままなので設定し直す必要があります。

この回答へのお礼

できました！完璧です！！

ご指摘のとおり、

Private Sub UserForm_Initialize()
UserForm1.ListBox1.RowSource = "B2:B21"
…

とRowSourceでList設定をしていました。その部分を

Private Sub UserForm_Initialize()
For i = 2 To 21
ListBox1.AddItem Cells(i, 2)
Next i
…

と改め、さらに削除処理をするときのカウントの仕方を回答No.3で教えていただいたように直したところ、見事に望みどおりの動きをしてくれるようになりました。ここ数日悩んでいたことが嘘のように解決しました。本当にありがとうございました。

お礼日時：2007/04/06 23:45