エクセルのVBAには入力規則のメソッドがあって、これでドロップダウンを作るなんて知らなかったお恥ずかしい話 ー Validationオブジェクト（VBA）

　　　　

正にお題の通りで、シートにドロップダウンリストを作るなんて知らなかった。

一方、ユーザーフォームのドロップボックスなら、数多く作っては来たのです。

どちらもリストから希望する値を選択しますが、表示するオブジェクトが違う。

シートのセルからできるなんて知らなかったのが、実にお恥ずかしい話でした。

なので、早速、ネットに転がるサンプルコードを実行して、手応えを確かめる。

起動させれば確かに、ワークシートでもユーザーフォームみたいにできました。

このValidationオブジェクトというのは、セルに入力規則を設定するものです。

多数のプロパティがありますので、実際にマクロを実行して調べるのが良いな。

ただ、使い方の原則はあって、最初にオブジェクトを削除するのが無難なんだ。

もちろん、入力に規則を設ける範囲（Range)を指定するのは当然なのですが。

サンプルを見ると、1～12の整数、最大10文字、半角のみ入力など設定が豊富。

セルを当てると、日本語入力モードが立ち上がる設定もあり、住所録に好適だ。

Sub IME起動()

    With Range("A2").Validation

        .Delete

        .Add Type:=xlValidateInputOnly

        .IMEMode = xlIMEModeOn

    End With

End Sub

こういうのは実用的と思いますが、ドロップボックスはどうやって活用するか。

サンプルコードも紹介されていますが、問題は表示するリストの記述コードだ。

例えば、Formula1:="ロンドン,東京,ニューヨーク"の場合は、記述が固定です。

別シートに要素を書き込んでおいて、それを読み出して表示したら便利だろう。

なので、そういった点を加味してコードを書いてみましたが、これなら使える。

コードの中では、リストの要素を引用符で括って置く必要もあって、工夫した。

というわけで、入力値が無効なデータならば、入力データを元に戻すのも必要。

このサンプル構文も紹介しておきますが、こういったアイデアを駆使しておけば、入力者が変な挙動をしてもエラーを回避できるだろうし、ユーザーフォームを起動させないでリストから選択できるなんて、目からウロコの便利な発見なのでした。

＜サンプルコード＞

Sub 汎用ドロップダウンリスト()  '冒頭は実際に起動させた画像

Dim i As Long, strRange As String, Str As String

  For i = 1 To Cells(Rows.Count, 4).End(xlUp).Row 'D列の最も下の行まで

    If strRange = "" Then

        strRange = Cells(i, 4)

    Else

        strRange = strRange & "," & Cells(i, 4)

    End If

  Next i '以上はドロップダウンリストの要素作成

Str = ""   '引用符付で括っておく必要性

Str = Str & strRange

Str = Str & ""

With ActiveSheet.Range("B3").Validation

 .Delete

 .Add Type:=xlValidateList, _

 AlertStyle:=xlValidAlertStop, Formula1:=Str

End With '以上はValidationオブジェクトの設定

End Sub

※入力規則を設定しても無効データが入力される場合の対応

Private Sub Worksheet_Change(ByVal Target As Range)

　　If Target.Validation.Value = False Then

　　　　MsgBox "無効データが入力されました。" & vbLf & _

　　　　　　　　　"入力データを元に戻します。"

　　　　Application.Undo

　　End If

End Sub

注：入力規則を適用したシートのシートモジュールに記述しておく。

いいねと思ったら、三つポチっとね！
  
にほんブログ村    にほんブログ村      にほんブログ村