エクセルのマクロ実行について

質問

ボタンではなく動作を受けてマクロを実行させることはできませんか？ 例えば、あるセルに「2」を入れると実行する等

回答No.7

Wendy02 · ベストアンサー

こんにちは。

今、読み返してみると、私の文章はおかしいです。コードは一気に書きますが、文章、足したり消したり、縦書きにしたり横書きにしたりして書いています。

こちらの含みのメッセージに良く気がつかれましたね。さすがです!
私は、聞かれない前には、ほとんどあれこれ書かないことにしています。そうしないと長く続けられません。他人のコードに疑問を感じることも大事ですね。

If Target.Count > 1 Then Exit Sub　

これは、Changeイベントの、お約束ということで、ロックで開いているところがひとつなら、ここは必要ありません。二つ開いて処理したりすると、以下のような部分でエラーが出ます。

あくまでも、Target は、ひとつ分しか想定していないからです。
2個分入るとエラーが発生します。（配列値になるからです)

　If Target.Address <> "$B$4" Then Exit Sub
　If Target.Value = 2 Then

>なぜロックシート？ということで呼び出さなければならないのか。
実は、サブルーチン LockSheet そのものは、意味を持っていないからです。だから、取り外しが簡単なように、別にしました。

B4さえ、アンロック状態なら、ThisWorkbook モジュールの　WorkBook_Open などの起動時に、

Private Sub Workbook_Open()
With Worksheets("Sheet1")
　.UnLock
　.Protect, _
　 DrawingObjects:=True, _
　 Contents:=True, _
　 Scenarios:=True, _
　 UserInterfaceOnly:=True
End With
End Sub

とするだけで、最後のUserInterfaceOnly という単語が生きていて、マクロ側からは、シートのプロテクトでエラーが出ません。

>多少無理があるコードのようですね。

実は、私は、個人的には、あまり、Change イベントを使いません。今まで、Excel 2003 までですと、イベントの再発生は、1万回ぐらいで、イベントとマクロコードのタイムラグが生じて停止するのですが、Excel 2007 では、スピードが速いせいか、ハング状態になってしまいます。

Application.EnableEvents = False

だから、イベント・スイッチをOffにして使うわけです。
今回の元のマクロも、イベントの再発生が発生しています。入力の自セル(B4)以外に値を置く場合は、イベントの再発生してしまいます。

　　　　Range("V12:AL15").Delete Shift:=xlUp
　　　　Range("V12:AL15").Insert Shift:=xlDown
　　　　Range("V12:V13").Value = "管理部署"
　　　　Range("V14:V15").Value = "設置場所"
----------------------------------------------
　　　　Range("V12:V13").Value = "移動先　管理部署"
　　　　Range("V14:V15").Value = "移動先　設置場所"

この二つ範疇で発生してしまいます。ボタン・クリックには、そうしたイベント再発生がありません。

イベント・ドリブン型マクロというのは、なかなか難しいです。コードの中では、出来る限りエラーが発生しないようにして作ることが大事なのですが、一旦、エラーが発生すると、イベントが動かなくなってしまいます。そのたびに、Application.EnableEvents = True　という方法も、あるのですが、ユーザーさんに、それを説明するのが、面倒なのです。そういう自力回復できないマクロは、不完全だと思います。

安易に、On Error Goto ErrHandler というエラー・トラップを設けることもあるのですが、全体のコードが格好が悪いです。それを他の方法で回復する方法もあるのですが、それは、実務上の問題ですから、掲示板では相応しくないような気がします。アマチュアとプロの差というのは、エラー処理の仕方だといわれる所以です。たかが、VBAマクロなのですが、それでも、完ぺきを目指さなくてはならない時があります。プロの方でも、VBAマクロになるとテンションが下がる人もいます。

人に使わせるマクロって、簡単なことでも、ものすごく難しいのです。悔しいかもしれませんが、よほど実力が付くまでは、ユーザーのために、必要以上はプロジェクト・ロックはしないということです。

お礼

今回はマクロをあきらめました（少し悔しいですが）。
条件付き書式というのがあるのを思い出して、
白くするということで対応しました。
ありがとうございました。

補足

こんにちは。
ご丁寧にありがとうございます。
実は、夜に教えていただいたコードのファイルをいじっていたら、B4以外のアンロックセル（イベントには関係ないただの入力セル）をいじって保存すると、マクロが無効になることがわかりました。やっぱりChange イベントにこの動作がひっかかりこのようなことが起こってしまうかなと思いました。（2007のExcelで97-2003の互換モードで作成しているためそのことも多少影響があるのかもしれませんが・・・）いったんファイルを閉じてマクロを有効にすれば回避はできますが、それだと意味がないので、正直自分のレベルからするとこのイベント・ドリブン型マクロというのは、使いこなせないかなと思いました（せっかく教えていただいたのにすみません。）。作業を低減してもらおうと思って配ったもので困らせてしまうのは本末転倒ですからね。

ボタンをつけてのマクロを作成して試みているのですが、コピーのところでエラーが出てしまい（上書きしてもいいですか？とメッセージが出てマクロが止まる）、その回避を考えています。

書いていただいた内容でよくわからないところがあり、今後のためにできれば教えていただきたいのですが、・・・

Private Function LockSheet(flg As Boolean)
ActiveSheet.Protect , _
DrawingObjects:=flg, _
Contents:=flg, _
Scenarios:=flg, _
UserInterfaceonly:=True
End Function

の代わりに、下記文章をかいたらコンパイルエラーが出てしましました（Sub またはFunctionが定義されていません）なぜだか検討はつきますでしょうか。（面倒でしたら回答なしでOKです＾＾）

Private Sub Workbook_Open()
With Worksheets("Sheet1")
.UnLock
.Protect , _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceonly:=True
End With
End Sub

回答No.8

Wendy02

こんにちは。

お礼と補足の前後関係がわからないので、答えようがありませんが、

以下は、このようにして、ThisWorkbook に取り付ければよいですが……。
.Unlock は、間違いでした。

Private Sub Workbook_Open()
With Worksheets("Sheet1")
　　　　.Unprotect　'ここは修正してください。
　　　　.Protect , _
　　　　DrawingObjects:=True, _
　　　　Contents:=True, _
　　　　Scenarios:=True, _
　　　　UserInterfaceonly:=True
　　End With
End Sub

お礼

ご回答ありがとうございました。
Private～についてまだ理解できていない部分が多いので
勉強してみます。

回答No.6

Wendy02

こんばんは。

コードとしては、少し無理があるように見受けられます。
B4をロックしたら、このコードはまったく動きません。
この場合は、トグルボタンとかどの方が確実です。なぜ、ボタンではいけないのか、良く分かりません。セルに入力するというコードの方が複雑になり、不安定になります。

Application.EnableEvents = False
これで、イベントの再読み込みは防いではいるのですが、コード中にエラーが発生すると、手動か別のマクロで、EnableEvents = True にしなければなりません。

Range("V12:V13").FormulaR1C1 = "=IF(R4C2=1,""移動先　管理部署"",IF(R4C2=2,""管理部署"",""""))"

B4 で判定させるなら、数式の必要はないと思います。

'------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
　　Call LockSheet(False)
　　If Target.Count > 1 Then Exit Sub　'アンロックセルが2つ以上ある場合

　　Application.EnableEvents = False

　　If Target.Address <> "$B$4" Then Exit Sub

　　If Target.Value = 2 Then
　　　　Range("V12:AL15").Delete Shift:=xlUp
　　　　Range("V12:AL15").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
　　　　Range("V12:V13").Value = "管理部署"
　　　　Range("V14:V15").Value = "設置場所"

　　　　Range("B4").Select

　　Else
　　　　Range("A12:Q15").Copy Range("V12")
　　　　Range("V12:V13").Value = "移動先　管理部署"
　　　　Range("V14:V15").Value = "移動先　設置場所"

　　　　Range("B4").Select
　　End If
　　Application.EnableEvents = True
　　Call LockSheet(True)
End Sub
Private Function LockSheet(flg As Boolean)
　　　ActiveSheet.Protect , _
　　　DrawingObjects:=flg, _
　　　Contents:=flg, _
　　　Scenarios:=flg, _
　　　UserInterfaceonly:=True
End Function

補足

（寝てしまいました。）
おはようございます。
ご回答ありがとうございます。
書いていただいたコードで行ったところ、目的の内容を行うことができました。

このファイルは依頼票で、マクロなどあまりわからない多数の人間に配布し、記入印刷してもらったものを提出してもらうために作成しています。
マクロを意識しないで（ボタンを押すというひと手間をさせないで）ファイルを使用してもらおうと思い、ボタンなしでというマクロにしようと思ったのですが、多少無理があるコードのようですね。

自分が理解できていない部分があるのでこのままファイルを配布するかどうか悩んでいます。

わからない部分についてご教授いただけると助かります。

このシートの中で、B4以外にも複数のセルについてセルのロックをはずしてシート全体に保護をかけています。
ただし
Range("V12:AL15")
Range("V12:AL15")
Range("V12:V13")
Range("V14:V15")
についは保護をかけている部分なので（セルがロックされている状態）、当初保護をいったん外すコードを記入していました。

Call LockSheet(False)

If Target.Count > 1 Then Exit Sub　

Application.EnableEvents = False

If Target.Address <> "$B$4" Then Exit Sub

上記のコードの意味をもう少し詳しく教えていただけないでしょうか。
なぜロックシート？ということで呼び出さなければならないのか。
またターゲットカウントをいう概念で分岐しなければならないのか。

Private Function LockSheet(flg As Boolean)
ActiveSheet.Protect , _
DrawingObjects:=flg, _
Contents:=flg, _
Scenarios:=flg, _
UserInterfaceonly:=True
End Function

上記文章についてもヘルプなどで調べてみたのですが、どういう意味かわかりませんでした。
保護を外したシートに再度保護をかけている？という作業行っているのですか？

お手数をおかけいたしますが、よろしくお願いいたします。

回答No.5

xls88

If Target.Address = Range("B4").Address And Target.value = 2 Then
としてみてください。

回答No.4

hallo-2007

横からすみません。コードを記述する場所が違っていると思います。
シート名のタブが下にありますが、それを右クリックして、コードの表示をクリック
VBエディターが起動したら、
Private Sub Worksheet_Change(ByVal Target As Range)
と
End Sub
の間にコードを記述します。

補足

ご回答ありがとうございます！
今度は
If Target.Address = Range("B4") And Target = 2 Then　のところで型が違うというメッセージがでてしまいました。

何かだめなのか検討がつきますでしょうか。。

回答No.3

takesun

マクロの中身までみていませんが
頭のSub Macro5()が不要だと思います。

回答No.2

okormazd

SheetのコードにWorksheet_Changeイベントを書く。

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address=(あるセル) And Target=2 then
(処理)
end if

End Sub

補足

ご回答ありがとうございます！
でも・・・コンパイルエラーが出てしまいました。
「名前が適切ではありません：Worksheet_Change」
できればご教授いただけると助かります。。

↓マクロ全文です。
--------------------------------------------------------
Sub Macro5()

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("B4") And Target = 2 Then

ActiveSheet.Unprotect

Range("V12:AL15").Select
Selection.Delete Shift:=xlUp
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Range("B4").Select

Else

ActiveSheet.Unprotect

Range("A12:Q15").Select
Selection.Copy
Range("V12").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("V12:V13").FormulaR1C1 = "=IF(R4C2=1,""移動先　管理部署"",IF(R4C2=2,""管理部署"",""""))"
Range("V14:V15").FormulaR1C1 = "=IF(R4C2=1,""移動先　設置場所"",IF(R4C2=2,""設置場所"",""""))"

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("B4").Select

End If

End Sub

回答No.1

imogasi

シートのセルのChangeイベントの例をGoogleででも検索して、勉強のこと。しょっちゅうVBAの質問の回答でも出ているよ。

補足

ご回答ありがとうございます。
さっそくGoogleで検索してみたのですが、自分のやりたいことと結びつけができないです。勉強不足ですね。
月曜までに仕上げたいので、困っています。
一般のプロシージャ（標準モジュール）に作成するのではなく、オブジェクトに対して直接作成する形式と取らないとだめなのでしょうか。