Excel Workbook_SheetSelectionChange マクロについて

特定のセルの値をユーザーによって変えられないようにするために、
Workbook_SheetSelectionChangeを使ったのですが、
Workbook_SheetSelectionChangeでは、そのセルを選択した時にトリガが掛かり、
実際問題としてそのセルの値を変えられてしまった後の対応はしてくれません。
そこで、[Enter]が押されて、隣りのセルに移動(選択)されたことを掴んで、対処しています。
もっとスマートな方法はないでしょうか？
下のマクロでは、[Enter]を押さずに、マウスで他のセルをクリックして
抜けてしまうと、結果、値を変えられてしまいます。
・・・そのセルの値が変更されたことをキャッチして、eventマクロ
のトリガが掛かると、理想的かと、考えますが・・・
宜しくお願い致します。

Public DataORG As String
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.MoveAfterReturnDirection = xlToRight
If Target.Address = "$A$1" Then
DataORG = Target.Value
MsgBox "このセルの値を変更することはできません"
ElseIf Target.Address = "$B$1" Then
Application.EnableEvents = False
Range("$A$1").Value = DataORG
Application.EnableEvents = True
End If
End Sub

PS. 勿論、このままでは、セルA1には、未来永劫、値を入れることはできません。
　　他のルーチンにて「Application.EnableEvents = False」を設定して
　　入力を可能としております。
　　シートの保護も考えましたが、別の理由により、止めました。

No.1 回答者： WindFaller 回答日時： 2015/08/23 12:35

こんにちは。

私は、こういうご質問は大好きですね。（^^;

私も、同じような堂々巡りに入り込んでしまったことがあります。
でも、自分で考えているのが楽しいのではありませんか？
「シートの保護も考えましたが、別の理由」というのが、なんとなく分かります。
人から聞くと「なんだ、そんなことか」と思うかもしれませんし、いや、そうではない、と思うかもしれません。

それと、昔(Excel2003以前)ですと、私は、もうちょっと、がっちりしたマクロが組めた気がしますが、いろいろあって、Excelがわからなくなったせいか、今は、「ゆるい=loose」ものしか考えられなくなってしまいました。「ゆるさ」はゆるさとして、使うしかありませんね。

「なんだ、そんなレベル」か程度の内容です。MsgBox で注意もなにもありません。

'------
'ThisWorkbook モジュール(オブジェクトモジュール)
'//
Private DataORG As String '←オブジェクトモジュールに、Public キーワードは無駄です。
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "Sheet4" Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
　　Application.EnableEvents = False
　　Range("A1").Value = DataORG
　　Application.EnableEvents = True
End If
End Sub

Sub SetData()
　DataORG = "FirstMemory" '規定の値を決めてしまう。
　'または、　DataORG =Worksheets("Sheet1").Range("A1").Value
　'本当は、ここで、カスタムプロパティを使います。
End Sub

'------
''標準モジュール
Sub Auto_Open
　　 Call ThisWorkbook.SetData　'変数にセッティング
End Sub

'///

でも、これって、「入力規則」と同等だと思われる方は、違いますね。「入力規則」は削除キーで削除できてしまいます。ただ、一般的には、DataORGのデータの確保場所を、物理的(静的)に保管したほうがよいと思います。例えば、カスタムプロパティが真っ先に挙げられます。

この回答へのお礼

ありがとうございます。Intersectのコマンド、初めて知りました。
含んでいなければ、・・・・なるほど、感心しました。

２つ、質問させてください。
①当初は。。。。Workbook_SheetChange(xxxx)は、セルを選択した時に、確かにトリガが掛かっていたのですが、サジェスチョンをいただいて
組み直してみると、そうでもない・・・
intersectの意味することから考えると、[Enter]が入力された後に起動されているような気がするし。　　よくわからないところです。

②「カスタムプロパティ」の定義：文章から推測するに、例えば、静的に保管とは、シート上の特定セルを1つ決めておいて、そこにDataORGを入れておいて、参照することかと考えます。そういうものを「カスタムプロパティ」と表現したものと思います。正しいでしょうか？

以上、よろしくお願い申し上げます。

お礼日時：2015/08/23 22:55

No.2 回答者： WindFaller 回答日時： 2015/08/23 12:40

#1のコードにミスがありました。

アップロード時に入れ替えしていませんでした。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "Sheet4" Then Exit Sub
ではなく、
If Sh.Name <> "Sheet1" Then Exit Sub
でした。

訂正願います。

No.3 ベストアンサー 回答者： WindFaller 回答日時： 2015/08/24 08:57

こんにちは。

atom_28さんのコンセプトからすれば、私の書いたものは、意図しているものとは違うかもしれません。そのときは、また別なものを考えましょう。

>PS. 勿論、このままでは、セルA1には、未来永劫、値を入れることはできません。
>　　他のルーチンにて「Application.EnableEvents = False」を設定して
>　　入力を可能としております。

数式入力の状態は、マクロは待機状態になります。この時に、変更が出来てしまいます。

以下の話は、もう少し筋道建てれば分かりやすくなるのですが、とりあえず、②のカスタムプロパティ側から読んでみてください。

①　SheetChangeの場合
>intersectの意味することから考えると、[Enter]が入力された後に起動されているような気がするし。
その通り、[Enter]で起動しています。

SheetSelectionChangeは、本来、移動した時にイベントが発生しています。これも、誤解しやすいのです。

Not Intersect(Target, Range("A1")) Is Nothing
この意味は、二重否定ですから、すぐに分からりづらいです。なぜ利用したかというと、ピンポイントで削除は他の方法でも防げることでも、そこを含めた広い面を、消去(delete)などされると、プロテクト(保護)を掛けてない限りは、一般的には防ぐことはできません。

これは、行の削除でも対応します。

話が前後していますが、②のカスタムプロパティを利用したマクロを考えてみました。一例です。

'標準モジュール
Sub Auto_Open()　'ファイルを開いた時に設定
Dim Dummy As Variant
　 On Error Resume Next
　With ThisWorkbook　'カスタムプロパティに入っているかチェック
　　Dummy = .CustomDocumentProperties("DataORG").Value
　If Err() = 0 Then　'入っていれば次へ
　　GoTo EndLine
　End If
　End With
　 With ThisWorkbook.CustomDocumentProperties
　　　 .Add Name:="DataORG", _
　　　　　　LinkToContent:=False, _
　　　　　　Type:=msoPropertyTypeString, _
　　　　　　Value:="SettingName"　'設定された文字
　　End With
EndLine:
　　Call ThisWorkbook.SetData　'標準モジュールから、Private 変数は設定できません。
　　On Error GoTo 0
End Sub

'ThisWorkbook モジュール
'Private DataORG As String '前回の続きなら、すでに変数はあるはず。
Sub SetData()　'Private 変数に設定
　DataORG = ThisWorkbook.CustomDocumentProperties("DataORG").Value
End Sub


②「カスタムプロパティ」とは、「プロパティ」の詳細プロパティの中のユーザー設定

https://support.office.com/ja-jp/article/Office- …

プロパティに製作者や会社名を入れますが、自分の作った項目も登録できるようになっています。今回の全体の流れからすると、コンセプトが違いすぎるかもしれません。レジストリを使おうかと思う時に、こんな便利なものがあるということを思い出していただければよいかと思います。

サンプルマクロを紹介します。なお、他にも、隠しデータを入れる場所はいろいろあります。VeryHiddenのシートの中にデータを置いて使っている人もみかけます。
「静的(static)に保管」というのは、当たり前ですが、保管しても定数として消えていないことです。セルですと、消してしまうことがありますが、プロバティは知っていて行わなければ消えません。

サンプルマクロ
'//
Sub TestForCustomDProperties()
'初めての登録
　 On Error Resume Next
　 With ThisWorkbook.CustomDocumentProperties '--アクティブブックに対して
　　　 .Add Name:="テーマ", _
　　　　　　LinkToContent:=False, _
　　　　　　Type:=msoPropertyTypeString, _
　　　　　　Value:="イベント型マクロ"　　　　　 '--文字列型のプロパティ
　　　　.Add Name:="ログ番号", _
　　　　　　LinkToContent:=False, _
　　　　　　Type:=msoPropertyTypeNumber, _
　　　　　　Value:=9050730　　　　　　　　　　　 '--数値型のプロパティ
　　　　.Add Name:="登録日", _
　　　　　　LinkToContent:=False, _
　　　　　　Type:=msoPropertyTypeDate, _
　　　　　　Value:="2015/08/23"　　　　　　　　　　'--日付型のプロパティ
　　End With
　　If Err() <> 0 Then MsgBox Err.Description
　　On Error GoTo 0
End Sub
Sub OutputProperties()
Dim myTheme As String
Dim LogNum As Long
Dim tDate As Date
'出力
　With ThisWorkbook
　 myTheme = .CustomDocumentProperties("テーマ")
　 LogNum = .CustomDocumentProperties("ログ番号")
　 tDate = .CustomDocumentProperties("登録日")
　End With
　MsgBox myTheme & vbCrLf & LogNum & " :　" & tDate
End Sub
Sub ChangePropertyies()
Dim myTheme As String
'修正用
　With ThisWorkbook
　 .CustomDocumentProperties("テーマ").Value = "SheetChange とSelectionSheetChangeについて。"
　End With
　Call OutputProperties
End Sub

この回答へのお礼

WindFallerさん,遅くなりました。大変感謝です。
CustomDocumentProperties、今まで知りませんでした。
ファイル/情報/プロパティ/詳細プロパティで、見ることができるのですね。
そこにたどり着くまで、結構時間が掛かってしまいました。
スクリプトで、設定、読み出しができる故、存在することはわかりますが、
入口を見つけることがなかなかできませんでした。
カスタムプロパティは、今後に活用させていただきます。

また、SheetChange、SheetSelectionChangeの違いも改めて、理解しました。
Excelって、本当に、奥が深いと思います。
ありがとうございます。

お礼日時：2015/08/29 14:15