Excelマクロ初心者なのですが、なんとか作成したいのでどうかご指導く

質問

Excelマクロ初心者なのですが、なんとか作成したいのでどうかご指導ください。

例えばですが、商品ID、商品名、商品の概要、製品番号、単価、在庫数、入力日、生産中止、倉庫ID
というフィールド名の商品情報というExcelファイルがあるとします。

これをユーザーホームを表示して、すべてのフィールドの数のテキストボックスを作成し、コマンドボタンをクリックしたら、あいまい検索（一部を入力して検索させる）。
元のデータにフィルタをかけて表示させたいのです。今回のデータには数字のデータや日付のデータが入りそうですが、実際のデータはすべてテキストが入るフィールドです。
一度検索したら検索条件をクリアさせるボタンをクリックして、再度条件を変えて検索します。

各テキストボックスすべてに入力するとは限らず、１つか２つのテキストボックスに入力してほかは空白という場合もあります。

Excelファイルは不定期で送られてくるものなので、できればどのファイルでも使えるような
マクロにしてボタンで登録できてつかえたらなと思っています。
このファイルは25台のPCでそれぞれが使って検索します。
ファイルを開いてボタンをクリックすると検索するユーザーホームが表示されればいいなと・・・

このような場合にはどうすればいいのでしょうか？
やりたいことははっきりしているのですが、何をどのようにすればいいかわからず本当に困っています。
どうかご指導ください。

サンプルファイルなどあれば教えていただければ参考にさせていただきたいです。
よろしくお願いいたします。

回答No.1

mitarashi · ベストアンサー

フィルタオプションを勉強されてはいかがでしょうか。
フィルタオプションの抽出条件のセルに直接入力する代わりに、ユーザーフォームで入力して、転写する様にすればお望みの事は可能です。
新規ブックにフィルタオプションの抽出条件のシートを設け、マクロの自動記録を開始して、別ブックの抽出したい表の任意のセルを選択した状態で、フィルタオプションの操作を行ってみて下さい。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter3.htm

ただ、下記の部分が引っかかります。
>このファイルは25台のPCでそれぞれが使って検索します。
検索するだけなら良いのですが、検索して25人が同時に書き込むとなると、エクセルでは無理な話になります。不定期に送られてくるとの事なので、元データをいじる事はなさそうですが、そうであれば余分な情報を付け加えるのは混乱を招き、回答が付き難くなります。

お礼

教えていただいたURL大変参考になりました。
あと自分で工夫していろいろやってみてわからないところを改めて質問したいと思います。
どうもありがとうございました。

補足

ご指示ありがとうございます。

教えていただいたURLを印刷して作成してみました。

Sheet1には商品ID,商品名,商品の概要,製品番号,単価,在庫数,入力日,生産中止,倉庫ID
のフィールド名のデータがあります。
Sheet2にこのフィールド名をコピーし、条件を入力したらSheet2の指定した場所に抽出した
データが表示されます。

Dim myRow1 As Long, myRow2 As Long

myRow1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
myRow2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

If myRow2 >= 5 Then
Sheets("Sheet2").Range("A5:I" & myRow2).ClearContents
Sheets("Sheet2").Range("A5:I" & myRow2).ClearFormats

End If

Sheets("Sheet1").Range("A1:I" & myRow1).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet2").Range("A1:I3"), CopyToRange:=Sheets("Sheet2").Range("A5"), Unique:=False

あとユーザーフォームを作成して、フィールドの数だけのテキストボックスを作成して検索ボタンをつけました。このフォームをファイルを開くときに自動的に表示させるようにしました。

Sub workbook_open()

検索.Show

End Sub

このフォームのテキストボックスとSheet2のフィールド名をラベルに、条件をテキストボックスに
入れたいのですが、どのようにリンクさせればいいのでしょうか？
テキストボックスをダブルクリックして下記のように入れても何も表示されないのです。
入れ方が間違っているのでしょうか？考え方がまちがっていますか？

Private Sub TextBox2_Change()
TextBox2.Value = Sheets("sheet2").Range("A2").Value　’シート2のA2の値を代入のつもり
End Sub

すべてワークシートとリンクさせて、検索ボタンをクリックすると該当するデータを
フォーム上に１レコードずつ表示させたいのです。

回答No.5

mitarashi

#1,4です。
一区切りつけるとの事ですが、最後に一つだけ。
シート→テキストボックスへの転写を、テキストボックスのChangeイベントでやろうというのは無茶です。
テキストボックス→シートであっても、Changeイベントでやるのは止めた方が無難です。別にコマンドボタンを設けて、全テキストボックスをまとめて書き込み・あるいは読み込みする事をお勧めします。

お礼

ご指示ありがとうございます。
実はアクセスのフォームで検索ボタンをクリックしてあいまい検索をして、サブシートで結果をだせるので、そんなイメージでExcelのVBAでできるのではないかと模索していました。

いろんな本をみてもテキストボックス→シートというのはないので無理なのかなと思っておりまして、
じゃあテキストボックス→シートで検索させるしかないのかなと思いまして、changeイベントで
１つのテキストボックスの値をシートに入れてみたらできたのでそれでやってみようと思っていました。
それって止めたほうがいいのですね。ではまとめて書き込み・読み込みする方向でやってみます。
ご親切にどうもありがとうございます。まだまだ完成までは程遠いですが、少しづつやってみます。

回答No.4

mitarashi

別の質問でマクロが却下になってしまったので(^^;)
自動記録を発展させるときに、ご参考になると思われるコードを投稿しておきます。
抽出元のシートが、データをctrl+shift+: で選択できる形なら、フィルタオプションのマクロは簡単です。
抽出条件を設定する別ブックのシートも同様に、ctrl+shift+: で、選択できる配置にしておきます。
条件を設定したシートのA10以降に抽出するコードです。
条件の設定方法はお勉強なさって下さい。結構奥が深いと思います。
実用には、ご希望のユーザーフォームからの抽出条件設定の他に、Book1.xls相当のファイルをダイアログを表示して指定する等、種々付け足す必要があると思いますが、ご参考まで。

'基本形
Sub test()
'抽出元Book1.xlsのSheet1 -> 抽出先は、Book2.xlsのSheet1のA10セルとします。
If Workbooks("Book2.xls").Sheets("Sheet1").Range("A10") <> "" Then
Workbooks("Book2.xls").Sheets("Sheet1").Range("A10").CurrentRegion.Clear
End If
Workbooks("Book1.xls").Sheets("Sheet1").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Workbooks("Book2.xls").Sheets("Sheet1").Range("A1").CurrentRegion, _
CopyToRange:=Workbooks("Book2.xls").Sheets("Sheet1").Range("A10"), _
Unique:=False
End Sub

'もうちょっとすっきりさせる
Sub test2()
Dim srcSh As Worksheet
Dim dstSh As Worksheet

Set srcSh = Workbooks("Book1.xls").Sheets("Sheet1")
'Set dstSh = Workbooks("Book2.xls").Sheets("Sheet1")
Set dstSh = ThisWorkbook.Sheets("Sheet1") '抽出先のブックにマクロを記述した場合
If dstSh.Range("A10") <> "" Then
dstSh.Range("A10").CurrentRegion.Clear
End If
srcSh.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=dstSh.Range("A1").CurrentRegion, _
CopyToRange:=dstSh.Range("A10"), _
Unique:=False
End Sub

お礼

ご指示ありがとうございます。
やってみますと、いい感じです。
あとはいろいろ自分で工夫してやってみてわからないところをポイントを絞って改めて
質問したいとおもいます。感謝！！です。

回答No.3

imogasi

似たような操作はデーターフォームにありますが、機能は満足できないと思いますが知っておくこと。
Googleで「エクセル　データ　フォーム」で照会すること。
ーーー
私もデーターフィルタオプションの設定の応用を勧めます。操作でやったことありますか。
無いとすれば、Googleででも「エクセル　フィルタオプション」で照会のこと。
VBAのコードはともかく、エクセルの機能をある程度知り尽くさないで、VBAのプログラムを望むのは
おかしい。
操作をして、マクロの記録を採って、どのセルにフォームからどのように、特に条件を、設定したら良いか推測してやってみること。
＞サンプルファイルなどあれば
初心者であればまず、マクロの記録を採って、応用できないか考えることだ。この鉄則を忘れている質問者が多く、回答者にねだるような質問が多い。もっと疑問点が絞れたら、その点に付いて質問するのにはここは適しているだろうが。

お礼

フィルタオプションの設定は知っていましたが、別のやり方をするのかと思っていました。
でもおっしゃるとおりですね。
いろいろやってみてその結果改めてポイントを絞って質問させていただきます。
ありがとうございました。

回答No.2

MackyNo1

>やりたいことははっきりしているのですが、何をどのようにすればいいかわからず本当に困っています。

ご質問の内容では、どこに入力されているデータをあいまい検索（部分一致のこと？）するのか良くわからないだけでなく、具体的に現在どの部分までできていて、どの部分がわからないのか不明ですので、皆さんからの回答が期待できないように思います。

単純に複数の検索条件であいまい（部分）検索したいという意味なら、フィルタオプションの設定を利用されたほうがわかりよいと思います（この条件部分をマクロでテキストボックスからセルに入力して、そのセル範囲を検索条件にしたフィルタオプションの設定を実行する）。

いずれにしろ、具体例を含めて、もう少しわからない部分をピンポイントで絞って質問されたほうが具体的な回答が得られると思います。

お礼

そうですね。今自分でできる範囲で作成中です。
もっと聞きたい部分をはっきりさせて改めて質問させていただきます。
どうもありがとうございました。