VBAでセルにリストを設定する方法:初心者向けガイド

こんにちは!大阪市住之江区に拠点を置く会社『縁紡ぐ』の稲垣です。

当社は、Excel、ACCESS、RPAなどのシステム開発や既存ツールを使った業務効率化の提案、また、ITスキルアップのための教育に力を入れています。効率的なビジネス運営を目指している企業様、ITスキルの向上を図りたい企業や個人の方に、最適なご提案をさせていただきます。業務プロセスの改善とITスキルアップをサポートし、共に成長するパートナーでありたいと考えています。

目次

VBAでセルにリストを設定する方法:初心者向けガイド

今日はVBAを使ってExcelのセルにリストを設定する方法を紹介します。リスト(ドロップダウンリスト)は、ユーザーがセルに入力できる値を限定し、入力ミスを防ぐのに役立ちます。初心者の方でも簡単に実装できるように解説します。

コード例

以下に、特定のセルにリストを設定するVBAコードの例を示します。

Sub リスト設定()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ’対象のシートを指定
    
    With ws.Range("B1").Validation
        .Delete ' 既存の入力規則を削除
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="選択肢1,選択肢2,選択肢3" 'リスト型の入力規則を指定し選択しを追加
        .IgnoreBlank = True ' 空白選択肢の有無
        .InCellDropdown = True ' セル内にドロップダウンリストを表示するかどうかTrueが基本
        .ShowInput = True ' セルが選択されたときに入力メッセージを表示するかどうか
        .ShowError = True ' 無効な値が入力されたときにエラーメッセージを表示するかどうか
        .InputTitle = "リスト入力" ' セルが選択されたときに表示される入力メッセージのタイトル
        .ErrorTitle = "入力エラー" ' 無効な値が入力されたときに表示されるエラーメッセージのタイトル
        .InputMessage = "リストから選択してください" ' セルが選択されたときに表示される入力メッセージの内容
        .ErrorMessage = "無効な値です。リストから選択してください" ' 無効な値が入力されたときに表示されるエラーメッセージの内容
    End With
End Sub

シートの情報からリストを作成する

別のシートや同一のシートにある選択肢から、リストの選択肢として設定したいことはよくありますよね。今回は、二つの方法をご紹介します。ポイントは、

範囲に名前をつけて設定する

まずは、コードです。

Sub 他のシートのリスト設定()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' リストデータがあるシートと範囲を指定
    Dim listSheet As Worksheet
    Set listSheet = ThisWorkbook.Sheets("Sheet2")
    Dim listRange As Range
    Set listRange = listSheet.Range("A1:A3")
    
    ' リスト範囲を名前付き範囲として設定
    listRange.Name = "リスト範囲"
    
    With ws.Range("B1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=リスト範囲"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
        .InputTitle = "リスト入力"
        .ErrorTitle = "入力エラー"
        .InputMessage = "リストから選択してください"
        .ErrorMessage = "無効な値です。リストから選択してください"
    End With
End Sub

先ほどとほとんどが同じですが、選択肢の範囲を設定するところが違っています。

listRange.Name = “リスト範囲”で、セルの範囲に名前を付けています。

左上に、選択肢のセルを選ぶと、左上にリスト範囲と表示されるようになっています。この名前の付いた範囲をFormula1:=”=リスト範囲”で指定しています。

共通部品化

設定したい範囲と、文字列を渡すことがで共通部品として使えるようにします。

Public Sub inputRuleSet(ByVal targetRange As Range, ByVal validationList As String)
    ' 入力規則を設定
    With targetRange.Validation
        .Delete         ' 既存の入力規則を削除
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:=validationList  'リストを設定
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

ここで、気をつけて欲しいのは

選択肢と選択肢の間には、「カンマ( , )」を入れること

’呼び出すとき
Dim formula As String
Dim targetRange As Range
    
formula = "選択肢1,選択肢2,選択肢3" ’選択肢のリスト
Set targetRange = Me.Range("A3:A5") ’設定する範囲
    
inputRuleSet targetRange , formula  'ルールを設定

これで、共通部品課ができます。

シートの保護をしている場合は、解除してから設定する必要があります

入力規則の設定と入力規則にあっていないセルの値をクリアする方法

入力規則の設定する前にセルにすでに入力値がある場合、そのままだとクリアはされません。クリアする方法については、こちらで紹介しています。

あわせて読みたい
VBA初心者でもできる!入力規則のリストを設定し無効なセルをクリアする方法 【VBA初心者でもできる!入力規則のリストを設定し無効なセルをクリアする方法】 VBA(Visual Basic for Applications)は、Excelなどのアプリケーションを自動化するた...

まとめ

他のシートの範囲をリストとして使用する場合、名前付き範囲を使用することで、Excelの制約を回避し、スムーズに設定することができます。同じシート内で範囲を使用する場合は、名前付き範囲を使用せずに直接範囲を指定することも可能です。

今回の説明が少しでもお役に立てれば幸いです。ぜひ実際に試してみてください!

記事を書いた人

稲垣

  • Excel、ACCESSでのシステム開発が得意
  • ITスキルを共有し実践的に学びながら成長する人を見るのが幸せ
  • 自家焙煎するほどのコーヒー好き
  • 使用言語 VBA、Python、Javascript、Java、HTML、CSS etc.
  • 保有資格 Kintoneアソシエイト、日商簿記検定2級、マンション管理士、管理業務主任者、情報セキュリティマネジメント、ExcelVBA etc.
  • 業務フロー図の作成や業務時間分析を通して、効率化ポイントを探る人
  • お客様にとって本当に良いことかを第一に考える人
よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次