リストボックスに番号と名前を表示したい。

質問

windows7　Excel2003でマクロ作成中の超初心者です。
現在以下のマクロにより、リストボックスに社員名を表示し、その名をクリック
すると、その社員名のシートに飛ぶようになっています。
　シート名は社員名になっていますが、最終のシートの５枚は他の目的に使用しています。
現在、リストボックスは、名前だけが順不動に並んでおり、ボックスから、選択するのに、見にくいのでちょっと時間がかかります。
　それで名前の前に、シート番号等（任意の番号でも可）を表示して、見やすくして、容易に名前を選択するようにしたいのです。
よろしくお願いします。

Private Sub CommandButton1_Click()
Unload 社員選択
End Sub
-------------------------
Private Sub UserForm_Initialize()
Dim i As Integer
For i = 1 To Worksheets.Count - 5
正社員リスト.AddItem Worksheets(i).Name
Next i
With 社員選択
.StartUpPosition = 0 '初期表示位置を表す値を指定しない
.Top = 55 '上端からの距離を設定する
.Left = 300 '左端　　　　〃
' .Show '表示する
End With
End Sub
-----------------------------------------
Private Sub 正社員リスト_Click()
Application.ScreenUpdating = False
With 正社員リスト
Worksheets(.list(.ListIndex)).Activate
End With
Unload 社員選択
End Sub

回答No.5

mitarashi · ベストアンサー

#2～です。
>全シートのうち最後の５枚だけはリストボックスに表示されないようにしたいです。
にいては、右側の5シート限定という事なら、
ReDim myData(2, ThisWorkbook.Worksheets.Count - 5 )
と配列のサイズから5引いてやれば良いはずです。

ただ、
>リストボックスには　1　日本太郎　　2　日本花子　の２名しか表示されません。
という方は、当方の環境(xl2010,WindowsXP SP3)では問題なく出来ているので対応に困るところです。
エラーは出ていないのですね？
対処法としては、
・新たなフォーム、新たなListBoxで試してみる。それでうまくいけば、ListBoxのプロパティを意図せず操作しているかも。
・debug.print ThisWorkbook.Worksheets.Count等で、それぞれの変数が意図する箇所で、意図する値になっているかどうか確認する。
といったところです。
下記URLをご覧下さい。
http://www.excel-excel.com/vbachair/step1-7.html
そのページの一番下のdebug.printでたいてい済みます、というか他は使いこなせていません。

お礼

最後の最後までお世話になりました。頂いたご回答をもとに実行しましたらできました。本当にありがとうございました。

回答No.4

mitarashi

#2,3です。
検証が甘かったです。
配列の一次と二次の要素数が入れ替わっておりました。確認したブックのシートが3枚だけだったので、たまたま動いてしまったのでした。失礼いたしました。

ReDim myData(2, ThisWorkbook.Worksheets.Count)

が正しいです。
ただ、その為に出たエラーはご指摘の内容とは異なっていましたが、とりあえず上記をお試し下さい。

補足

ご面倒をおかけして恐縮しています。教えていただきました
ように変更し ためしたところ
　リストボックスには　1　日本太郎　　2　日本花子　の
２名しか表示されません。
　そしてお願いは、全シートのうち最後の５枚だけは
リストボックスに表示されないようにしたいです。
わたしのわがまま、お手数ですがよろしくお願いします。

回答No.3

mitarashi

#2です。最近投稿が後を引く癖がついてしまっていて申し訳ありません。
グラフシートが混在するケースが気になって試してみましたが、エラーがでます。
コード中、Sheets.Count,Sheets(i)のところを、それぞれWorksheetsに置き換えて下さい。
日頃、グラフシートを滅多に使わないので、Sheetsで済ませてしまっておりましたが、危険ですね。
おかげさまで再認識しました。

Private Sub UserForm_Initialize()
Dim myData() As Variant
Dim i As Long

ReDim myData(ThisWorkbook.Worksheets.Count, 2)
For i = 1 To UBound(myData, 1)
myData(0, i - 1) = i
myData(1, i - 1) = Worksheets(i).Name
Next i
With ListBox1
.ColumnCount = 2
.ColumnWidths = "20;50"
.Column() = myData
End With
End Sub

補足

教えていただきました
Private Sub UserForm_Initialize()の
With ListBox1のところを
With 社員リスト　に変更し
ためしたところ
　リストボックスに一番左の人の名前と　番号1の表示があり
それ以降は名前が出てきません。どうしてでしょうか？

回答No.2

mitarashi

複数列はあまり使った事がないので、ネットの情報を頼りに試しにやってみました。

Private Sub UserForm_Initialize()
Dim myData() As Variant
Dim i As Long

ReDim myData(ThisWorkbook.Sheets.Count, 2)
For i = 1 To UBound(myData, 1)
myData(0, i - 1) = i
myData(1, i - 1) = Sheets(i).Name
Next i
With ListBox1
.ColumnCount = 2
.ColumnWidths = "20;50"
.Column() = myData
End With
End Sub

例えば、クリックされた行のシート名を取り出すなら、
Private Sub ListBox1_Click()
With Me.ListBox1
MsgBox .List(.ListIndex, 1)
End With
End Sub

といった方法でできます。
ご参考まで。

なお、vbhanatyanさんは考え方だけ紹介されたのだと思います。
当方もご質問のコードをみて、これを使いこなせる方なら何故自分で調べてやらないのだろうと思いました。

参考URL：

http://www.eurus.dti.ne.jp/yoneyama/Excel/vba/vba_userform03.html

回答No.1

vbhanatyan

リストボックスにタブを設定する等色々方法がありますが、簡単に実現するなら

登録時下記のように設定し
With ListBox1
.Font.Size = 12
.Font.Name = "ＭＳ　ゴシック"
.AddItem " 002 " & "山田 花子"
.AddItem " 015 " & "浅田 まり子"
.AddItem " 009 " & "前田 温子"
End With

取り出すときは
Dim s_Name As String
Dim s_ID As String
s_Name = Right$(ListBox1.Text, Len(ListBox1.Text) - 6)
s_ID = Left$(ListBox1.Text, 6)

Debug.Print s_ID
Debug.Print s_Name

のようにすれば、番号と名前が別々に管理できるかと思います。

補足

ご回答ありがとうございます。
ただ、名前が（シート名も）不定期に変わりますのでそのたびに、名前と番号を入力しなければなりません。
それは面倒です。
現在の方法は、いちいち名前を入力しなくても、一番左のシートの名前（つまり社員名）から順にリストボックスに自動的に入っていきます。

自分のやりたいことは、1）山田花子　2）浅田真理子・・というシート名にすれば、出来るかもしれませんが
その方法は、他の理由で避けたいです。
一番の方法はシート番号をそのまま、名前にくっつけたいのですがそんなん事出来るでしょうか？
または、リストボックスを二列にして、番号と名前を表示する。・・などの方法が便利なんですが・・