EXCELでコマンドボタンを使用したマクロ

質問

EXCEL2003を使用しています。
A列にプロジェクト名、B列以降の列にデータが入っています。
A列のプロジェクトについて、ランダムに担当者が10名います。その担当者名はどこのセルにも記載していません。必要ならば別シートに記載してもＯＫです。
担当者名の列をどこかに挿入し、その担当者名で並べ替えることはしません。
今あるシートを、各担当者がデータを入力し易くしたいのです。
例えば、マクロのコマンドボタンを使って、各担当者にIDをつけ、そのIDを入力後ボタンを押したら、A列のプロジェクトが抽出されるシートを作成したいのですが、
マクロを教えてください。
例）
【現在のシート】
A列（プロジェクト名）B列　C列　　D列　　…
さかな　　　　　　　100　　100　　300　　…
野菜　　　　　　　　500　　650　　200　　…
くだもの　　　　　　200　　150　　　90　　…
鍋　　　　　　　　　80　　　200　　500　　…
鉄板焼き　　　　　　800　　400　　700　　…
：

【定義】
A氏担当プロジェクト＝さかな、鍋
B氏担当プロジェクト＝野菜、鉄板焼き
C氏担当プロジェクト＝くだもの
ID　A氏＝111　B氏＝222　C氏＝333

【作成したいシート】
111を入力後、コマンドボタンを押したら、

A列（プロジェクト名）　B列　　C列　　D列　　…
さかな　　　　　　　　100　　100　　300　　…
鍋　　　　　　　　　　80　　　200　　500　　…

が抽出される。

解りにくいかもしれませんが、ご回答の程よろしくお願いいたします。

回答No.1

zap35 · ベストアンサー

＞各担当者がデータを入力し易くしたいのです
ということであれば、質問のように入力シートと、抽出した結果が表示されるシートが別だと不都合がありませんか？　別シートに各担当が入力しても、元のシートには変更が反映されませんよ。
オートフィルタを用いて元のシートで絞り込み表示する方法はどうでしょうか

まずSheet2に担当者別プロジェクト(以下PJと略す)リストを作成します
Ａ列　　　Ｂ列　　　　Ｃ列　　　　Ｄ列
担当　　ＰＪ１　　　　ＰＪ２　　　ＰＪ３
111　　　さかな　　　鍋
222　　　野菜　　　　鉄板焼き
333　　　くだもの

入力シートの適当なセルを担当ID入力セルとします（仮にF2セルとしています）。また入力シートのG1セルに、入力シートＡ列と全く同じタイトルを入れてください。そして入力シートG2セルに以下の式を貼り付けて下方向にコピーします。
ここではG列を使用しましたが、これは操作時に見えない列（Z列やAA列で構いません）。なお担当IDを入力するF2セルを別のアドレスにする場合は式の修正が必要ですからご注意ください

=IF(COUNTA(OFFSET(Sheet2!$A$1,MATCH($F$2,Sheet2!A:A,0)-1,0,1,10))>ROW(A1),OFFSET(Sheet2!$A$1,MATCH($F$2,Sheet2!A:A,0)-1,ROW(A1)),"")

するとF2セルに入力した担当IDのPJリストが作成されるはずです
一担当当たりの最大PJ数は10としています。もっと多いなら式中１カ所ある「10」を大きな数値に変更して下さい。

次に以下のマクロをALT＋F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて下さい。
入力シート名と、入力シートで先の関数式で抽出条件を表示させた行（今まではG列で説明）は実際の値に変更する必要がありますので、マクロ中のコメントを見てください

Sub Macro1()
Dim lstPJ As Range
Dim lstList As Range
Dim idx As Integer
Const sht As String = "Sheet1" '入力シート名に変更する
Const col As String = "G" '入力シートの担当PJを抽出する列に変更する
　　Set lstPJ = Sheets(sht).Range("A65536").End(xlUp)
　　For idx = 1 To 100
　　　　If Sheets(sht).Cells(idx, col).Value <> "" Then
　　　　　　Set lstList = Sheets(sht).Cells(idx, col)
　　　　Else
　　　　　　Exit For
　　　　End If
　　Next idx
　　Sheets(sht).Columns("A:A").Select
　　Range(Range("A1"), lstPJ).AdvancedFilter Action:=xlFilterInPlace, _
　　　　CriteriaRange:=Range(Range("G1"), lstList), Unique:=False

'　　ActiveSheet.ShowAllData　'フィルタを解除して全行表示
End Sub

最後に入力シート上で「ツールバー右クリック」→「フォーム」→「コマンドボタンアイコンクリック」→「シート上でドラッグ」を行ってコマンドボタンを配置して、それに上記マクロを割り付けます。

するとF2に入力した担当IDによって入力シートの絞り込みが可能です。

説明が分かりにくいと思いますが、まずご自身が質問に書かれたデータでお試しいただけませんか。私もそのデータでテストしています。いきなり実際のシートで試して、「動きません」と言われても補足回答のしようがありませんので、お手数ですがお願いいたします

お礼

ボタンを2つ作成した結果、出来ました!!（抽出・解除の2つです。）
とても助かりました。
どうもありがとうございます☆

補足

早速にご回答いただき、どうもありがとうございます。
今テストした結果、おかげさまで出来ました。
ただ、フィルタの解除が出来ないのですが、最後の記述を教えてください。
よろしくお願いいたします。

回答No.5

sunrisejp

ANo.4の回答はＮｏ.3です。
間違えました。大変失礼いたしました。

回答No.4

sunrisejp

Ｎｏ.2です。
使用したシートは

　　　　　A列　　　　B列　 C列　　D列　　…
（1行目には何も記述せず）
（2行目）さかな　　　100　　100　　300　　…
（3行目）野菜　　　　500　　650　　200　　…
（4行目）くだもの　　200　　150　　　90　　…
（5行目）鍋　　　　　80　　　200　　500　　…
（6行目）鉄板焼き　　800　　400　　700　　…

です。

回答No.3

sunrisejp

考え方としては、
(1)あるセル（ここではＦ12）にコード（ここでは111）を入力する。
(2)コマンドボタン1（Macro1を登録しておく）を押す。
(3)コード番号に適合した行だけを表示する。（ここでは2行目と5行目）
つまり、3、4、6行目を非表示にする。
(4)必要なデータを入力する。
(5)入力し終わったら、コマンドボタン2（Macro2を登録しておく）を押す。
(6)非表示にした行を表示する。

マクロは、
Sub Macro1()
Dim code As Integer
code = Range("f12").Value
If code = 111 Then
Range("3:4,6:6").Select
Selection.EntireRow.Hidden = True
End If
End Sub

Sub Macro2()
Cells.Select
Selection.EntireRow.Hidden = False
End Sub

以上でどうでしょうか？
このマクロをコピーし、各氏用にしたらいいと思います。

お礼

早速にご回答いただき、どうもありがとうございました!!
今回は1つのシートで作業を行いたかったので、No.1の方のVBAを使用させていただきました。
また、シートで分ける事もあると思いますので、その時は使用させていただきます。
本当に助かりました。
どうもありがとうございます!!

回答No.2

imogasi

補足要求
（１）A氏とB氏（A氏以外のこと）は、例えば魚プロジェクトについて、共同担当のようなことは、ないのか？。１人だけが担当なのか？
ーー
（２）（１）について、1人担当として、
A,B、C・・氏と担当プロジェクト名の対応表は、他のシートにあるのか。作ることは許されるのか？
無ければ、プログラム内に持たなければならなくなるが、担当者の変動に対し、面倒になる。
ーー
一番良さそうなのは別シートに
魚　　田中
なべ　田中
野菜　伊藤
肉　鈴木
調味料　伊藤
か
田中　魚　なべ
伊藤　野菜　調味料
鈴木　肉
のような表を持つことです。
前半の方が良いと思う（プロジェクト別ー担当者）。
ーー
そして担当者名（例　田中）をテキストボックスなどに入れる（コンボで選択する）と、魚の行のフォームが出て
入力、済むとなべの行がフォームに出て入力、のようにするのはどうかな。
意見を出してみてください。
ーー
プロジェクトの数のことで、量的なことも注記してください。担当者は１０人程度ですね。
VBAのコードを質問する前に、構想といったものの検討・選択・決定は
、質問者が仕事を一番知っているし、自分の仕事なのだから、決めなくてどうするの。
それはVBAのコード作りと必ずしも関係しない。

お礼

どうもありがとうございました。

補足

（1）殆どは1プロジェクト＝1人なのですが、あるプロジェクトについては、残念ながら2プロジェクト＝1人のものがあります。
（2）他のシートを作成することは可能です。

>そして担当者名（例　田中）をテキストボックスなどに入れる（コンボで選択する）と、魚の行のフォームが出て
>入力、済むとなべの行がフォームに出て入力、のようにするのはどうかな。
>意見を出してみてください。

担当者毎に、一度にプロジェクトを見ることができた方がいいです。
担当者は10人です。

どうぞ、よろしくお願いいたします。