新規会員登録における電話番号登録必須化のお知らせ

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

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

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

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

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

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
教えて!goo グレード

A 回答 (7件)

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

ど、、その可能性は?
この回答への補足あり
    • good
    • 0
この回答へのお礼

いえ、スペースはありません。
試しに全選択して削除してから、保存してもそのままできる状態でございます。。。。

お礼日時:2022/06/09 16:04

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


下記の差し替えソースコードに該当部分を入れ替えて試して下さい。
表示してから隠すに処理の順番を変更しています。

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

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

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


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

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

' ------------------------------------------------------------------------
' @機能 ワークシートの表示切り替え
' @引数 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
    • good
    • 0
この回答へのお礼

KenKen_SPさん、何度もご丁寧に教えていただきありがとうございます。

差し替えコードで試してみました。
ダミー→有効化→編集用へ展開していきますが、未入力でも上書き保存がすんなりできてしまいました。エラーもでず。。。

>校閲→ブックの保護→パスワード入力で保護を解除し、その状態で上書き保存。
この状態で上書き保存すると、マクロが実行されて、パスワードがかかった状態になります。そして未入力でも保存できます。

>また、パスワードが平文になってますので、VBAプロジェクトも保護をかけないと意味がないです。
ご指摘の通りです。これは後々対応いたします。

KenKen_SPさんのpcでは、きちんと願い通りの動きをされているということですよね。
私は、何が間違っているのでしょう。。。。?
以前教えていただいた方法で、確認してみます。

お礼日時:2022/06/09 14:32

こんばんは。



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

■予想する原因

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

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

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

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

2. 実際のワークシート名とソース内の名前が一致していない
    • good
    • 0
この回答へのお礼

KenKen_SPさん、おはようございます。
ご回答ありがとうございます。

1.シートを追加しても同じでした。

全てのシートを非表示にしたいのではなく、最終は編集用シートを表示して閉じたいです。

ファイルを開けたとき、ダミー→有効化ボタンクリック→編集用へきちんと展開してくれました。
でもそれから保存しようとすると、エラーがでます。

2.私もそう思いあれこれ何十回も試したので、間違い無いと思います。


それから、申し訳ございませんが、急用で今日明日外出しなければいけなくなりました。
もし可能でしたら、引き続きご教示いただけましたら幸いです。宜しくお願いいたします。

お礼日時:2022/06/07 10:08

こんにちは。



横から失礼します。
以前同様のソースを書いた経験がありますが、とても難しかったと記憶してます。
あまり原型を残していないですけど、その時の記憶でサンプルソースを書いてみました。
全て 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
    • good
    • 0
この回答へのお礼

KenKen_SPさん
この度は、ご丁寧に新案をご教授いただきありがとうございます。
あれから色々トライしていたら、マクロが強制実行になったり、ぐるぐるしてしまい、今日になってしまいました。申し訳ございません。

保存しようとすると
<WorkseetクラスのVisibleプロパティを設定できません>が出てきてしまい、 Me.Worksheets("編集用").Visible = xlSheetVeryHiddenがヒットしました。でも、この私では再表示の何がいけないのかわかりませんでした。
もし、よろしければ教えていただけますでしょうか。よろしくお願いいたします。


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

Me.Unprotect Password:=MY_PASSWORD
If flag Then
Me.Worksheets("編集用").Visible = xlSheetVeryHidden

お礼日時:2022/06/06 16:24

すみません


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

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

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

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

どうでしょうか?(処理内容については評価していません)
    • good
    • 0
この回答へのお礼

ご丁寧にありがとうございます。
単語をひとつづつ確認しながら、トレイしています。
初心者でまことに申し訳ございません。。。。

また結果をご報告させていただきます。
よろしくお願いいたします。

お礼日時:2022/06/02 17:46

>頭に ' を付加する


のやり方が間違っていますか?

' On Error Resume Next

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

If文で、はじかれている場合は、その値を確認してみましょう
    • good
    • 0
この回答へのお礼

Qchan1962さん
何度もご丁寧に新案をご教授いただきありがとうございます。
あれから色々トライしていたら、マクロが強制実行になったり、ぐるぐるしてしまい、今日になってしましました、申し訳ございません。
まず①から別々にやってみましたら、
名前が適切でありません。と出て、下記がヒットしました。
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'この行から

やはり私には、何がエラーなのかわかりませんでした。
たびたびで申し訳ございませんが、ご教示いただけますと幸いです。
よろしくお願いいたします。

お礼日時:2022/06/06 16:29

こんにちは


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

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

頭に ' を付加する

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

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

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

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

早速お返事いただきまして、ありがとうございます。
そして申し訳ございません。

、On Error Resume Next 各箇所 をコメントにしてください
の意味が分からなかったのですが、
実行時エラーが発生してもマクロVBAを中断せずに、エラーが発生したステートメントの次のステートメントから実行を継続するこことらしいので、
各行の頭初めに' を付けてみましたが、やはりエラーがでませんでした。

頭に ' を付加する
のやり方が間違っていますか?
お手数をおかけしますが、教えていただけますでしょうか?よろしくお願いいたします。

お礼日時:2022/06/02 15:10

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

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

教えて!goo グレード

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

人気Q&Aランキング