質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.35%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

1回答

1749閲覧

【Excel VBA】Worksheet_Change内で、先頭と末尾にシート保護解除→保護を設定すると1004エラーが発生してしまう

user202102

総合スコア11

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2021/11/20 13:16

問題点

  • イベントプロシージャーWorksheet_Changeにおいて、下記の順番で処理を行うと1004エラーが起きてまう。
    1. シートの保護を解除する。

 2. A1またはB1のセルの値に変更が合った場合に、その値の色で当該セルを塗りつぶしを行う。(標準モジュールからCall)
3. 塗りつぶしたセルの1つ下のセルに、選択した色の文字列の日本語名を挿入する。
4. シートを保護する。(該当のセルは書式設定>保護>ロックのチェックを外しています。)

  • 上記3.でセルの変更があったので、再度イベントが発生してしまう。
  • 上記2.でシートが保護されてしまっており1004エラーが起きてしまう。(恐らく再度イベントが発生している途中でエラーが起きている?)
  • エラーは、呼び出したFunction内で起きている。(サンプルコード参照)

質問・解決したいこと

  • なぜイベントの最後にシートを保護しているのに、途中でシートが保護されてしまっているのでしょうか。
  • イベント内でセルの値が変更になった場合の処理の順序が把握できておりません。

なぜか一旦End Subに到達する前にIf Not Intersect(Target, Range("A1")) Is Nothing Thenを2回通ります。

  • イベント内でセルの値を変更しても、再度イベントが発生してしまわないような処理はできますか?

サンプルコード

※シートのA1およびB1には、「yellow」、「blue」の選択肢を設定しています。
Sheet1(Sheet1)

Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Unprotect If Not Intersect(Target, Range("A1")) Is Nothing Then If Range("A1").Value = "yellow" Then Range("A2").Value = "黄色" Call color_yellow(Range("A1")) ElseIf Range("A1").Value = "blue" Then Range("A2").Value = "青いろ" Call color_blue(Range("A1")) End If End If If Not Intersect(Target, Range("B1")) Is Nothing Then If Range("B1").Value = "yellow" Then Range("B2").Value = "黄色" Call color_yellow(Range("B1")) ElseIf Range("B1").Value = "blue" Then Range("B2").Value = "青いろ" Call color_blue(Range("B1")) End If End If ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub

Module1

Function color_blue(val As Range) With val.Interior .Pattern = xlSolid 'ここでエラー .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent5 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With End Function Function color_yellow(val As Range) ActiveSheet.Unprotect With val.Interior .Pattern = xlSolid 'ここでエラー .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End Function

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

自己解決

処理の先頭と末尾にApplication.EnableEvents = False/Trueを追記することで解決できました。
イベントがループしていたようです。

投稿2021/11/20 13:27

user202102

総合スコア11

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.35%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問