VBA 非表示シートの取り扱い

Worksheets(1)を使う時、左から一番目のシートが非表示の場合、シートの指定を誤る可能性があります。非表示シートを無視して表示シートの左から1番目を指定することは可能でしょうか？

VBAなので非表示シートを調べる事はかのうですが、ファイル数が多い上に順番が同じとは限らないので、出来れば避けたいです。また、表示シートの名前もバラバラなので、番号指定を希望します。

No.4 ベストアンサー 回答者： mar00 回答日時： 2014/10/16 20:16

No.2です。

>Controls(1)の数字を変えると番号指定可能なのでしょうか？
Sub Macro1()
Dim Ws As String, stNo As Byte
Ws = Application.CommandBars("Workbook tabs").Controls(2).Caption
stNo = Sheets(Ws).Index
End Sub

のようにすればいいと思います。
stNoに表示されているシートの２番目のシート番号が返ります。

この回答へのお礼

ありがとうございます。
実質１行ですね。

お礼日時：2014/10/16 20:50

No.3 回答者： eden3616 回答日時： 2014/10/16 09:54

配列myStに表示されているシートの左から指定番号のシート名を格納します。

St_noに取得したい番号（表示されている左からn番目のシート番号）を指定してください。

最下のコードでは1,2,4番目の表示されているシートを取得してmyStに格納します。
Sheet1～Sheet10のうち、奇数シートが非表示設定の場合、以下が格納されます。
　　mySt(0) = Sheet2
　　mySt(1) = Sheet4
　　mySt(2) = Sheet8

Sheets(mySt).Selectなどでシート選択できるように文字列配列に格納していますが、
取り扱いやすいようにオブジェクト変数として扱うには以下を変更してください。
①　Dim mySt() As String
　　　　　　↓
　　　Dim mySt() As Worksheet
②　mySt(nxSt) = Worksheets(i).Name
　　　　　　↓
　　　Set mySt(nxSt) = Worksheets(i)

上記例で、非表示5シート、表示5シートであるにもかかわらずSt_noに
1,2,4,6を指定した場合の6は無視されて1,2,4番目の表示シートが格納されます。

■VBAコード

Sub sample()
Dim mySt() As String, St_no() As Variant
Dim i As Integer, cnt As Integer, nxSt As Integer

'配列へ格納するシートの番号を指定
St_no = Array(1, 2, 4)

For i = 1 To Worksheets.Count
　　If Worksheets(i).Visible Then
　　　　cnt = cnt + 1
　　　　If St_no(nxSt) = cnt Then
　　　　　　ReDim Preserve mySt(nxSt + 1)
　　　　　　mySt(nxSt) = Worksheets(i).Name
　　　　　　nxSt = nxSt + 1
　　　　End If
　　End If
　　If UBound(St_no) < nxSt Then Exit For
Next i
ReDim Preserve mySt(nxSt - 1)

'格納された配列を表示
For i = 0 To UBound(mySt)
　　Debug.Print mySt(i)
Next i
End Sub

この回答へのお礼

ありがとうございます。

お礼日時：2014/10/16 20:48

No.2 回答者： mar00 回答日時： 2014/10/15 20:10

番号指定ではないですが

Sub Macro1()
Ws = Application.CommandBars("Workbook tabs").Controls(1).Caption
Sheets(Ws).Select
End Sub
でも可能だと思います。

この回答へのお礼

ありがとうございます。
Controls(1)の数字を変えると番号指定可能なのでしょうか？
検索してもなかなか見つかりません。

お礼日時：2014/10/16 00:49

No.1 回答者： tom04 回答日時： 2014/10/15 19:42

こんばんは！

Sub Sample1()
Dim k As Long, wS As Worksheet
For k = 1 To Worksheets.Count
If Worksheets(k).Visible = True Then
Set wS = Worksheets(k)
Exit For
End If
Next k
End Sub

※　これで k　番目のSheetが　wS　という変数に格納されます。

こんな感じをご希望だったのでしょうか？m(_ _)m

この回答へのお礼

ありがとうございます。
欲しいワークシート名は複数だったので、wSを配列にして取り込むことにします。

お礼日時：2014/10/16 00:47