Excel VBAでユーザーフォームだけ表示したい

質問

Excel VBAでユーザーフォームを作成し、ファイルを起動時にSheetを非表示にしてユーザーフォームのみ表示させるようにしました。

そうしたところ、

(1)実行ボタンをクリックすると作成されるExcelファイルも表示されなくなる
(2)終了ボタンをクリックするとユーザーフォームを含むExcelファイルだけが閉じるのではなく、Excelのアプリ自体が終了してしまう(実行ボタンで作成したExcelファイルは表示したままにしたいのに閉じてしまう）

という現象になってしまいました。

某書籍を参考にしてコードを書いたのですが、なぜかこのようになってしまいました。
※「★」が書籍に指示があった箇所です。

＜ThisWorkbook＞
Private Sub Workbook_Open()
Application.Visible = False '★
myForm.Show '★
myForm.MultiPage1.Value = 0 'マルチページ構成のため
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'========== [×]ボタン,[Alt]+[F4]キーを無効にする ==========
Dim msg As String, title As String
msg = "[画面を閉じて終了する]ボタンから終了してください。"
title = "終了方法"
Dim res As Integer
Select Case CloseMode
Case vbFormControlMenu
res = MsgBox(msg, vbOKOnly + vbCritical, title)
Cancel = True
End Select
ActiveWorkbook.Save '★
Application.Visible = True '★
Application.Quit '★
End Sub

実行したいのは、

(1)ユーザーフォームを含むExcelファイルを起動したときは、起動時にユーザーフォームだけが表示されるようにしたい
　※ワークシートを非表示としたい
(2)「実行」ボタンをクリックすると、プログラムが実行されExcelファイルが新規で作成され表示される
　※上記で記載した「★」の部分をコードをコメントアウトしてユーザーフォームから実行ボタンをクリックすると、プログラムが実行され正常にExcelファイルが新規で作成されることは確認済みです。
(3)ユーザーフォームが閉じても、ユーザーフォームのExcelファイルのみ閉じ、新規で作成されたExcelファイルは閉じない。
　※実行ボタンをクリックして新規ファイルを作成しなかったり、他にExcelファイルがなかった場合は、Excelは終了する。

それとあわせて、
(1)を実行できた場合に、再度コードを編集する時はどうしたらExcelの画面が表示できるのか教えて下さい。
　※それとも表示できないのでしょうか？

作成締め切りが迫っていて焦っています。
お知恵のある方どうかお力添え下さい。
よろしくお願い致します。

回答No.4

cj_mover

＃３、お礼欄へのレスです。

> 追記いただいたコードを実行してみたところ、終了しては「マクロを有効にしますか？」の画面が表示される・・・という繰り返しになってしまいました。

Excel 2000、2003、2010、と３つの環境で、セキュリティレベルを変えながら動作確認していますが、
ご指摘のような状況は再現できませんでした。
原因を特定することはできませんが、他のイベントプロシージャを間接的に呼び出していたり、
何かしら、こちらから見えない記述が影響しているものと思われます。

> 実現したいことは「閉じたらその選択したものだけ閉じる」「開いたときはユーザーフォームだけが表示される」というごくシンプルなことなのです。

はい、＃３は専ら、そのシンプルな要求に必要最低限の手数でお応えしたものです。
（Private Sub CommandButton99_Clickは保険のようなものです。）
動作の確認方法として、＃３の記述を新規のブックに貼り付け、ユーザーフォームとコントロールを追加して
試してみては、、、と思います。

＃３でも書きましたが、こちらとしては奨めるものでもないので、
諦めるのでしたら、特に返信頂かなくても結構ですが、
その場合は、本件質問を未解決のまま締切にしてください。

お役に立てなかったようで、すみません。

回答No.3

cj_mover

＃１、２、ｃｊです。
＃２補足欄へのレスです。

> ご提供いただいたコード「 ' ThisWorkbook モジュール　　'　　8521223」を実行したところ
> 「実行時エラー '1004　'Rows' メソッドは失敗しました '_Global'オブジェクト」エラーになってしまいました。

Rowsのように親オブジェクトを省略して書いた場合は、ActiveWorkbookのActiveSheetのRowsという意味になりますから、
＃１の説明でいうと、この部分、
>> ActiveWorkbookとしては捉えられない（=非表示の）ブックオブジェクトが対象ですから、
>> ThisWorkbook、とか、（ThisWorkbook モジュールに書く場合は）Me、とか、明示的に指定してください。
要は、
【"Active"なオブジェクト配下のオブジェクトを捉える】
【表示されている［ブック、シート、セル範囲、等］を捉える】
等の、暗黙の意味付けを持たせてある省略表記をひとつひとつ、
【ブック.シート.セル範囲】のようにきちんと階層を踏んで書換えてあげれば、そのようなエラーは起こりません。
例えば、
　　ThisWorkbook.Sheets("シート名").Rows(index)
　　Workbooks("ブック名").Sheets("シート名").Rows(index)
のように書く、ということです。
オブジェクトを明示的に指定することは、
これからも頻繁に必要になるであろう非常に重要な基本のひとつです。

> シートを非表示というか、ファイルを開くとユーザーフォームだけが表示されるようにしたいのです。

そうですか。まだ今一つ、目的や意図、求める結果が掴めていませんが、
＃１で提示したものは違う、ということなのでしょう。
例えば、Excel以外のアプリケーションは一切起動していない場合、
ファイルを開くと、デスクトップ上にユーザーフォームだけが表示されるようなイメージでしょうか？

ご質問でご提示のコードを基にすれば、、、。
現在のようにユーザーフォームをモーダル表示している場合は、
ユーザーフォームを閉じたタイミングでアプリケーションウィンドウを再表示する為には、
ユーザーフォームを表示する一連の処理の直後に
　　Application.Visible = True '◆
を書き加えるだけでいいです。

ユーザーフォームを閉じた時に、ThisWorkbookを閉じる命令をする場合には、
Workbook_Open()プロシージャを最後まで実行した後に、ThisWorkbookを閉じる
ように順番を意識した工夫が必要です。
ここでは、Application.OnTimeメソッドを使った例を簡単に提示します。
ThisWorkbookモジュールの記述が実行される順番はこうなるように書いてあります。
　　Application.Visible = False
↓
　　myForm.Show
↓
　　Application.Visible = True
↓　条件によっては
　　Me.Close SaveChanges:=True
通常は、myForm.Showを実行中に様々な処理をしています。
（myForm表示中は、ずーっとmyForm.Showの行の処理を実行中、なのです）
なので、myForm.Showを実行中にThisWorkbookを閉じてしまうと、
以降の処理に進んではくれません。
そこで、待機中の処理スケジュールを実行してから、ThisWorkbookを閉じるようにする為に、
　　Application.OnTime Now, "ThisWorkbook.CloseMe"　　　　'◆ ThisWorkbookを閉じる。
のように、閉じる方法を工夫します。
（本当はApplication.Quitの方も同様に処理するのがベターで、私は必ず実践していますが、
　ＱＡ掲示板では触れないのがお約束？みたいなので、ここでは割愛しています。）

' ' ///

' ' ThisWorkbook モジュール
Private Sub Workbook_Open()
　　Application.Visible = False '★
　　myForm.MultiPage1.Value = 0 'マルチページ構成のため '◆ 必要ない気もしますが、この記述が先でないと意味ないです。
　　myForm.Show '★
　　Application.Visible = True '◆
End Sub

Private Sub CloseMe()
　　Me.Close SaveChanges:=True　'◆ ThisWorkbookを閉じる。上書き保存したくない場合はFalse
End Sub

' ' ///

' ' myForm モジュール
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'========== [×]ボタン,[Alt]+[F4]キーを無効にする ==========
　　Dim msg As String, title As String
　　msg = "[画面を閉じて終了する]ボタンから終了してください。"
　　title = "終了方法"
　　Dim res As Integer
　　If CloseMode <> vbFormCode Then '◆
　　　　res = MsgBox(msg, vbOKOnly + vbCritical, title)
　　　　Cancel = True
　　　　Exit Sub '◆　以降の行の処理を実行したくないという意図だと思うのですが、その場合の記述。
　　End If '◆

''　　ActiveWorkbook.Save '★　？ この記述で何をしたいのかはこちらでは解りません

　　Dim wbk As Workbook
　　For Each wbk In Workbooks '◆ Workbooks総当たり
　　　　If Not (wbk.Name = ThisWorkbook.Name Or UCase(wbk.Name) Like "PERSONAL.XLS*") Then Exit For
　　Next
　　If wbk Is Nothing Then '◆ ThisWorkbook以外にWorkBookが開かれていなければ
　　　　Application.Quit '★ Applicationを終了
　　Else '◆ ThisWorkbook以外にWorkBookが開かれていれば
　　　　Set wbk = Nothing '◆
　　　　Application.OnTime Now, "ThisWorkbook.CloseMe"　　　　'◆ ThisWorkbookを閉じる。
　　End If
End Sub

' ' ///

私個人の考えとして、Application.Visible = Falseを扱うのは、
絶対にエラーを出さない（エラー時にプロジェクトを終了させない）
という前提でしか書けるものではありませんから、
非常時の対策が面倒という意味で他人に奨めるものではないのです。
一応、こちらの立ち位置だけは理解して貰いたい、というだけのことで、
ご要望に応えた物を書くこと自体は、差支えありません。

ただ、やはり心配なので、
＃１でも提案したように、緊急避難用に
ボタンを用意（目立たない所に配置）しておくことをお勧めしておきます。

' ' ///

' ' myForm モジュール
Private Sub CommandButton99_Click()
　　Application.Visible = True
　　Me.Hide
End Sub

' ' ///

> 本を見たらそれができると書いてあったので。
> ただ、本の通りに実行したら質問に記述したとおり、

本に書いてあれば間違いない、というものではないでしょうし、
目的や条件などを十分に考慮した上で、応用するのは思ったより難しかった、というようなことでしょうね。

> ボタンをクリックすると作成されるファイルまで非表示になってしまい
> (最小化もされていないが、バックグラウンドでは開いている様子)、
> 結果を見ることができません。
> また、ユーザーフォームを閉じると、Excelのアプリケーション自体が終了してしまい、
> 他のファイルが開いていてもそれも終了してしまうので、
> ユーザーフォームのファイルだけが終了するようにしたいというのが希望です。

一通り解説策は示せたものと思っています。

以上です。

お礼

「Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
」に追記いただいたコードを実行してみたところ、終了しては「マクロを有効にしますか？」の画面が表示される・・・という繰り返しになってしまいました。実現したいことは「閉じたらその選択したものだけ閉じる」「開いたときはユーザーフォームだけが表示される」というごくシンプルなことなのです。いろいろご迷惑をおかけしてしまい申し訳ございませんでした。

回答No.2

cj_mover

＃１、ｃｊです。
原稿を投稿時に要約して再構成している内に、
おかしな文章を上げてしまいました。

> ユーザーフォームをモーダル（排他的）表示した場合は、
> コマンドバーのボタン（又はクイックアクセスツールバー）に頼るぐらいしか無いのではないかと。
> それでは心許ない、というか、カスタマイズされた環境でしか出来ないことになってしまいます。

結果的に間違った内容になっています。
ユーザーフォームをモーダル（排他的）表示したまま、
「ユーザーフォームを介してのコードによる操作」以外の方法で、
VBEを新たに開くなどの方法は、なかったように記憶しています。
（予めVBEを開いておいて最小化してあればタスクバーから再表示できますし
　裏ワザ的なネタはあるといえばあるのですが）
今回回答の趣旨からしても、不要な説明になっていますので、
上記３行については撤回、とさせてください。

最後のサンプル、.Hideメソッドを使い方、とかは、役に立つんじゃないかな？と思っていますけれど。

失礼しました。

補足

いつもありがとうございます。ご提供いただいたコード「 ' ThisWorkbook モジュール　　'　　8521223」を実行したところ「実行時エラー '1004　'Rows' メソッドは失敗しました '_Global'オブジェクト」エラーになってしまいました。シートを非表示というか、ファイルを開くとユーザーフォームだけが表示されるようにしたいのです。本を見たらそれができると書いてあったので。ただ、本の通りに実行したら質問に記述したとおり、ボタンをクリックすると作成されるファイルまで非表示になってしまい(最小化もされていないが、バックグラウンドでは開いている様子)、結果を見ることができません。また、ユーザーフォームを閉じると、Excelのアプリケーション自体が終了してしまい、他のファイルが開いていてもそれも終了してしまうので、ユーザーフォームのファイルだけが終了するようにしたいというのが希望です。

回答No.1

cj_mover

こんにちは。

シートを非表示というオーダーですが、
ブック内のすべてのシートを非表示にすることは不可能ですから、
代りにブックのウィンドウを非表示にします。
（※ブックが複数のウィンドウをもっている場合もあります。
　　その場合はすべてのウィンドウを非表示にするようループさせてください）
ウィンドウがひとつであれば、
　　Me.Windows(1).Visible = False
という記述で非表示に、
　　Me.Windows(1).Visible = True
で再表示されます。

因みに、ウィンドウを非表示にした状態で、ブックを保存すれば、
次に開いた時にも非表示の状態は維持されますのでWorkbook_Openイベントからの
ブックのウィンドウを非表示にする処理は、設計次第では必要ない場合もあるのかも知れません。

しかし、Excelメニューの[表示]タブから簡単に[再表示]出来てしまいますから、
見られては困る内容ならば、ブックの保護を組み合わせた方がよいのかも知れません。
その場合のサンプルとしては以下のような書き方になります。
後述の本題のコードではブックの保護を扱っていませんので、
必要なら.Windows(1).Visible = の前後の行に追加修正してください。
その際は、記述の順番を間違えないように注意することと、
ActiveWorkbookとしては捉えられない（=非表示の）ブックオブジェクトが対象ですから、
ThisWorkbook、とか、（ThisWorkbook モジュールに書く場合は）Me、とか、明示的に指定してください。

Sub ブックのウィンドウ非表示()
　　Me.Windows(1).Visible = False
　　Me.Protect Password:="1234", Structure:=True, Windows:=True
End Sub
Sub ブックのウィンドウ再表示()
　　Me.Unprotect Password:="1234"
　　Me.Windows(1).Visible = True
End Sub

また、そもそもシートを表示させないブックということなら、
アドインブック（*.xla、*.xlam）として保存しておくのが一般的なので、
通常のマクロ有効ブックを開いてシートが見えないのは、
慣れた人でも驚いてしまうかも知れませんから、理解を得るよう工夫した方が好いとも思います。

' ' ///

お望みのものとは違うかも知れませんが、
直接的な返答として現状に役立ちそうな記述を以下並べてみます。

' ' ///

' ' ThisWorkbook モジュール　　'　　8521223
Private Sub Workbook_Open()
' '　　Application.Visible = False '★
　　Me.Windows(1).Visible = False '◆
　　myForm.MultiPage1.Value = 0 'マルチページ構成のため '◆ 必要ない気もしますが、この記述が先でないと意味ないです。
　　myForm.Show '★ モーダル表示の場合は、myForm を閉じるまで次の行に進みません。
End Sub

' ' ///

' ' myForm モジュール　　'　　8521223
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'========== [×]ボタン,[Alt]+[F4]キーを無効にする ==========
　　Dim msg As String, title As String
　　msg = "[画面を閉じて終了する]ボタンから終了してください。"
　　title = "終了方法"
　　Dim res As Integer
'　　Select Case CloseMode
'　　Case vbFormControlMenu ' フォームの閉じるボタンから閉じようとした場合は　　◎
'　　　　res = MsgBox(msg, vbOKOnly + vbCritical, title)
'　　　　Cancel = True
'　　End Select
　　If CloseMode <> vbFormCode Then '◆ コード（自作のボタンのイベント）からUnLoadした場合以外は　　◎
　　　　res = MsgBox(msg, vbOKOnly + vbCritical, title)
　　　　Cancel = True
　　　　Exit Sub '◆　以降の行の処理を実行したくないという意図だと思うのですが、その場合の記述。
　　End If '◆

''　　ActiveWorkbook.Save '★　？ この記述で何をしたいのかはこちらでは判りません

　　Dim wbk As Workbook
　　For Each wbk In Workbooks '◆ Workbooks総当たり
　　　　If Not (wbk.Name = ThisWorkbook.Name Or UCase(wbk.Name) Like "PERSONAL.XLS*") Then Exit For
　　Next
　　If wbk Is Nothing Then '◆ ThisWorkbook以外にWorkBookが開かれていなければ
''　　　　Application.Visible = True '★
　　　　Application.Quit '★ Applicationを終了
　　Else '◆ ThisWorkbook以外にWorkBookが開かれていれば
　　　　Set wbk = Nothing
　　　　With ThisWorkbook '◆ ThisWorkbookについて
　　　　　　.Windows(1).Visible = True '◆ Windowを再表示する
　　　　　　.Close SaveChanges:=True '◆ 閉じる。上書き保存したくない場合はFalse
　　　　End With '◆
　　End If
End Sub

' ' ///

他、

> (1)を実行できた場合に、再度コードを編集する時はどうしたらExcelの画面が表示できるのか教えて下さい。
> 　※それとも表示できないのでしょうか？

ユーザーフォームをモーダル（排他的）表示した場合は、
コマンドバーのボタン（又はクイックアクセスツールバー）に頼るぐらいしか無いのではないかと。
それでは心許ない、というか、カスタマイズされた環境でしか出来ないことになってしまいます。
ブックのウィンドウを再表示するコード自体は前述のように至って簡単ですが、
権限によって再表示の可否を制限する必要があるなら、以下のような感じにします。
この例では、仮に、CommandButton99 を クリックした場合、のイベントプロシージャに書いていますが、
UserForm_QueryClose イベント内の記述、res = MsgBox()を工夫して、
resの値で条件分岐してみるのもいいかも知れません。
（ユーザー目線で、パスワードを探りたくなるような目立つ設計を奨めるつもりではありませんけれど。）
ブックの保護と関連付ける場合は前述のサンプルを参考にして下さい。

' ' ///

Private Sub CommandButton99_Click()
　　Dim vRtn
　　Const ADMIN_PASS = "1234" '◆ ぱすわーど任意に指定
　　vRtn = Application.InputBox(Prompt:="パスワードを入力", Title:="管理者用編集モード")
　　If vRtn <> ADMIN_PASS Then Exit Sub
　　ThisWorkbook.Windows(1).Visible = True
　　Me.Hide
End Sub

' ' ///

因みに、パスワードを扱うなら、
VBAProjectを保護しておくのが、比較的簡単でより堅くなるとは思います。

以上です。