コンボボックスとオートフィルタの連動

解決済

質問者：muh0101
質問日時：2008/02/21 01:27
回答数：1件

データの件数が増えてきたので、コンボボックスで選択した項目を一発で表示させるマクロを組みたいと思います。
前提は以下の通りです。
Webからの受け売りというか、書かれていた通りにやってみたのですが動作しません。どこが間違っているのでしょうか。
また、他にも方法があるようでしたらお知恵をお貸し下さい。

+++

マクロを実行させたいシートにはA3からK3までの項目があります。
そのうち、B3の項目でフィルタをかけたいです。

1.マクロを実行するシートとは別に「マスター」というシートを作成。
そこにコンボボックスにリンクさせる項目を入力。(A3:A16)
セルC1にINDEX関数を置き、(A3:A16)のそれぞれの値を文字に変換。

2.その変換した文字を変数に格納

3.もし、空白を選択してしまったら、マクロから抜ける

4.オートフィルタのセットは、既にセットされていたら一旦解除し再度セット。

5.変数に格納した文字をキーにして、オートフィルタで抽出する。

+++

Sub Combo_AutoFilter()

Application.ScreenUpdating = False

'変数宣言
Dim 選択項目 As Variant
Dim 実行シート名 As Variant

'現在のシート名の格納
実行シート名 = ActiveSheet.Name

'選択項目の格納
Sheets("マスター").Select
選択項目 = Cells(1, 3)

If 選択項目 = Empty Then
Sheets(実行シート名).Select
Exit Sub

End If

'オートフィルタのセット
Sheets(実行シート名).Select
If ActiveSheet.AutoFilterMode = True Then
Selection.AutoFilter
Range("A3:K3").Select
Selection.AutoFilter
Else
Range("A3:K3").Select
Selection.AutoFilter
End If

'オートフィルターで選択
Selection.AutoFilter Field:=2, Criteria1:=選択項目
Range("A3").Select

End Sub

+++

通報する

この質問への回答は締め切られました。

質問の本文を隠す

回答 (1件)

ベストアンサー優先
最新から表示
回答順に表示

No.1ベストアンサー

回答者： redfox63
回答日時：2008/02/21 04:07

変数『選択項目』に代入されるデータは期待通りの物でしょうか

実際に操作してうまくいくて順を『新しいマクロの記録』で作成した物と見比べてみましょう

また、現状どのようなデータがあって何をキーにしてどのような結果を期待するのか
実際にマクロを動作させた際にどの部分が期待に反する結果なのかを投稿しましょう

Sheets(実行シート名).Select
If ActiveSheet.AutoFilterMode = True Then
Selection.AutoFilter
Range("A3:K3").Select
Selection.AutoFilter
Else
Range("A3:K3").Select
Selection.AutoFilter
End If
の部分で
Range("A3:K3").Select
Selection.AutoFilter
は共通で使えると思いますから

Sheets(実行シート名).Select
Range("A3:K3").Select
If ActiveSheet.AutoFilterMode = True Then
　　Selection.AutoFilter
End If
Selection.AutoFilter
といった具合に簡略か出来ますよ

- 0
- 件

通報する

この回答へのお礼

結局、自分ではこれ以上時間が割けず、専任者にトスしてしまいました。
ご教示いただいたポイントは今後の参考にさせて頂きます。
また簡略化できる部分のアドバイスもありがとうございました。

通報する

お礼日時：2008/02/27 22:09

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう！

質問する（無料）

このQ&Aを見た人はこんなQ&Aも見ています

専門家回答数ランキング

専門家

※過去一週間分の回答数ランキングです。

質問する（無料）

他の専門家の回答をチェック

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QVBA コンボボックスで選んだ値を取得するには

ユーザーフォーム上のコンボボックスから値を選択し、その値を変数として使いたいのですが、うまくいきません。

コンボボックスのコードで
Private Sub ComboBox1_Change()
moji1 = ComboBox1.Text
Range("A1").Value = moji1
のようにすれば、コンボボックスから値を選んだ時点でA1セルにその値をコピーできるのですが、同じユーザーフォーム上にあるコマンドボタンをクリックして実行する「マクロ1」にてこのmoji1という変数を使いたいのです。

マクロ1にて、上記と同じ
Range("A1").Value = moji1
というコードを記述しても、ユーザーフォームで選択した値が消えており、empty値となってしまいます。

原因をご存知の方はお教えください。

Aベストアンサー

原因については下記を参考にしてください。
http://pc.nikkeibp.co.jp/pc21/special/2007_gosa/eg5.shtml

他の回答も見る

QVBAでシートからコンボボックスにデータを設定する方法

VBAにてフォーム起動時にシート内に設定した
値をコンボボックスに取り込みたいのですが・・。
たとえばA列に連続で入力されているデータを
取り込むなど・・。
設定データ数は動的に変化します。

Aベストアンサー

もうほとんど同じですが…

Private Sub UserForm_Initialize()
Dim i As Integer

　ComboBox1.Clear
　For i = 1 To Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
　　ComboBox1.AddItem Worksheets("sheet1").Cells(i, 1).Value
　Next
End Sub

他の回答も見る

QエクセルＶＢＡでフィルタ抽出部分のみのコピー

エクセルＶＢＡで売上帳を作成していますが、オートフィルタでデータ抽出した後、表示されている行のみをコピーして別シートに貼りつけるにはどうすればよいのでしょう？

別シートは指定したセルに値のみの貼り付けをしたいと思っています。

宜しくお願いします。

Aベストアンサー

こんにちは
マクロの記録で作成した一例です。コメントを読んで、適当にアレンジして下さい。

Option Explicit
Sub SampleMacro1()
'
' SampleMacro1 Macro
' マクロ記録日 : 2009/3/13
'
　'フィルター部分
　Selection.AutoFilter Field:=1, Criteria1:="=ほげほげ", Operator:=xlAnd
　'可視セルの選択
　Selection.SpecialCells(xlCellTypeVisible).Select
　'選択範囲のコピー
　Selection.Copy
　'コピー先のシート＆セル選択
　Sheets("Sheet2").Select
　Range("A1").Select
　'ペース...続きを読む

他の回答も見る

Qオートフィルタをフォーム上（VBA)で実現 2回目

http://okwave.jp/qa/q8423348.html

前回も、オートフィルタをフォーム上で実現させる方法について
質問させていただきました。　Excel2003を仕様しております。

一通り、オートフィルタをフォーム上で再現することは出来たのですが、
使い方が悪いのか、上手く表示できないことがあります。

例）
ユーザーフォーム上にCombobox1～Combobox3まであります。
Comboboxどれかが変更されたら、空のコンボボックスのリストを再取得します。

-----------------------------------------------
Sub Combobox_Renew_ChangeJob(ByVal ComboboxName As Object, ByVal ColumnNumber As Long)
Application.ScreenUpdating = False '画面更新しない（ちらつき防ぐ）

With ThisWorkbook.Worksheets("データ")

If ComboboxName = "" Then 'コンボボックスが空だった場合
.Select
.Range("A1").AutoFilter Field:=ColumnNumber 'フィルター解除！

ElseIf ComboboxName <> "" Then 'コンボボックスが空じゃない場合
.Select
.Range("A1").AutoFilter Field:=ColumnNumber, Criteria1:=ComboboxName.Text
End If

Result = WorksheetFunction.Subtotal(3, Range("B:B")) 'B列の可視セルがいくつあるか
If Result = 1 Then
MsgBox "一致するデータはありませんでした。" & vbCrLf & "　再度絞り込みなおしてください。"
.Select
.Range("A1").AutoFilter Field:=ColumnNumber 'フィルター解除！
ComboboxName = ""
End If

End With
Call ComboBox_Renewal 'コンボボックス更新
End Sub

------------------------------------------------------------

Sub ComboBox_Renewal()
Application.ScreenUpdating = False '画面更新しない（ちらつき防ぐ）
Dim LastData As Long
With ThisWorkbook.Worksheets("データ")
.Select
LastData = Cells(Rows.Count, 2).End(xlUp).Row 'B列最終行を取得

If (Me.ComboBox1 = "") Then
Me.ComboBox1.Clear
Me.ComboBox1.List = Module1.Get_Unique_and_Visible_List(.Range("E2：E" & LastData)) '[E]
Me.ComboBox1.AddItem ""
Else
Result = Combo1.ListIndex
Me.ComboBox1.Clear
Me.ComboBox1.SetFocus

End If

If (Me.ComboBox2 = "") Then
Me.ComboBox2.Clear
Me.ComboBox2.List = Module1.Get_Unique_and_Visible_List(.Range("C2：C" & LastData)) '[C]
Me.ComboBox2.AddItem ""
End If

If (Me.ComboBox3 = "") Then
Me.ComboBox3.Clear
Me.ComboBox3.List = Module1.Get_Unique_and_Visible_List(.Range("D2：D" & LastData)) '[D]
Me.ComboBox3.AddItem ""
End If
End With
End sub
------------------------------------

Private Sub ComboBox1_AfterUpdate()
Application.ScreenUpdating = False '画面更新しない（ちらつき防ぐ）

With ThisWorkbook.Worksheets("データ")
Call Combobox_Renew_ChangeJob(ComboBox1, .Range("E1").Column)
End With
End Sub

Private Sub ComboBox2_AfterUpdate()
Application.ScreenUpdating = False '画面更新しない（ちらつき防ぐ）
With ThisWorkbook.Worksheets("データ")
Call Combobox_Renew_ChangeJob(ComboBox2, .Range("C1").Column)
End With
End Sub

Private Sub ComboBox3_AfterUpdate()
Application.ScreenUpdating = False '画面更新しない（ちらつき防ぐ）
With ThisWorkbook.Worksheets("データ")
Call Combobox_Renew_ChangeJob(ComboBox3, .Range("D1").Column)
End With
End Sub
-----------------------------------------

上記の方法を使っていますが、
Comboboxに値を全て入れたあと、
絞り込みされていると考え、リストを更新しておりません。
そのため、最初のほうにコンボボックスにデータを入力したものは
選択できてしまいます。

一番最初に選んだComboboxは、
リストが全て残っている状態です。
そのため、他のComboboxで絞り込んだ後、
一番最初に選んだComboboxでは他の値が選択できてしまいます。

説明が下手で分かりにくいかもしれませんが…
何か良い改善方法があれば、教えて頂きたいです！
よろしくお願い致します!!

Aベストアンサー

こんにちは。

申し訳ないのですが、個人的感想としては、ここまで作ってしまったら、作った当人以外、直す人はいないと思ったほうがよいかもしれません。

通常、これは、小さなブックを作り、メニューなどを非表示にしたワークシートだけで済ませるほうが普通だと思います。こういう点では、Excel2003の方が自由度が高いはずです。

それから、コードのいくつかの点で、可動するようには思えない部分があるのと、生きていない部分があるように思いました。

私自身は、このようなことが起きた場合は、頑張ることをしないで、全面的に最初からやり直すことをしています。要するに、設計段階での考え方の問題が多いわけです。

ただ、よくみると、分かりにくい部分はあるものの、コードとしては単純です。

>Sub Combobox_Renew_ChangeJob(ByVal ComboboxName As Object, ByVal ColumnNumber As Long)

「ByVal ComboboxName As Object 」　というのは、ありえるのでしょうか。
ByVal では、Object はパラメータとしてひき受けられないはずです。それに、ComboboxName は、暗黙のValue値になっています。

それで、わたし流に、全面的に書き換えてみました。重複するプロシージャ名はありません。
ComboBox にデータを入力して、Enterで起動します。ただ、個人的に、VBAはかなり忘れつつあります。しかし、本来、これはクラスから作ったほうが良いような気がしています。Indexが取れないのをコントロール名から取得しているからです。

なお、本質問とは関係ありませんが、先日、レスが付かずに、削除された質問についても、回答は作りましたが、そのままになってしまいました。完全ではないのですが、作りましたので、もし興味があれば、ここに出しておきます。

'//----
'UserFormモジュールのみ
Dim shData As Worksheet
Private Sub UserForm_Initialize()
　Set shData = ThisWorkbook.Worksheets("データ")
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　Dim Ar() As Variant
　If KeyCode <> 13 Then Exit Sub
　With shData
　If .AutoFilterMode = False Then Exit Sub
　　ComboBoxReflesh
　End With
End Sub
Private Sub ComboBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　Dim Ar() As Variant
　If KeyCode <> 13 Then Exit Sub
　With shData
　If .AutoFilterMode = False Then Exit Sub
　　ComboBoxReflesh
　End With
End Sub
Private Sub ComboBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　Dim Ar() As Variant
　If KeyCode <> 13 Then Exit Sub
　With shData
　If .AutoFilterMode = False Then Exit Sub
　　ComboBoxReflesh
　End With
End Sub

'
Sub ComboBoxReflesh()
Dim AcCombo As Object
Dim i As Long, k As Long
Dim cb As Control
　 If TypeName(ActiveControl) <> "ComboBox" Then Exit Sub
　 Set AcCombo = ActiveControl
　 'ComboBox のインデックスを取得(二桁有効)
　 i = Application.Max(Val(Right(AcCombo.Name, 2)), Val(Right(AcCombo.Name, 1)))
　 '列への変換
　 Select Case i
　　 Case 1: k = 5
　　 Case 2: k = 3
　　 Case 3: k = 4
　　 Case Else: k = 99
　 End Select
　　With shData
　　If AcCombo.Text = "" _
　　 Or Application.CountIf(.AutoFilter.Range.Columns(k), AcCombo.Text) = 0 _
　　 Or k = 99 Then
　　　MsgBox "一致するデータはありませんでした。" & vbCrLf & _
　　　"　再度絞り込みなおしてください。", vbExclamation
　　　.AutoFilter.ShowAllData
　　　AcCombo.Value = ""
　　　KeyCode = 0
　　Else
　　 .AutoFilter.ShowAllData
　　　ComboboxClear i
　　　ComboBoxInput i, k, ActiveControl.Value
　　End If
　　End With
End Sub

Sub ComboBoxInput(ComboNo As Long, FldNo As Long, CBVal As Variant)
Dim Ar() As Variant
'出てきた配列を、ComboBox に戻す
　With shData
　.Range("A1").AutoFilter Field:=FldNo, Criteria1:=CBVal
　MakingList .AutoFilter.Range.Columns(5), Ar()
　If ComboNo <> 1 Then ComboBox1.List = Ar()
　MakingList .AutoFilter.Range.Columns(3), Ar()
　If ComboNo <> 2 Then ComboBox2.List = Ar()
　MakingList .AutoFilter.Range.Columns(4), Ar()
　If ComboNo <> 3 Then ComboBox3.List = Ar()
　End With
End Sub

Sub MakingList(Rng As Range, Ar() As Variant)
'AutoFilter の内容から一意のデータを出す
　Dim c As Range
　Set Rng = Rng.Offset(1).Resize(Rng.Rows.Count - 1)
　ReDim Ar(0)
　For Each c In Rng.SpecialCells(xlCellTypeVisible)
　　If c.RowHeight > 0 Then
　　　If UBound(Filter(Ar, c.Value)) = -1 Then
　　　　Ar(i) = c.Value
　　　　i = i + 1
　　　　ReDim Preserve Ar(i)
　　　End If
　　End If
　Next c
　ReDim Preserve Ar(i - 1)
End Sub
Sub ComboboxClear(ComboNo As Long)
'前のValueを消す--データの消去は不要
Dim cb As Control
　　　For Each cb In Me.Controls
　　　 If TypeName(cb) Like "ComboBox*" Then
　　　　 If Application.Max(Val(Right(cb.Name, 2)), Val(Right(cb.Name, 1))) <> ComboNo Then
　　　　　 cb.Value = ""
　　　　 End If
　　　 End If
　　　Next cb
End Sub

'//----x

他の回答も見る

QExel VBA　別ブックから該当データを検索し、必要なデータを取得する方法について

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列　　　　 B列　　　　　C列　　　　　　
商品名　　商品番号　　コード
モータ　　U-1325-L　　
ホルダ　　R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数...続きを読む

Aベストアンサー

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
　Application.ScreenUpdating = False
　Dim I As Long
　Dim xlBook
　Set xlBook = Workbooks....続きを読む

他の回答も見る

QExcelマクロ：オートフィルタ3つ以上の条件

添付の画像を使って質問させて頂きます。
バージョンは2010です。

お客様名　A,B,C,D,E 以外のお客様名を抽出するようにマクロを組みたいのですが

ActiveSheet.Range("$A$1:$D$15").AutoFilter Field:=2, Criteria1:= _
"<"&">&"A", Operator:=xlOr, Criteria2:="<"&">&"B""

の様に考えましたが一つの列に3つ以上の条件では対応できないことが分かりました。

添付の画像は実際使用している表を簡素化しているため
お客様名が少ないですが、実際は多様なお客様名があります。

その中で特定した5社以外のお客様の情報を抽出したいです。

宜しくお願い致します。

Aベストアンサー

＞特定した5社以外のお客様の情報を抽出したい

sub macro1()
　dim a
　a = application.transpose(range("B2:B" & range("B65536").end(xlup).row).value)

　a = filter(a, "A", false)
　a = filter(a, "B", false)
　a = filter(a, "C", false)
　a = filter(a, "D", false)
　a = filter(a, "E", false)

　range("A:D").autofilter field:=2, criteria1:=a, operator:=xlfiltervalues
end sub

とかでいいです。

他の回答も見る

Q別のシートから値を取得するとき

Worksheets("シート名").Activate
上記のを行ってから別シートの値を取得するのですが、
この処理を行うと指定したシートへ強制的にとんでしまいます。。。

※イメージ
For　～　To　～
　　Worksheets("シートＡ").Activate
　　シートＡの値取得
　　　　　　　：
　　Worksheets("シートＢ").Activate
　　シートＢの値取得
Next

このイメージ処理を行うとものすごい勢いで画面がチカチカします。。。
シートを変えずに他のシートから値を取得する方法はないのでしょうか。
教えてください！

Aベストアンサー

Worksheets("シートＡ").Range("A1")

みたいな感じでできませんか？

他の回答も見る

Qエクセル　VBA　ユーザーフォームを閉じる

ユーザーフォームを開く時は
UserForm1.Showですが
閉じる時は？
UserForm1.Close
だとコンパイルエラーになります。
End
にするしかないですか？

Aベストアンサー

Unload Me とか　Unload UserForm1 でユーザーフォームを閉じることができます。

他の回答も見る

QエクセルVBA　コンボボックスの値から検索したい

エクセル・VBAで商品検索をしようとしているのですが行き詰ってしまい、皆様のお知恵を拝借できればと思い質問させていただきます。

・現状
１．商品マスタシートにJANコード・商品名・規格その他を入力しています。
２．ユーザーフォームを使い、商品名（一部分）を入力・検索したら、その商品の商品名・規格がコンボボックス上に表示される（2列分の情報を出力しています）

３．当てはまる商品を選択し、コマンドボタンを押したら、その商品のJANコードや商品名・規格・価格などが出力されるようにしたい

...続きを読む

Aベストアンサー

＞コンボボックスに格納されたデータは配列形式で、findメソッドを使って引っ張ってきているのですが、選択したデータからもともとの商品マスタのセルを割り出す操作があるのかどうかも分かりません。

有り体に言えば，そういう「リンク」はありません。
ので，配列データの中に「行番号」を３列目データとして併せて格納し，必要に応じて表示はしないようにしておくなどの手続きが簡単かなと思います。

行番号などのインデックスが格納されていれば，選択データの該当行データを出力するのは極めて容易ですね。

他の回答も見る

Qオートフィルタとドロップダウンリストの連動について

Excel2003を使用しています。

添付の表で
黄色いＣ４セルにはＨ２～Ｈ５の記号のドロップダウンリストが設定されています。

この状態で、たとえば表ＡのNo.列で１をオートフィルタで選択したときに、
Ｃ４も連動する形で自動的に「■」を表示させたいのです。
２を選択した場合なら「●」を、３を選択したら「△」をという感じです。

VLOOKUPと何かの関数を組み合わせるのではないと思いましたがうまくいきません。
アドバイスよろしくお願いします。

可能であればマクロやＶＢＡではなく関数でできればうれしいです。

また逆バージョン（「■」を選択するとオートフィルタで「１」が自動的に選択される）でもかまいません。

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