大体でIT

－ちょっと使えるネタを紹介－

HOME >  Excel VBA >  データの操作
大体でIT

Excel VBAで、ユーザーフォームで検索して、値を抽出するには、AutoFilterとCopyを使うとできます。テキストボックスのイベントは、「KeyDown」イベントを使います。データを抽出したいという場面は、結構あるかと思います。データを抽出する方法について、マスターしていきましょう。

はじめに

この記事では、ユーザーフォームを使って、データベースから値を抽出する方法について、ご紹介します。

データを抽出するには、「AutoFilter」でフィルタして、フィルタ結果をコピーするとできます。

実務ではデータを抽出したいといった場面が、結構あるかと思います。

ユーザーフォームを使ったデータの抽出方法について、マスターしていきましょう。

では、ユーザーフォームを使って、データベースから値を抽出する方法について、解説していきます。

この記事を読むメリット

  • ユーザーフォームを使って、データベースから値を抽出する方法がわかります。

本記事の内容を動画でまとめています。

本記事の内容を動画でまとめています。

VBAコードの作成手順について、動画で確認することができます。

こちらの記事も参考になります

目次から見たい項目へ移動すると便利ですよ。

目次

ユーザーフォームで検索して値を抽出したい

やりたい内容は、ユーザーフォームを使って、社員名簿から名前で検索して、値を抽出したいということになります。

やりたい内容

ユーザーフォームを使って、社員名簿から「氏名」で検索して、値を抽出してみます。

ユーザーフォームを使って値を抽出したい

ユーザーフォームを使って、「氏名」で抽出したいです。

ユーザーフォームを使って、「氏名」で抽出したいです

ボタンをクリックしてみます。

ボタンをクリックしてみます

ユーザーフォームが表示されます。

ユーザーフォームが表示されます

検索値を入力して、Enterを押します。

検索値を入力して、Enterを押します

データベースから「氏名」で、値を抽出できます。

値を抽出できる

データベースから「氏名」で、値を抽出できます

データベースから「氏名」で、値を抽出できました。

VBAコードを作成

社員名簿から「氏名」で値を抽出するVBAコードを、段階的に作成していきます。

UserFormを作成

まずは、UserFormを作成します。

テキストボックスと、ラベルをユーザーフォームに作成します。

ユーザーフォームを作成

ユーザーフォームを作成します

ユーザーフォームの表示中もシートを操作できるように、「ShowModal」を「False」に設定します。

「ShowModal」を「False」にする

ユーザーフォームの表示中もシートを操作できるように、「ShowModal」を「False」に設定します

では、「UserForm1」のVBAコードに、VBAコードを作成していきます。

KeyDownイベントを使う

ユーザーフォームのテキストボックスで使うイベントは、「KeyDown」になります。

Enterキーを押した場合にのみ、実行するようにVBAコードを入力します。

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  
  'Enterキー以外は終了
  If KeyCode <> vbKeyReturn Then Exit Sub

  MsgBox "ABC"
  
End Sub

F5を押して、ユーザーフォームを表示します。

ユーザーフォームを表示

F5を押して、ユーザーフォームを表示します

ユーザーフォームのテキストボックスで、Enterキーを押すと、VBAコードを実行できます。

Enterを押してVBAコードを実行

ユーザーフォームのテキストボックスで、Enterキーを押すと、VBAコードを実行できます

「KeyDown」のイベントを使うと、Enterキーを押すだけで実行できるので、ユーザーファーストです。

では、VBAコードを記載していきます。

AutoFilterとCopyを使う

社員名簿からデータ抽出は、「.AutoFilter」と「.Copy」を使います。

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  
  'Enterキー以外は終了
  If KeyCode <> vbKeyReturn Then Exit Sub

  'シートを初期化
  Sheets("検索").Range("A3:H1000").ClearContents
  'テキストボックスの値で、フィルタする
  Sheets("DB").Range("A1").AutoFilter 2, "*" & TextBox1.Text & "*"
  'フィルタ結果をコピー
  Sheets("DB").Range("A1").CurrentRegion.Copy Sheets("検索").Range("A3")
  'フィルタを解除
  Sheets("DB").Range("A1").AutoFilter

End Sub

検索値を入力して、Enterを押すことでVBAコードを実行します。

検索値を入力して、Enterを押す

F5を押して、ユーザーフォームを表示します。

ユーザーフォームを表示します

検索値を入力して、Enterキーを押してみます。

検索値を入力して、Enterキーを押してみます

実行すると、データベースから値を抽出できます。

値を抽出できた

データベースから値を抽出できます

データベースから値を抽出できました。

データ部分のみを抽出

データ部分のみを抽出するには、「.Resize」と「.Offset」を使って、フィルタ結果のデータ部分のみのセル範囲を取得してコピーします。

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  
  'Enterキー以外は終了
  If KeyCode <> vbKeyReturn Then Exit Sub

  'シートを初期化
  Sheets("検索").Range("A4:H1000").ClearContents
  'テキストボックスの値で、フィルタする
  Sheets("DB").Range("A1").AutoFilter 2, "*" & TextBox1.Text & "*"
  'フィルタ結果のデータ部分をコピー
  With Sheets("DB").Range("A1").CurrentRegion
    .Resize(.Rows.Count - 1).Offset(1, 0).Copy Sheets("検索").Range("A4")
  End With
  'フィルタを解除
  Sheets("DB").Range("A1").AutoFilter

End Sub

見出しを入力しておきます。

見出しを入力しておく

見出しを入力しておきます

検索値を入力して、Enterを押すことで、実行してみます。

検索値を入力して、Enterを押す

ユーザーフォームを表示します。

ユーザーフォームを表示します

検索値を入力して、Enterキーを押してみます。

検索値を入力して、Enterキーを押してみます

実行すると、データ部分のみを抽出できます。

データ部分のみ抽出できた

データ部分のみを抽出できます

データ部分のみを抽出できました。

フィルタ結果がある場合に抽出

フィルタ結果がない場合にコピーしてしまうと、すべてのデータが抽出されてしまいます。

なので、フィルタ結果がある場合に、フィルタ結果のデータ部分をコピーします。

すべて抽出されちゃう場合をやってみます。

データベースにない値だと、すべてを抽出しちゃう

データベースにない値で、検索してみます。

データベースにない値で、検索してみます

すべてのデータが抽出されちゃいます。

すべてのデータが抽出されちゃいます

すべてのデータが抽出されちゃいました。

対策として、フィルタ結果がある場合にのみ、コピーするようにします。

フィルタ結果がある場合にのみ抽出する

フィルタ結果がある場合にのみ抽出するVBAコードになります。

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  
  'Enterキー以外は終了
  If KeyCode <> vbKeyReturn Then Exit Sub
  
  'シートを初期化
  Sheets("検索").Range("A4:H1000").ClearContents
  
  'テキストボックスの値で、フィルタする
  Sheets("DB").Range("A1").AutoFilter 2, "*" & TextBox1.Text & "*"
  'フィルタ結果がある場合
  If WorksheetFunction.Subtotal(3, Sheets("DB").Range("A:A")) > 1 Then
    'フィルタ結果のデータ部分をコピー
    With Sheets("DB").Range("A1").CurrentRegion
      .Resize(.Rows.Count - 1).Offset(1, 0).Copy Sheets("検索").Range("A4")
    End With
  End If
  'フィルタを解除
  Sheets("DB").Range("A1").AutoFilter

End Sub

フィルタ結果がない場合は、そのままにできます。

フィルタ結果がない場合は、そのままにできます

フィルタ結果がない場合は、そのままにできました。

検索値が空欄のときは処理を終了

テキストボックスが空欄の場合に検索してしまうと、すべてのデータを抽出してしまいます。

なので、テキストボックスの値が空欄の場合は、処理を終了するようにします。

空欄の場合で、検索してみます。

空欄で検索だと、すべてを抽出しちゃう

空欄で検索してみます。

空欄で検索してみます

すべてのデータが抽出されちゃいます。

すべてのデータが抽出されちゃいます

すべてのデータが抽出されちゃいました。

対策として、空欄の場合は処理を終了するVBAコードを追加します。

空欄の場合は、処理を終了

検索値が空欄の場合は、処理を終了するVBAコードを追加します。

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  
  'Enterキー以外は終了
  If KeyCode <> vbKeyReturn Then Exit Sub
  
  'シートを初期化
  Sheets("検索").Range("A4:H1000").ClearContents
  
  '検索値が空欄の場合は、終了
  If TextBox1.Value = "" Then Exit Sub
  
  'テキストボックスの値で、フィルタする
  Sheets("DB").Range("A1").AutoFilter 2, "*" & TextBox1.Text & "*"
  
  'フィルタ結果がある場合
  If WorksheetFunction.Subtotal(3, Sheets("DB").Range("A:A")) > 1 Then
    'フィルタ結果のデータ部分をコピー
    With Sheets("DB").Range("A1").CurrentRegion
      .Resize(.Rows.Count - 1).Offset(1, 0).Copy Sheets("検索").Range("A4")
    End With
  End If
  
  'フィルタを解除
  Sheets("DB").Range("A1").AutoFilter

End Sub

空欄の場合は、処理を終了することができます。

空欄の場合は、処理を終了することができます

空欄の場合は、処理を終了することができました。

ボタンを追加

ボタンでユーザーフォームを開くと便利なので、ボタンを追加します。

まずは、標準モジュールにユーザーフォームを表示するVBAコードを記載しておきます。

Sub TEST()
  
  'ユーザーフォームを表示する
  UserForm1.Show
  
End Sub

では、ボタンを追加していきます。

ボタンを追加

「開発」タブ→「挿入」→「ボタン」をクリックして、ボタンを挿入します。

「開発」タブ→「挿入」→「ボタン」をクリックして、ボタンを挿入します

ボタンを追加して、名前を変更しておきます。

ボタンを追加して、名前を変更しておきます

ボタンを追加して、名前を変更しておきました。

追加したボタンに、VBAコードを登録していきます。

マクロを登録

ボタンを右クリックして、「マクロ登録」をクリックします。

ボタンを右クリックして、「マクロ登録」をクリックします

ユーザーフォームを開くVBAコードを選択します。

ユーザーフォームを開くVBAコードを選択します

これで、ボタンにVBAコードを登録することができます。

ボタンをクリックして、ユーザーフォームを開くことができます。

ボタンをクリックでユーザーフォームを開く

ボタンをクリックして、ユーザーフォームを開くことができます

検索値を入力して、Enterを押して、値を抽出してみます。

検索値を入力して、Enterを押す

検索値を入力して、Enterキーを押してみます。

検索値を入力して、Enterキーを押してみます

データベースから「氏名」で検索して、値を抽出できます。

データベースから「氏名」で検索して、値を抽出できました

データベースから「氏名」で検索して、値を抽出できました。

おわりに

この記事では、ユーザーフォームを使って、データベースから値を抽出する方法について、ご紹介しました。

データを抽出するには、「AutoFilter」でフィルタして、フィルタ結果をコピーするとできます。

実務ではデータを抽出したいといった場面が、結構あるかと思います。

ユーザーフォームを使ったデータの抽出方法について、マスターしていきましょう。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

サイト内を検索する

↓キーワードを入力する

アーカイブから探す