以前の記事で、エクセルのChangeイベントを使って、指定セルに入力した値でマスタを検索して、検索にヒットした値をプルダウンリストに表示する方法をご紹介しました。
自分で書いておいてなんですが、実はプルダウンリストを作成した後のステップに、ちょっとしたひっかかりを覚えておりました。
それは、「VBAで作ったプルダウンリスト、手動で開くのは面倒臭いな…」ということ。
どうせなら、VBAでプルダウンメニューを作ったら、シート上ですぐに開いてくれた方が嬉しいですよね。
私が使用しているPCの場合、エクセルシート上でプルダウンメニューを開くには、セルを選択してAlt +↓を押せばよいので、これらのキーを押したということをVBAに投げてやればよいはず…！
SendKeysステートメントとは
SendKesyステートメントは、今アクティブになっている画面上で、人間がでキーボード入力をしたときの動作を、VBAにさせることができるステートメントです。
簡単に言えば、人間がキーボードのEnterキーを押したときのように、VBAに対して「今開いている画面でEnterキーを押しなさい」と命令できるようなもの。
記述方法も非常に単純です。
キーストロークは、キーボードのキーを指定する引数です。
キーストロークは文字列として指定する必要があるので、コードではこんな感じで指定することになります。
SendKeys "{ENTER}"
キーストロークの後に続く引数True or Falseは、以降の処理を、キーを押したことによって実行される処理が終わってから継続するのか(True)、キーが押されたという情報が送られたら即継続するのか(False)、によって使い分けることができます。
この引数は省略可能で、その場合はTrueをがデフォルトになります。
SendKeysステートメントで指定できるキー
このステートメントで指定できるキーは、例えば以下のようなものがあります。
| キーボードのキー | 引数に指定する文字列 |
|---|---|
| Enter | {ENTER} |
| PageDown | {PGDN} |
| PageUp | {PGUP} |
| ↑ | {UP} |
| ↓ | {DOWN} |
| ← | {LEFT} |
| → | {RIGHT} |
| Shift | + |
| Ctrl | ^ |
| Alt | % |
今回の場合は、Alt + ↓を指定したいので、「%{DOWN}」をVBAに記述すればよさそうです。
プルダウンメニューをSendKeysステートメントで開く
コードにSendKeysステートメントを組み込む
では、実際にSendKeysステートメントをコードに記述してみましょう。
今回使用するコードはこちら。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim objCustom As Object
Dim myRange As Range 'マスタのセル範囲
Dim varList As Variant 'プロパティFormula1の文字列作成用
Dim strAdr As String '最初にヒットしたセル
'マスタリストの範囲をセット
Set myRange = Worksheets("マスタ").Range("A1:A6")
With ActiveSheet
If Target = .Range("B5") Then
On Error Resume Next
Set objCustom = myRange.Find(what:=Target.Value, LookAt:=xlPart)
Application.EnableEvents = False
If objCustom Is Nothing Then
Target.Value = Target.Value
Else
'1件目を文字列にセット
varList = objCustom
strAdr = objCustom.Address
Do
Set objCustom = myRange.FindNext(objCustom)
If objCustom Is Nothing Then
Exit Do
Else
If strAdr <> objCustom.Address Then
varList = varList & "," & objCustom
End If
End If
Loop While Not objCustom Is Nothing And objCustom.Address <> strAdr
With Target.Validation
.Delete
.Add Type:=xlValidateList, _
Formula1:=varList
.ShowError = False
End With
Target.Select
SendKeys "%{Down}"
End If
Application.EnableEvents = True
End If
End With
End Sub
以前の記事でご紹介した、シートのB5セルに入力した値をマスタシートから検索して、検索にヒットした値を全て入力セルのプルダウンメニューに表示させるというものです。
検索とプルダウンメニューの作成が終わったら、入力セルを選択して、38行目に差し込んだSendKesysステートメントで、Alt + ↓を示す%{Down}を指定することによって、作成したプルダウンメニューを開くという動作にしています。
VBAを実行してプルダウンメニューを開いてみる
実際に上のコードを実行してみましょう。
入力セルに「株」を入力して、実行してみると…
この通り、VBAで検索した値が全てプルダウンメニューに表示され、且つ、シート上でプルダウンメニューが自動的に開かれていますね。
つまり、プルダウンメニューが作られた後に、キーボードのAlt + ↓が入力されたことと同じ状態になっています。
これで、面倒臭い手動でプルダウンメニューを開く作業ともオサラバです！
最後に
今回は、今アクティブになっている画面上でVBAでキーボード入力をさせられる、SendKeysステートメントをご紹介しました。
今回のプルダウンメニューを開くように、1回1回の手作業は大した作業量ではないとしても、それがエクセルツールを使うたび、それも頻繁に使うとなると塵も積もればなんとやら、無視できない手間になってきます。
今回のSendKeysステートメントを使えば、そんなちょっとした手作業を軽減できるかもしれませんね。
それでは、最後までお読みいただきありがとうございました！
連載目次：エクセルVBAのイベントを使ってもっと便利なツールにしよう！
エクセルVBAでリストの重複を排除する方法として、Dictionaryオブジェクトを使った重複排除の方法をご紹介しています。
- 【エクセルVBA】イベントを使ってもっと便利なツールにしてみよう！WorksheetオブジェクトのChangeイベント
- 【エクセルVBA】ChangeイベントとEnableEventsプロパティで部分一致検索をする方法
- 【エクセルVBA】Validation.Addメソッドで入力規則のドロップダウンを作る方法
- 【エクセルVBA】Changeイベントと組み合わせてもっと便利に！検索にヒットした値をドロップダウンリストに表示する方法
コメント
ExcelのSendKeysステートメントでは、Numlockが外れるというバグが発生し、テンキーで数値を入力する場合は非常に困ることになります。
sadoさま
こんにちは！コメントありがとうございます。
SendKeysの不具合は、Shellを使ったりする方法があるようですね。
有益な情報ありがとうございます！