2つのＶＢＡを一緒にしたら機能しなくなりました(エクセル)

Question

ご教授よろしくお願いいたします。

①エクセル　「未入力セルがあると保存できない設定」をしたいと思い、ここで回答を見つけて作成することができました。
https://oshiete.goo.ne.jp/qa/7426740.html

②このファイルは、不特定多数の方が自分のＰＣにＤＬしたり、コピーしたして利用するファイルなのですが、ファイルを開けるとセキュリティ警告がでてコンテンツの有効化しないと、作動しません。
そこで、「エクセルを開けたとき強制的にマクロ有効化する方法」で探していたら、下記サイトを見つけて試してみましらできました。
https://www.saka-en.com/office/vba-open-the-macr …

ですが、①②一緒にすると機能しなくなりました。（エラーメッセージはでません）

当方初心者なため、質問があやふやでしたらすみません。
以下がコピペしたＶＢＡです。

Option Explicit

'**
' ワークブックオープン
'**
Private Sub Workbook_Open()
ThisWorkbook.Unprotect Password:="password"
On Error Resume Next
If ThisWorkbook.Sheets("編集用").Visible <> True Then ThisWorkbook.Sheets("編集用").Visible = True
If ThisWorkbook.Sheets("ダミー").Visible <> False Then ThisWorkbook.Sheets("ダミー").Visible = False
ThisWorkbook.Protect Password:="password"
'ThisWorkbook.RunAutoMacros Which:=xlAutoOpen
On Error GoTo 0
Exit Sub
End Sub

'**
' ワークブックを閉じる前
'**
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Answer As Long
' 保存されているかチェック
If ThisWorkbook.Saved = False Then
Answer = MsgBox("Do you want to save the changes to the '" & ThisWorkbook.Name & "' ?", vbExclamation + vbOKCancel, "Microsoft Excel")
Select Case Answer
Case vbCancel
Cancel = True
Exit Sub
End Select
End If
ThisWorkbook.Unprotect Password:="password"
On Error Resume Next
If ThisWorkbook.Sheets("ダミー").Visible <> True Then ThisWorkbook.Sheets("ダミー").Visible = True
If ThisWorkbook.Sheets("編集用").Visible <> False Then ThisWorkbook.Sheets("編集用").Visible = False
ThisWorkbook.Protect Password:="password"
ThisWorkbook.Save

Dim k As Long
Dim str, buf As String
Dim myArray As Variant
myArray = Array("A4", "B7", "C8", "D19") '←好みの数だけセル番地を格納する
For k = 0 To UBound(myArray)
If Worksheets("Sheet1").Range(myArray(k)) = "" Then
str = WorksheetFunction.Substitute(Range(myArray(k)).Address, "$", "")
M = M + 1
buf = buf & str & ","
End If
Next k
If M > 0 Then
MsgBox "Sheet1の" & vbCrLf & Left(buf, Len(buf) - 1) & "セルが" & vbCrLf & "未入力です。"
Worksheets("Sheet1").Activate
Cancel = True
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim k As Long
Dim str, buf As String
Dim myArray As Variant
myArray = Array("A4", "B7", "C8", "D19") '←好みの数だけセル番地を格納する
For k = 0 To UBound(myArray)
If Worksheets("Sheet1").Range(myArray(k)) = "" Then
str = WorksheetFunction.Substitute(Range(myArray(k)).Address, "$", "")
M = M + 1
buf = buf & str & ","
End If
Next k
If M > 0 Then
MsgBox "Sheet1の" & vbCrLf & Left(buf, Len(buf) - 1) & "セルが" & vbCrLf & "未入力です。"
Worksheets("Sheet1").Activate
Cancel = True
End If
End Sub 'この行まで

すみません、
"編集用"は"Sheet1"です。

補足日時：2022/06/02 14:55
通報する
まだできていないのですが、最後まで教えていただきましたので、KenKen_SPさんをベストアンサーに選ばせていただきます。

No.7の回答に寄せられた補足コメントです。補足日時：2022/06/14 10:42
通報する

通報する

KenKen_SP · Accepted Answer

ちなみにスペースのみだと見た目空白に見えますけど、判定としては入力あり＝値ありとなるので、Validate関数のチェック通りますけど、、その可能性は？

KenKen_SP · Answer

こちらでは動いてますが、、少し気になっていたとこがあります。
下記の差し替えソースコードに該当部分を入れ替えて試して下さい。
表示してから隠すに処理の順番を変更しています。

さらに、一度次も試して下さい。

校閲→ブックの保護→パスワード入力で保護を解除し、その状態で上書き保存。

また、パスワードが平文になってますので、VBAプロジェクトも保護をかけないと意味がないです。

余談-------------------------------------------------------------------
以前（10～７年前ぐらい）に作成したときは、ブックに使用期限を設けるという目的でした。
今回と同じで主機能を仕込んだシートを保存前に隠してしまう仕組みを作成しました。
今になって思うところは、「マクロを有効にして使ってね」と周知した方が楽だったんじゃないか、ということです。
検討してみて下さい。
余談ここまで---------------------------------------------------------

’↓以下差し替えソースコード

' ------------------------------------------------------------------------
' @機能 ワークシートの表示切り替え
' @引数 flag　 True : 入力シートを隠す（→ダミーシートは表示）
'　　　　　　　False: 入力シートを表示（→ダミーシートは隠す）
' ------------------------------------------------------------------------
'
Private Sub switch_visible(ByVal flag As Boolean)

Me.Unprotect Password:=MY_PASSWORD
　　If flag Then
　　　　With Me.Worksheets("ダミー") '<--- ココの順番変更
　　　　　　.Visible = xlSheetVisible
　　　　　　.Activate
　　　　End With
　　　　Me.Worksheets("編集用").Visible = xlSheetVeryHidden
　　Else
　　　　With Me.Worksheets("編集用")
　　　　　　.Visible = xlSheetVisible
　　　　　　.Activate
　　　　End With
　　　　Me.Worksheets("ダミー").Visible = xlSheetVeryHidden
　　End If
　　Me.Protect Password:=MY_PASSWORD

End Sub

KenKen_SP · Answer

こんばんは。

> ＜WorkseetクラスのVisibleプロパティを設定できません＞が出てきてしまい、 Me.Worksheets("編集用").Visible = xlSheetVeryHiddenがヒットしました。

■予想する原因

1. 全てのワークシートが非表示になってしまう状況

前回の回答のソース内コメントより
　' memo: 全てのシートを隠すことはできない ←★ここが原因かも

全てのワークシートを非表示にしようとするとエラーになります。これは Excel の仕様です。

もう一枚ワークシートを追加したらどうなりますか？

2. 実際のワークシート名とソース内の名前が一致していない

KenKen_SP · Answer

こんにちは。

横から失礼します。
以前同様のソースを書いた経験がありますが、とても難しかったと記憶してます。
あまり原型を残していないですけど、その時の記憶でサンプルソースを書いてみました。
全て ThisWorkbook モジュール内です。

経験から言えば、関数やサブプロシージャを使ってイベント内をできるだけ完結にすると、ソースがすっきりして把握しやすいのではないかと思います。
ご参考まで。

デバッグの仕方は Qchan1962 さんが本当に丁寧に解説されてます。
仰る通り ブレークポイント、ステップイン を使って一行ずつ実行結果を VBE で確認するのを私もオススします。

Option Explicit

' memo: ThisWorkbook モジュールで Me と書くと ThisWorkbook と同義
' memo: 全てのシートを隠すことはできない ←★ここが原因かも
' memo: xlSheetVeryHidden で隠すと VBA からしか再表示できない

' ↓ブックのパスワード
Private Const MY_PASSWORD As String = "password"

' --------------------------------------------------------------------
' イベント: ブックを開いた時に自動的に実行される
' --------------------------------------------------------------------
'
Private Sub Workbook_Open()
　　Call switch_visible(False)
　　Me.Saved = True
End Sub

' --------------------------------------------------------------------
' イベント: ブックを閉じる時に自動的に実行される
' --------------------------------------------------------------------
'
Private Sub Workbook_BeforeClose(Cancel As Boolean)
　　If Me.Saved Then Exit Sub
　　' 未入力があったらブックの閉じるをキャンセルする
　　If Validate(Me.Worksheets("編集用"), "A4,B7,C8,D19") = False Then
　　　　Cancel = True
　　End If
End Sub

' --------------------------------------------------------------------
' イベント: ブックの保存前に自動的に実行される
' --------------------------------------------------------------------
'
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Validate(Me.Worksheets("編集用"), "A4,B7,C8,D19") = False Then
　　　　' 未入力があったらブックの保存をキャンセルする
　　　　Cancel = True
　　Else
　　　　' 保存前にダミーを表示、編集用を隠してから保存する
　　　　Application.ScreenUpdating = False
　　　　Call switch_visible(True)
　　End If

End Sub

' --------------------------------------------------------------------
' イベント: ブックの保存後に自動的に実行される
' --------------------------------------------------------------------
'
Private Sub Workbook_AfterSave(ByVal Success As Boolean)

' 保存できたら表示を元に戻す
　　Call switch_visible(False)
　　' シートの表示変更はブックへの変更とみなさない
　　Me.Saved = True

End Sub

' --------------------------------------------------------------------
' @機能 ワークシートの表示切り替え
' @引数 flag
'　　　　　True : 入力シートを隠す（→ダミーシートは表示）
'　　　　　False: 入力シートを表示（→ダミーシートは隠す）
' --------------------------------------------------------------------
'
Private Sub switch_visible(ByVal flag As Boolean)

Me.Unprotect Password:=MY_PASSWORD
　　If flag Then
　　　　Me.Worksheets("編集用").Visible = xlSheetVeryHidden
　　　　With Me.Worksheets("ダミー")
　　　　　　.Visible = xlSheetVisible
　　　　　　.Activate
　　　　End With
　　Else
　　　　With Me.Worksheets("編集用")
　　　　　　.Visible = xlSheetVisible
　　　　　　.Activate
　　　　End With
　　　　Me.Worksheets("ダミー").Visible = xlSheetVeryHidden
　　End If
　　Me.Protect Password:=MY_PASSWORD

End Sub

' --------------------------------------------------------------------
' @機能 未入力チェック関数
' @戻値 True 未入力なし　False 未入力あり
' @引数 sh　　　　　チェックするワークシート
' @引数 cell_list 　　チェックするセルのアドレスをカンマ区切りで
' --------------------------------------------------------------------
'
Private Function Validate( _
　　　　ByRef sh As Worksheet, _
　　　　ByRef cell_list As String _
) As Boolean

' SpecialCell で空白セルの参照を試みる
　　' この時本当に無いとエラーになるので On Error Resume Next で
　　' エラートラップ

On Error Resume Next
　　Dim blank_cell As Range
　　Set blank_cell = sh.Range(cell_list).SpecialCells(xlCellTypeBlanks)
　　On Error GoTo 0

' 空白セルがあれば変数 blank_cell に 空の Range が参照されているはず
　　' blank_cell is Nothing →漏れなし, Not blank_cell is Nothing →漏れあり

If Not blank_cell Is Nothing Then
　　　　Validate = False
　　　　sh.Activate
　　　　blank_cell.Select
　　　　MsgBox blank_cell.Address & "が未入力です", vbInformation, "入力チェック"
　　Else
　　　　Validate = True
　　End If

End Function

Qchan1962 · Answer

すみません
＃１　＞各プロシージャはイベントですが、VBEからも実行可能と思います
間違えですね。。Workbook_BeforeSave　Workbook_BeforeClose　は
保存、終了プロセスが実行されないと実行できませんのでブレークポイントなどを設けて確認してください。

あと、VBEのデバッグタグのコンパイルも押して確認してください
おそらくは、原因がつかめると思います

ヒントとしては
後から追加されたと思われる　Option Explicit　は
変数宣言を強制するものです。
プロシージャ内もしくはモジュール内で正しく変数を宣言してください

コンパイルエラーが解消されたら、On Error Resume Nextを（有効）戻し
一応、ステップ実行などで確認してみましょう。

どうでしょうか？（処理内容については評価していません）

Qchan1962 · Answer

＞頭に　' を付加する
のやり方が間違っていますか？

' On Error Resume Next

エラーが出ない場合は、F8キーで繰り返しステップ実行して
期待する処理コードが実行されているか確認してみましょう

If文で、はじかれている場合は、その値を確認してみましょう

Qchan1962 · Answer

こんにちは
拾ってきたコードは、そのままでは、使えない方が多いかと思います
コード内にはシート名が明示されているので、実際のシート名や環境に合わせて直す必要があると思います

＞（エラーメッセージはでません）
どこに問題があるのか分からないと直しようもないと思いますので
取り合えず、On Error Resume Next　各箇所　をコメントにしてください

頭に　' を付加する

エラーがあれば、デバッグできるようになると思います。

（各プロシージャはイベントですが、VBEからも実行可能と思いますので
各変数など確認してください。

無視して良いケース？（本来飛ばしたいエラー）のエラーでも止まりますので改修後、戻すか、環境に合わせエラールーチンを作成組み込む必要があると思います

現在、コードの評価は行っていませんので、具体的な場所を指摘できませんのでご自分でエラーを確認してください

2つのＶＢＡを一緒にしたら機能しなくなりました(エクセル)

ちなみにスペースのみだと見た目空白に見えますけど、判定としては入力あり＝値ありとなるので、Validate関数のチェック通りますけ

こちらでは動いてますが、、少し気になっていたとこがあります。

こんばんは。

こんにちは。

すみません

＞頭に ' を付加する

こんにちは

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

＞頭に　' を付加する