エクセルVBA・リストボックスに関する質問です。

質問

エクセルVBA初心者です。
作成したワークシート名をVBAにてリストボックス内に表示し、それを選択するとそのシートに飛ぶようにしています。
-------------------
Private Sub ListBox1_Change()
With ListBox1
Worksheets(.ListIndex + 1).Activate
End With
End Sub
-------------------
Private Sub UserForm_Initialize()
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
ListBox1.AddItem wsSheet.Name
Next wsSheet
End Sub
-------------------
これではすべてのシートがリストボックス内に反映される為、反映させたくないシート(3シートあるのですが）も一緒に表示されてしまいます。

この表示させたくないシートをリストボックス内に表示させない事は可能でしょうか？
又、できるとしたら、どんな言語を使用すれば良いのでしょうか？
ご教授お願い致します。

回答No.4

Wendy02 · ベストアンサー

#2です。
>名称変更・コピーした"＠(2)"はリストボックス内に表示されませんでした。

こういうのって、基本的な話なのですが、再取得させればよいのですから、UserForm 内で、サブルーチンにして呼び出せばよいと思います。

UserForm モジュール内

Private Sub MakingList()
　　Dim wsSheet As Worksheet
　　For Each wsSheet In Worksheets
　　　　Select Case wsSheet.Name
　　　　　　Case "Sheet1", "Sheet2", "Sheet3" '表示させたくないシート
　　　　　　Case Else
　　　　　　　　ListBox1.AddItem wsSheet.Name
　　　　End Select
　　Next wsSheet

End Sub

Private Sub UserForm_Initialize()
　 Call MakingList
End Sub

つまり、加工した場合は、そのコマンドボタンの後などに、

　 Call MakingList
と、再取得すればよいと思います。

ListBox の中は、何かに連動していない、「定数」ですから、シート名やシートの状態を変更したら、そのたびに、ListBox のリストも変更しないとダメです。

お礼

Wendy02様
大変ありがとうございました。
無事解決致しました!!
何度のご回答下さいまして心よりお礼を申し上げます。

自分なりに「リストボックスにて名称変更されたシートを選択→何故か、シート先頭に配置してある"Ａ"が選択される。(必ず先頭のシートが表示される）」
原因を調べまして、
Worksheets(.ListIndex + 1).Activate が原因であることに気が付きました。.ListIndex + 4　に変更しまして無事に解決致しました。

親切に教えて頂きましてありがとうございました。

回答No.3

fly_moon

>名称変更・コピーした"＠(2)"はリストボックス内に表示されませんでした。

Private Sub UserForm_Initialize()
Dim wsSheet As Worksheet

'無視するシート名の設定--------
Const str1 As String = "@"
Const str2 As String = "A"
Const str3 As String = "B"
'------------------------------

Sheets(str1).Copy Before:=Sheets(1)'@シートコピー

For Each wsSheet In Worksheets

If wsSheet.Name <> str1 Then
If wsSheet.Name <> str2 Then
If wsSheet.Name <> str3 Then
ListBox1.AddItem wsSheet.Name
End If
End If
End If
Next wsSheet
End Sub

だと普通に表示されますけど。シートコピーを入れる位置が変なんじゃないですか？

お礼

何度もご回答ありがとうございます。
お手数をお掛け致します。
Sheets(str1).Copy Before:=Sheets(1)'@シートコピー
だと、リストボックスを開いたら"＠"がコピーされてしまいます。
リストボックスにはコピーしたシートは表示されるのですが、以下の2点の不具合があります。

(1)最初にコマンドボタンにて作成し、マクロを組んだ"新規シート作成タブ"をクリック→雛形"＠"をコピーしInputBoxにて名称変更
(2)リストボックスにて名称変更されたシートを選択→何故か、シート先頭に配置してある"Ａ"が選択される。(必ず先頭のシートが表示される）
わかりくい文章・説明で申し訳ございませんが、ご教授の程、宜しくお願い致します。

回答No.2

Wendy02

こんにちは。

方法はいろいろあるけれども、こんな方法があります。

Private Sub UserForm_Initialize()
　　Dim wsSheet As Worksheet
　　For Each wsSheet In Worksheets
　　　　Select Case wsSheet.Name
　　　　　　Case "Sheet1", "Sheet2","Sheet3" '表示させたくないシート
　　　　　　Case Else
　　　　　　　　ListBox1.AddItem wsSheet.Name
　　　　End Select
　　Next wsSheet

End Sub

お礼

ご回答ありがとうございます。
大変参考になります。
fly_moon様へのお礼欄に記載した内容の件からですが、Wendy02様のご回答では問題無くコピー、名称変更できました。
しかし…コピー・名称変更したシートを移動し選択した場合、"＠コピー後・変更したシート名"を選択しても、シートトップにある"Ａ"にジャンプしました。
わかりにくい説明文で申し訳御座いません。

回答No.1

fly_moon

例えば
Private Sub UserForm_Initialize()
Dim wsSheet As Worksheet

'無視するシート名の設定--------
Const str1 As String = "シート名１"
Const str2 As String = "シート名２"
Const str3 As String = "シート名３"
'------------------------------
For Each wsSheet In Worksheets

If wsSheet.Name <> str1 Then
If wsSheet.Name <> str2 Then
If wsSheet.Name <> str3 Then
ListBox1.AddItem wsSheet.Name
End IF
End IF
End IF
Next wsSheet
End Sub
とか。

お礼

ご回答頂きましてありがとうございます。
非表示にする事ができました。
大変感謝しております。
ただ、私の説明不足がありました。
シートは"Ａ"、"Ｂ"、"＠"の３種類があり、"＠"はシートの雛形として
使用しています。
つまり、"＠"は隠したいのですが、"＠"をコピーし、名称変更したシートはリストボックス内に表示したいのです。
(コピー、新規シート作成、名称変更もマクロにて実行）
fly_moon様のご回答では、名称変更・コピーした"＠(2)"はリストボックス内に表示されませんでした。
"＠"だけは諦めてリストボックス内に表示させるしかないのでしょうか？