>この様な経験された事ありますか？

質問

再度
Wendy02さんにお尋ねしたいのですが
前回の回答大変参考になりました。
新規bookでお教えいただいたマクロ動作する事を確認
しましたが、いざ目的のプログラムに移植しましたら
UserForm1のListBox1には2列目が表示されてしまうのです
1列目、販売店名　2列目、販売店よみ（前回エンドユーザとしていた所です）勿論教えていただいた
myCrRange.Value = WorksheetFunction.Transpose(Array("エンドユーザー", "*"))のエンドユーザーは販売店名に変更しています。
どうしても2列目の"販売店よみ"が表示されます
どこを変更すれば"販売店名"をListBox1に表示させられますでしょうか？
よろしくお願いいたします。

回答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さんには又わからないことをお尋ねする事があるやも知りません
その時はよろしくお願いいたします。

回答No.2

Wendy02

こんばんは。Wendy02です。

＞リスト範囲はb1:c501
ということですが、「販売店よみ」は、どこにあるのですか？
AdvancedFilter の範囲に、「販売店よみ」を含めなければ、検索できません。
B列が販売店名, C列がエンドユーザー, D列がよみ？

全体の位置情報が分らなくなりました。

また、クライテリア部分（検索条件範囲）は、１列しか使わなくても、２列の余裕を持ったほうがよいです。論理和検索だけでなく、論理積検索のことも考えなくてはなりません。今の話の流れからすると、E-F列をクライテリア部分にし、G列からということになってしまいます。

それから、

「読み」は、Phonetic 関数をお使いなのですか？半角全角の区別があります。メニューの書式－ふりがな－編集　で、全角か半角か、カタカナかを設定をみて、クリアテリア部分の入力を決定します。

もう１つは、Version（Excel 2002 のみ、ワイルドカード・オプションの違いがあります）によって、「アジア○○○」の時もあるので、「アジア*」というようにしてあげなくてはならない、ということがあります。
また、Excel 2002 以外（つまり　Excel 2003 /Excel 2000　などということ)ですと、デフォルトが、ワイルドカード（文字に「*」をつけた状態）付きになっていますので、逆に、ワイルドカードなし状態にするには、このクライテリア部分を、２列にしないと、フィルタオプションを使いこなすのは難しいはずです。

＞UserForm2でTextBox1アジアを入力したい時

正直なところ、UserFormをふたつにすることで、前回よりも、難しくなっています。手直ししてみましたが、たぶん、全部作り直しのほうが早いような気がします。任意の部分なので、タイミング（イベント）は、こちらでは決めかねますが、ボタンなどでUserForm2 を起動するなど考えなくてはなりません。いままでの、変数も、グローバルレベルに変更したほうが良いかもしれません。

補足

再度のご返答ありがとうございます。惑わしてしまい申し訳ありません
実はWorksheet3は
A1：No、B1:販売店、C1:販売店よみ、D1:エンドユーザ
又、抽出条件としてF1:(販売店よみ）抽出範囲にH1としています。
自分なりに教えていただいたマクロのセル位置を作り変えてみました。
Dim myCrRange As Range
Dim myCrRange2 As Range
Dim mySh As Worksheet

Private Sub UserForm_Activate()
Set mySh = Worksheets("Sheet2")
Set myCrRange = mySh.Range("f1:f2") '’検索条件範囲
Set myCrRange2 = mySh.Range("l1:l1")'’UserForm2で一時書き込む所
'出力データは、ちゃんと消さないと次の検索に出てきません。
mySh.Range("F1", mySh.Range("F65536").End(xlUp).Offset(, 2)).ClearContents

'Criteriaへの書き込み
myCrRange.Value = WorksheetFunction.Transpose(Array("販売店よみ", myCrRange2))

myFilterExecute myCrRange 'サブルーチンへ

ListBox1.ColumnCount = 3 '3列表示
'列が離れすぎていたら、
'ColumnWidths | 30 ; 30 ;30 　'などと入れます。

'RowSource よりもListのほうが動きが速い
ListBox1.List = mySh.Range(mySh.Range("h1"), mySh.Range("h1").End(xlDown)).Value ''"h1"はListBox1に表示したい列
End Sub
Private Sub myFilterExecute(myCrRange As Range)
mySh.Range("b1", mySh.Range("b65536").End(xlUp).Offset(, 2)).AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=myCrRange, _
CopyToRange:=mySh.Range("h1"), Unique:=True ''b1からOffset(, 2)で選択範囲’h1から3列表示が抽出範囲
End Sub
Private Sub UserForm_Terminate()
Set mySh = Nothing
Set myCrRange = Nothing
End Sub
UserForm2でセルl1に一時書き込み抽出条件としました。

なんとか目的を達することが出来ました。
''のコメント部分がいろいろ数値を入れ替えて試した結果です
まだエクセルＶＢの初心者の私には中身は理解出来ていないのですが！
これは初心者なりに納品書入力プログラムの一部でしてUserForm1には他に品名入力など有りまして
抽出を多用しますとListBoxのRowSourceプロパティが原因でエラーになる事があり困っていました。

「読み」は、Phonetic 関数を使わず強引によみを単体で半角カタカナで入力しています。
（ユーザーフォームで入力させると"よみ"をうまく読み取ってもらえず、
販売店名とよみのテキストボックスを使っていますが良い方法があるのでしょうか？）

Excel 2002 とExcel 2000でそのような事があったのですか、Excel 2003は所持していないのですが
Excel 2002 で作成し（自宅ｐｃ）Excel 2000（事務所ｐｃ）で使用していますので気をつけます

エクセルＶＢは変数を余り意識せず出来るので初心者の私はOption Explicitを表示させないで
使ってきましたが、今後、Dim宣言して使うようにいたします。

回答No.1

Wendy02

こんばんは。
Wendy02です。

前回、不安を残したままでしたので、気にはしていました。

＞1列目、販売店名　2列目、販売店よみ（前回エンドユーザとしていた所です）

「よみ」を入れることにしたのですね。そうすると、もう少し、手直しが必要かしら？

もし、「販売店よみ」で検索するなら、

myCrRange.Value = WorksheetFunction.Transpose(Array("販売店よみ", "アジア"))
（本来は、「アジア」に当たる部分は、テキストボックスを一つ設けて、そこを入力場所に使います。

販売店名"をListBox1　に表示させるためには、

以下、読み込ませているわけですね。

# ListBox1.List = mySh.Range(mySh.Range("E1"), mySh.Range("E1").End(xlDown).Offset(, 2)).Value

だから、出力された1列目ですから、E列でしたら、このようにします。（配列にするのは、私の好みですが）

　　　↓
ListBox1.List = mySh.Range(mySh.Range("E1"), mySh.Range("E1").End(xlDown).Value

それと、1列でしたら、

ListBox1.ColumnCount = 3 '3列表示

ここは、コメントアウトですね。

'ListBox1.ColumnCount = 3 '3列表示
（ただし、左側に出てくる、ListBox1のプロパティのColumnCountが１であるという条件です。）

ただ、前回のご質問に戻ることですが、そうすると、ダブりが出てこないかしらね。

ダブりを排除し、ユニーク・データにする場合は、以下のように、コメントアウトを戻すのですが、

CopyToRange:=mySh.Range("F1"), _
Unique:=True

ご自身で、相当お分りになるようですが、話がややこしくて、分りにくいようでしたら、もう少し話を聞きご要望をまとめなおしたら、修正したものをアップロードします。

よみの検索を作らなくてはならないような気がしますからね。

私の個人的なイメージからすると、ユーザーフォームだけで、すべてをコントロールできれば、なかなか便利なデータベースが出来上がるかと思います。今のやり方は、私の使う方法と似ていて、とても良いと思います。

ただ、私の感覚からすると、今の段階は、やっと第一段階終了という感じがします。（^^;　まだ、もう少し掛かりますね。

補足

早速のご回答ありがとうございます。
私のやりたい事をお話せずに最小のマクロで表示しましたが
目的は販売店の数が50位あり、それぞれに10数件の
客先（エンドユーザ）があるのです。
その為500件程度の行数（データベース）になります
まず、"よみ"で販売店をListBox1に限定表示（重複なしで）させたいのです
ＤＢなのでNOリングしていますがA1列のNoは必要ないのです。
フィルタオプションをマクロの自動作成で作る時の条件は以下です
リスト範囲はb1:c501
検索条件範囲にd1:d2を
抽出範囲にf1:g1を指定し重複しないにチェックを
入れた時f1列（販売店）をListBox1に表示させています、
次に、ListBox1に表示された販売店をクリックすると
その販売店のエンドユーザーがListBox2に表示される
ように組んでいます。
ところで
myCrRange.Value = WorksheetFunction.Transpose(Array("販売店よみ", "アジア"))のところですが"*"ですと全行表示されるのですが"アジア"にしても
アジアが表示されませんが（勿論、販売店列にアジアは入っていますが）どうしてでしょうか？
それとUserForm2でTextBox1アジアを入力したい時
どのようにすればよいのでしょうか？