VBAコードでシート名を取得する方法

質問

• VBAコードを使用してExcelブックのシート名を取得する方法について説明します。
• 情報を取得するSheet名を取得するために、UserForm2を使用してSheet名を一覧化し、選択する方法についても解説します。
• これにより、指定したSheet名ではなく、ユーザーが選んだSheetの内容を取得するように変更することができます。

回答No.7

piroin654

遅くなりました。
No1の捕捉で、
＞If ComboBox1.Text = "" Then ←の部分でエラーになってしまいます。
＞なんででしょうか？
とありますが、これはマクロ自体はUserFormに書かれたものでは
ないからです。

Application.OnTime Nowでの自動実行があったりするので、
一つの案ですが。

1）　標準モジュールに

Public strSheetName As String

　を設定。

2）　UserForm1を開くときに、変数の設定などの
　あとに、

　strSheetName = ""

　としてstrSheetNameの初期化をしておく。

3）　UserForm1にコマンドボタンとコンボボックス
　を設定し、
　コマンドボタンのクリックイベントに

Dim wkb As Workbook
Dim wks As Worksheet
Dim strFileName As String
Dim i As Integer

strFileName = "テストファイル.xls"

Set wkb = Workbooks.Open(ThisWorkbook.Path & "\" & strFileName, , True)
With ComboBox1
For Each wks In wkb.Worksheets
.AddItem wks.Name
Next
End With

wkb.Close
Set wks = Nothing
Set wkb = Nothing

　を設定する。

　コンボボックスの変更イベントに

strSheetName = ComboBox1.Text

　を設定する。ComboBox1という名前は実際に
　合わせて変更して置いてください。

4）マクロ　Sub CommandButton1()　の中で、

Sub CommandButton1()
If strSheetName = "" Then
MsgBox "シートが選択されていません"
UserForm1.Show (vbModeless)
Exit Sub
End If
If MsgBox(Space(6) & "メールデータを取込みます。よろしいですか？", vbYesNo, "継続確認") =

7 Then Exit Sub

とし、

Set LstWs = LstWb.Sheets("Sheet1")

を

Set LstWs = LstWb.Sheets(strSheetName)

とする。

というのはどうでしょう。

補足

ありがとうございます。一連のコードですが・・・こんな感じになります。
すいませんが間違っている所がありましたら教えてください。

※まず標準モジュールに・・・
Public Day As String
Public strSheetName As String

Sub CommandButton1()
If MsgBox(Space(6) & "メールデータを取込みます。よろしいですか？", vbYesNo, "継続確認") = 7 Then Exit Sub

Dim LstWb As Workbook
Dim LstWs As Worksheet
Dim OutWs As Worksheet
Dim LstDt As Variant
Dim EndRow As Long
Dim Day0 As String
Dim Day1 As String
Dim i As Long
Dim j As Integer
Dim k As Long
UserForm1.Hide

Set LstWb = Workbooks.Open(ThisWorkbook.Path & "\テストファイル.xlsm")
Set OutWs = ThisWorkbook.Sheets("Sheet1")
Set LstWs = LstWb.Sheets(strSheetName)

　　If strSheetName = "" Then
　　MsgBox "シートが選択されていません"
　　UserForm1.Show (vbModeless)
　　Exit Sub
　　End If

　　EndRow = LstWs.Cells(Rows.Count, 1).End(xlUp).Row
With LstWs
LstDt = .Range(.Cells(1, 1), .Cells(EndRow, 5))
End With
LstWb.Close
Set LstWb = Nothing
Set LstWs = Nothing

　　Load UserForm2
With UserForm2
Day0 = LstDt(2, 4)
.ComboBox1.AddItem Day0
For i = 2 To EndRow
With .ComboBox1
Day0 = .List(.ListCount - 1)
Day1 = LstDt(i, 4)
If Day0 <> Day1 Then
.AddItem Day1
End If
End With
Next i
.ComboBox1.Value = .ComboBox1.List(0)
End With
UserForm2.Show 'modelessでは上手く動きませんので
For i = 1 To 5
OutWs.Cells(2, i).Value = LstDt(1, i)
Next i
k = 2
For i = 2 To EndRow
Day0 = LstDt(i, 4)
If Day = Day0 Then
For j = 1 To 5
OutWs.Cells(k, j).Value = LstDt(i, j)
Next j
k = k + 1
End If
Next i
Set OutWs = Nothing
LstDt = Empty

MsgBox "取り込み完了です。" & vbCrLf & "" & vbCrLf & "良ければ『OK』をクリックして下さい。"
UserForm1.Show (vbModeless)
End Sub

※UserForm1のCombobox1には、標準モジュールを実行
Application.OnTime Now, "CommandButton1"

※UserForm1のCombobox2には、
Private Sub CommandButton2_Click()
Dim wkb As Workbook
Dim wks As Worksheet
Dim strFileName As String
Dim i As Integer

strFileName = "テストファイル.xlsm"

Set wkb = Workbooks.Open(ThisWorkbook.Path & "\" & strFileName, , True)
With ComboBox1
For Each wks In wkb.Worksheets
.AddItem wks.Name
Next
End With

wkb.Close
Set wks = Nothing
Set wkb = Nothing
End Sub

※UserForm1のCombobox1には
Private Sub ComboBox1_Change()
strSheetName = ComboBox1.Text
End Sub