エクセルVBAでのプログラム移植時にListBoxに表示される列を変更する方法

質問

• エクセルVBAのプログラムを移植する際に、ListBox1に表示される列を変更する方法について教えてください。
• 移植したプログラムにおいて、ListBox1には2列目のデータが表示されてしまいますが、1列目のデータを表示させたいです。
• myCrRange.Value = WorksheetFunction.Transpose(Array("エンドユーザー", "*"))の部分を変更することで、ListBox1に1列目のデータを表示させることができるのでしょうか？

回答No.3

Wendy02 · ベストアンサー

acenoh 様、こんにちは。

何度も、検討してみました。

全体の概要が分りました。UserForm1 と　UserForm2 は、両方立ち上げているのですね。

本当は、UserForm1 の中で、テキストボックスとリストボックスを両方を備えていたほうが、Excel本体を意識することなく使えるかと思いますが。

最初に、UserForm2 のTextBox1 のプロパティの　IMEMode は、6-fmIMEModeKakanaHalf にしてください。

それから、以下のHPの内容は、今回の仕様に直接ではありませんが、Activate を使っていますので、多少疑問はもちながらも、一応、考慮して変更しました。

http://www.h3.dion.ne.jp/~sakatsu/Excel_Tips07.htm

＞ユーザーフォームで入力させると"よみ"をうまく読み取ってもらえず、
理由はわかりませんが、こちらでも、現象を確認しました。マクロで文字列を入力すると、何かがジャマしているようです。

●ところで気になるのは、「エンド・ユーザ」名は出さなくてよいのでしょうか？

ListBox1.List = mySh.Range(mySh.Range("h1"), mySh.Range("h1").End(xlDown)).Value ''"h1"はListBox1に表示したい列

もしかして、ListBox が、三つあるわけではありませんよね。
販売店名とエンドユーザ名の２列表示にすればよいと思いますが・・・。

・ユーザーフォームの起動は、以下のように、DoEvents か、起動に多少の時間差をWaitで明示的に入れてあげるか、下位のPCや下位のVersionを考慮した場合、このほうが確実かもしれません。そのまま、UserFomr2に入ってしまうと、オブジェクトの取りこぼしがあるかもしれません。

'<シートモジュール>
'---------------------------------------------
Private Sub CommandButton1_Click()
　UserForm1.Show 0
　　DoEvents
　UserForm2.Show 0
End Sub

'---------------------------------------------
'<Userform1 モジュール>

Dim myCrRange As Range
Dim mySh As Worksheet
Private Sub UserForm_Activate()
'Criteriaへの書き込み
If UserForm2.TextBox1.Value = "" Then
　myCrRange.Value = WorksheetFunction.Transpose(Array("=C1", ""))
Else
　myCrRange.Value = WorksheetFunction.Transpose(Array("=C1", UserForm2.TextBox1.Value))
End If
myFilterExecute myCrRange 'サブルーチンへ
ListBox1.List = mySh.Range(mySh.Range("H1"), mySh.Range("H1").End(xlDown)).Value ''"h1"はListBox1に表示したい列
End Sub

Private Sub myFilterExecute(myCrRange As Range, Optional UniqFlg As Boolean = False)
mySh.Range("H1", mySh.Range("H65536").End(xlUp).Offset(, 2)).ClearContents
mySh.Range("B1", mySh.Range("B65536").End(xlUp).Offset(, 2)).AdvancedFilter _
Action:=xlFilterCopy, _
　　CriteriaRange:=myCrRange, _
　　CopyToRange:=mySh.Range("H1"), Unique:=UniqFlg ''B1からOffset(, 2)で選択範囲’H1から3列表示が抽出範囲
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'ここで終了させる
Unload UserForm2
Set mySh = Nothing
Set myCrRange = Nothing
End Sub
Private Sub UserForm_Initialize()
Me.StartUpPosition = 1
'ここで設定
Set mySh = Worksheets("Sheet2")
Set myCrRange = mySh.Range("F1:F2") '’検索条件範囲
End Sub

'---------------------------------------------
'<Userform2 モジュール>
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode <> 13 Then Exit Sub
　AppActivate "Userform1"
End Sub

Private Sub UserForm_Initialize()
　Me.StartUpPosition = 0
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Userform2 単独では終わらせないようにする
　If CloseMode = 0 Then
　 Cancel = True
　End If
End Sub

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

変更点
<Userform1 モジュール>
・Private Sub UserForm_Activate()

初期に立ち上げたときに分岐をさせた。
If UserForm2.TextBox1.Value = ""
クライテリア部分を、数式にした。
　Array("=C1", "")

・Private Sub myFilterExecute
サブルーチン側に移し、出力値を消去するようにした。
mySh.Range("H1", mySh.Range("H65536").End(xlUp).Offset(, 2)).ClearContents

Uniqueフラグを設けて、オプションで、変更できるようにした。
Unique:=UniqFlg　

・UserForm_QueryClose
の中で、UserForm2 を終了させた。

・UserForm_Initialize
で、StartUpPosition 　の　Userform2 の立ち上げと重ならないようにした。

<Userform2 モジュール>
TextBox1_KeyDown で、"ENTER"　コードで、UserForm1 に戻るようにした。
AppActivate "Userform1"　UserForm1 のActivate イベントが作動しないことを考慮し、Userform1 自体を、Activate させた。

・UserForm_QueryClose
Userform2 側からは、終了できないようにした。

以上が変更、改良点です。

お礼

ご紹介いただいたＨＰ大変参考になりました。
どうしてそうなるのか、わからず使っていました。
仕方なくUserForm1からUserForm2を呼ぶ時は同じ大きさで
同じ場所に表示させていました。（下に隠れていて見えないから）

>●ところで気になるのは、「エンド・ユーザ」名は出さなくてよいのでしょうか？
　お話していませんでしたが、
UserForm2で、50音のコマンドボタンを配置し1文字を選択すると
選択した文字で（例”ア*”とシートのセルc2に書込みます）
UserForm2をUnloadさせUserForm1をShowさせActivateした時に
ListBox1で販売店名を表示（この時まだ、ListBox2は空欄）
中の1件をクリックするとその販売店の扱い店（エンドユーザ）を
表示するListBox2があるのです。
（クリックした時に別の場所で抽出するマクロを作成）
ListBox2にあるエンドユーザをクリックするとUserForm1を閉じて
納品書（シート）に販売店名とエンドユーザ名を表示させています。

　UserForm2からUserForm1を表示させた時、もし選択ミスをした時には
キャンセルボタンでUserForm2に戻り1文字選択し直すようにしていますが
問題なく使用できています。（会社のｐｃ（Office2000）で修正したからですかね？）
アドバイスいただいた、DoEventsを使用したモジュールに 書き換えてみます。
大変参考になりました。一旦このスレッド閉じさせていただきますが
Wendy02さんには又わからないことをお尋ねする事があるやも知りません
その時はよろしくお願いいたします。