HOME > Q&A > Excel （VBA）

Excel （VBA）
Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
特定のセル範囲が変更されたときに処理を実行する（を利用して第三者に入力させないようにしたい）
投稿日時: 24/09/08 14:06:35
投稿者: けけちゃま

いつもお世話になっております。
連日の質問大変申し訳ございません。
昨晩から色々調べまわっているのですが、どうしてもわからず再度ご質問でございます。
 
というのも、とあるエクセルで管理表を作成をしているのです。
 
オートシェイプでボタンを設け、それを押すとフォームが起動し、フォーム内の実行ボタンを押すと、フォームに記入されたテキストが、Sheet1やSheet2の指定した箇所に反映され、アウトルックが起動するというものです。
 
 
その管理表は、完成したら共有ファイルとしてシャアポイントに格納する予定です。
勿論、マクロ付エクセルファイルは、Webブラウザー上では機能しないみたいなので、閲覧だけでなくファイルを更新したい人はその都度デスクトップアプリで開いてもらうことになります。
 
 

引用:
フォームに記入されたテキストが、Sheet1やSheet2の指定した箇所に反映され

 
この↑コードは、入力箇所を設定するときにCurrentRegionプロパティ（メゾット？）を使っているので、隣接するセルに第三者による入力を防ぎたいのです。
 
例えば、Sheet2のB列には第三者には入力させないようにしたいとなったときに、シート保護を最初に考えたのすが、なぜかマクロが動かなくなりました。
 
https://tonari-it.com/excel-vba-protect-userinterfaceonly/
 
上記サイトを見つけ「エクセルシート側で保護かけたらマクロもロックかかるんだな」と理解しました。
さらに続けてみると
 
引用:
マクロだけシート内の操作ができるシートの保護を設定する

 
も記載されてしました。
しかし、私は正直そこまで求めておらず、マクロでの入力を間違えたら、すぐに列削除や、カットコピーなどはVBAではなく、ワークシートに備わっているネイティブな機能を使いたいのです。
 
要は、『Sheet2の「B列」のみ、値が入力されないようしたい』のです。
（前置きが長くてすみません…）
 
 
そこでネットで探し回ってたどり着いたのが下記のサイトによるコードです。
https://www.moug.net/tech/exvba/0050131.html
 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:B5")) Is Nothing Then
        Exit Sub
    Else
        MsgBox "セルの値が変更されました"
    End If
End Sub

 
これを私の望む使用にかきかえると…
 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Columns("B")) Is Nothing Then
        Exit Sub
    Else
        MsgBox "ここには入力しないでください"
　　　　Target.Value=""

    End If
End Sub

 
これで実験すると無限ループになりさらにネットでしらべあげ、下記サイトの
https://terakoya.sejuku.net/question/detail/32336
 
「Application.EnableEvents = False」…①
「Application.EnableEvents = True」…②
 
を利用するのか！となりさらに下記のようにかきくわえました。 
Private Sub Worksheet_Change(ByVal Target As Range)

　　Application.EnableEvents = False

    If Intersect(Target, Columns("B")) Is Nothing Then
        Exit Sub
    Else
        MsgBox "ここには入力しないでください"
　　　　Target.Value=""

    End If

　　Application.EnableEvents = True
End Sub

 
これで1回だけ動いたのですが、2回目以降が動かないのです。
どうすれば私の望むような動き
『Sheet2の「B列」のみ、値が入力されないようしたい』
が実行されるのか、どなたかご教示いただけますでしょうか・・・。
 
もう、最悪無理だったら、利用者たちにB列には絶対なにもいれないで！っていうしかないけど、アウトルックまで一通り書き上げて、今は色んな可能性があったときの対策で色々コードを書き加えている段階なのでそこだけアナログなのも、ちょっと悔しいと思い…
 
 
回答
投稿日時: 24/09/08 14:47:35
投稿者: Suzu

引用:
これで1回だけ動いたのですが、2回目以降が動かないのです。

 
とりあえず、
Private Sub Worksheet_Change(ByVal Target As Range)
にブレイクポイントを設けて
B列に値を入れようとしたときに、そのイベントが実行されていますか？
 
実行されていないのでは？
 
このイベント自体は、
セルの値を変えたときに発生します。
 
A列の値を変えたときにも発生します。
そうすると、
 
Application.EnableEvents = False
を通過しますから、
イベント無効になってしまいますよね？
 
Application.EnableEvents = False
Application.EnableEvents = True
を入れる位置を工夫しましょう。
 
 
直接の話は以上ですが
引用:
この↑コードは、入力箇所を設定するときにCurrentRegionプロパティ（メゾット？）を使っているので、隣接するセルに第三者による入力を防ぎたいのです。

 
じゃぁ、CurrentRegion を使わなければ良い。
End(xlUp)等で代用できませんか？
 
 
引用:
マクロでの入力を間違えたら、すぐに列削除や、カットコピーなどはVBAではなく、ワークシートに備わっているネイティブな機能を使いたいのです。

？状況が理解できません。
マクロで入力を間違える？間違えない様マクロを使うのでは？
マクロの動作が間違えない様するのが先決なのでは？
 
 
引用:
アウトルックまで一通り書き上げて、今は色んな可能性があったときの対策で色々コードを書き加えている段階なのでそこだけアナログなのも、ちょっと悔しいと思い…

 
これは、その作業を金額を決めて請け負われているのでしょうか？
そうではなく、社内依頼であったり、どなたからか頼まれて
あるいは自発的 に作成しているのではありませんか？
 
その場合、コーディン者が、色々と考え、制限や対処を入れる事をしますが
独り善がりの 機能を追加し、そこに時間を掛けがちになります。
コーディング者の 自己満足に過ぎない事に悩みがちです。
 
使用者としては使い勝手が良ければ良い。悪ければ、その都度直せば良いでしょう。
 
大局的に考えコーディングを進める事をお勧めします。
 
引用:
私は正直そこまで求めておらず
は、コーディング者の自己満足的なこだわり。
 
プロなら、 UserInterfaceOnly で必要十分な効果が得られるなら それを選びますよね。
回答
投稿日時: 24/09/08 16:15:01
投稿者: WinArrow

suzuさんのレスにあるように
Application.EnableEvents = False
Application.EnableEvents = True
を記述するところが間違ってると思います。
 
ところで、
>要は、『Sheet2の「B列」のみ、値が入力されないようしたい』のです。
ということとならば、ＶＢＡに頼らず、
セルにロックを掛けてシート保護すればよいのでは？

回答
投稿日時: 24/09/08 16:23:25
投稿者: WinArrow

>第三者には、入六させない
は、人手入力を制御したいと理解しました。
 
では、誰ならば入力を許可するンんでしょう？
Worksheet_Change
イベントで記述したコードでは
第三者以外も入力できなくなりますが・・・・

投稿日時: 24/09/08 21:36:04
投稿者: けけちゃま

Suzu様
 
ありがとうございます！
F9とF8の使い方を今まで一番生かせた気がします！
Suzu様のヒントをいただき、下記で作って成功しました泣
 
 

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Columns("B")) Is Nothing Then
        Exit Sub
    Else
        MsgBox "ここには入力しないでください"

　　　　Application.EnableEvents = False

　　　　Target.Value=""

    End If

　　Application.EnableEvents = True

End Sub

 
 
引用:
End(xlUp)等で代用できませんか？

 
確かにです・・。
以前VBA教室に通っていたときは、CurrentRegion押しだったので、その手法は思いつきませんでした。
それなら、隣のセルに入力されてもがっつり指定範囲でやれますよね。
 
 
引用:
マクロで入力を間違える？間違えない様マクロを使うのでは？

 
伝わりにくくて申し訳ございません。。
たとえば、「2024/9/8」と入力したけど、実は「2024/9/7」と入力するべきだったという事態を想定しております。
（Text.BoxのChangeイベントの設定などは一応しているので、入力したテキストは自体はちゃんと反映するけど、そもそも入力するべきテキストがちがった場合は、すでにフォームを使って書いたデータを修正や削除しないといけないので、それはマクロ使わずエクセル上でやりたいって感じです）
 
引用:
コーディング者の 自己満足に過ぎない事に悩みがちです。

図星すぎでした（苦笑）
はい、全然請け負いでもないです（恐れ多すぎます）
ふつうの会社員で、ちゃんとしたシステムが完成するまでの1年間はエクセルで代用したいからという理由で、上司から頼まれました。（あとは私自身がストレスなく使えるものを作りたいという願望もあります）
 
引用:
プロなら、 UserInterfaceOnly で必要十分な効果が得られるなら それを選びますよね。

 
実は調べるうえで、そのProtectメソッド的なところにもたどり着いたのですが、また初めての構文だったので、先述のようにただの凡人初心者なので今回は敬遠しようと思います（T　T）。
でも、心得なども含め色々教えていただきありがとうございます。精進いたします（T　T）
投稿日時: 24/09/08 21:40:42
投稿者: けけちゃま

WinArrow　様
 
ありがとうございます。
正しいコードが掛けました！（前回コメント掲載のやつです）
 
（エクセルシートで設定する保護機能でいままでマクロが上手く機能したことなくて、それこそProtectメソッド的なものを使えればいいんですが、そこまでまだ理解がおいておらず・・・）
投稿日時: 24/09/08 21:41:19
投稿者: けけちゃま

皆様、ご教示頂きありがとうございます＞＜。