リストボックスの選択行を、シート上の行数で取得する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の処理に飛ぶという流れです。
ところで、ExcelVBAはプロシージャ名や変数名などに日本語を使うことができます。プログラムといえば半角英数が基本ですが、昔と違ってそうエラーが起こるものでもない、そして可読性が良いという面から、私としては(規模が小さい、限られた人しか使わない、などの場面に限って)「有り」じゃないかなと思っています。
ただし、別の言語でプログラムを組みたいとか、不特定多数の人が使う、関わるものは多数の人が「常識」と思える形のほうが受け入れやすいのは確かです。

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にデータを抽出して、それをフォームで選択してまた処理をする、なんてときにリスト内の位置じゃなくてシート上での位置が欲しいんだよ!と思って作りました。どなたかのお役に立てれば幸いです。
ExcelVBAに興味をお持ちの方は、こちらの記事もどうぞ!
- これからExcelのマクロを始めたいという方に!簡単な練習問題作りました。
- 私がExcelVBAでよく使う便利なコード・スニペットまとめ
- プログラム初心者さんへ贈る、エラーが起きたら試してみて欲しいこと
- ExcelVBAのクラスモジュールって何?という人向けの使い方まとめ
書籍を執筆しています。

2件のコメント
初心者です。
欲しいと思っていたコードだったのでとても助かりました。有難うございます。
iichoyさん、コメントありがとうございます! こんな昔の記事でもお役に立てるなんて書いておいてよかったですー!!
コメントは承認制ですので、反映までしばらくお待ち下さい。(稀にスパムの誤判定にて届かないこともあるようですので、必要な際はお問い合わせからお願い致します。)
YouTubeでQ&Aコンテンツを企画しています
運営しているYouTubeチャンネルで、ご相談やご質問を募集しています。動画のコメントやお問い合わせページからお気軽にご相談をお寄せください。