リストボックスの選択行を、シート上の行数で取得するExcelVBA

Excelのシートに書いてあるリストをリストボックス内に表示して、そこで選択したアイテムの、Excelシート上での行数を取得するというVBAです。日本語難しい。伝わりますかね…？リストボックスの中身が毎回変わる場合によく使うのでまとめてみました。

リストボックスをつくる

ユーザーフォームを作って、その中にリストボックスを作ります。ユーザーフォームの作り方はこちらの記事を参考にどうぞ。

ExcelBookのSheet1にこんな感じのものを作ります。これがリストボックスの中身になります。

後で使う「処理」ボタンも付けてみました。では肝心のリストボックスを選択して、プロパティを見てみます。

赤枠の中を変えてみます。ColmunWidthはホントは50で設定したんですが、勝手に変わっちゃうっぽいです。

• ColumnCount・・・リストボックスに表示する列の数
• ColumnHeads・・・見出しの有無。RowSourceの直前の行が使われる
• ColumnWidth・・・列幅。複数の場合は“;”で区切る
• RowSource・・・リストボックスの中身

プロパティを設定すると、こんな感じに！

デフォルトだとリストボックスはひとつしか選択できないので、複数選択が必要な場合はこちらの項目をMultiSelectに変えておきましょう。

起動のためのコード

処理ボタンを押した時に動くプログラムを先に設定してしまいます。ボタンをダブルクリックして、出てきたUserForm1のコードに

Private Sub CommandButton1_Click()
    Call 処理
End Sub

と記述して、Module1のほうに

Sub 処理

End Sub

こう記述します。（中身は後で書きます。）ボタンを押すと、Module1の処理に飛ぶという流れです。

ExcelBookのほうには、ボタンを押したらUserForm1が表示できるボタンを作ります。詳しくはこちらの記事もどうぞ。

Sub フォーム表示()
    UserForm1.ListBox1.RowSource = "Sheet1!B3:D" & Range("B2").End(xlDown).Row '最終行で再設定
    UserForm1.Show 'フォーム表示
End Sub

フォームを表示させる前に、RowSorceをデータのあるところだけに再設定すると、リストの中身が変わっても、空白を読み込まずに無駄なスクロールバーを出さずに済みます。

選択されている位置を取得するコード

やっと本題です。長くてすみません。さっき作った“処理”のコードの中身を書きます。

Sub 処理()
  Dim rows As String, i As Integer

  With UserForm1.ListBox1
    For i = 0 To .ListCount - 1 'リストボックスの行数
      If .Selected(i) Then '選択されていた場合
        If rows = "" Then
          rows = i + 3 '1こめのとき
        Else
          rows = rows & ", " & i + 3 '複数あるとき
        End If
      End If
    Next i
  End With

  If rows = "" Then
    MsgBox "選択されていません"
  Else
    MsgBox "選択されている行は" & rows & "です。"
  End If
End Sub

1行だけならrowsはintegerでいいんですが、複数の場合を想定してStringで宣言しています。リストボックス上での0行目はシート上での3行目に当たるので、i+3 が行数になります。

動作

選択なし

1行選択

リストボック内では3つめですが、Excelのシート上では5行目です。

複数選択

行さえ分かれば、Cells(row,○)とかで他の値は簡単に指定できますね！

データベースなどからExcelにデータを抽出して、それをフォームで選択してまた処理をする、なんてときにリスト内の位置じゃなくてシート上での位置が欲しいんだよ！と思って作りました。どなたかのお役に立てれば幸いです。