エクセルVBAで、作業対象を全ワークシートか選択シートかを切り替えたい

質問

エクセル2000です。
VBAで、作業対象を全部のワークシートか選択されたシートかを任意で切り替えたいのです。
ためしに無理とは思いながら以下のように作ってみましたが、当然のようにエラーになります。
どうかお知恵をお貸しくださいませ。
お願い致します<(_ _)>

Sub test()
sc = ThisWorkbook.Worksheets.Count
ssc = ActiveWindow.SelectedSheets.Count

If sc <> ssc Then

ans2 = MsgBox(ssc & " 枚のシートだけでいいんですね？" _
& vbCr & "" _
& vbCr & "もし全部のシート（" & sc & "枚）を対象にするなら" _
& vbCr & "いいえを選択してください。", vbYesNo + vbQuestion, " o(^-^)o")
ts = IIf(ans = vbNo, Worksheets, ActiveWindow.SelectedSheets)'ここでエラー

End If

For Each sh In ts
sh.Activate
sh.Cells(1, 1).Value = sh.Name
Next sh

End Sub

回答No.2

KenKen_SP · ベストアンサー

こんにちは。

zap35 さんのご回答で解決済みだと思います。蛇足で恐縮ですが、オリジナル
のコードだと、全てのシートを選択状態で実行すると ts が Empty でエラー
になります。

Sub test()
　　sc = ActiveWorkbook.Worksheets.Count
　　ssc = ActiveWindow.SelectedSheets.Count
　　Set ts = ActiveWindow.SelectedSheets
　　If sc <> ssc Then
　　　　ans2 = MsgBox(ssc & " 枚のシートだけでいいんですね？" _
　　　　& vbCr & "" _
　　　　& vbCr & "もし全部のシート（" & sc & "枚）を対象にするなら" _
　　　　& vbCr & "いいえを選択してください。", vbYesNo + vbQuestion, " o(^-^)o")
　　　　If ans2 = vbNo Then Set ts = ActiveWorkbook.Worksheets
　　End If
　　For Each SH In ts
　　　　' SH.Activate
　　　　SH.Cells(1, 1).Value = SH.Name
　　Next SH
End Sub

お礼

KenKen_SP、おはようございます。
試してみましたが、まさにその通りでした。
よくよく見れば、If sc <> ssc のときにしかSet ts をしてないんですから当然なんですよね。
勉強になりました。いつもありがとうございます。o(^-^)o

回答No.1

zap35

IIS関数を使用するところを以下に差し替えたら動きませんか？　tsはオブジェクトなのでSetでないとダメではないでしょうか…？

If ans2 = vbYes Then
　Set ts = ActiveWindow.SelectedSheets
Else
　Set ts = ThisWorkbook.Worksheets
End If

お礼

> tsはオブジェクトなのでSetでないとダメではないでしょうか…？

仰せの通りでございました。　(^^;;
ありがとうございます。