ExcelのVBAコードについて教えてください。

シート名「審査」に
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Z12")) Is Nothing Then
If Target = "電子無" Then
Call shp_move(ActiveSheet.Shapes("電子審査"), Range("L6"))
Call shp_move(ActiveSheet.Shapes("審査完了"), Range("L9"))
Call shp_move(ActiveSheet.Shapes("チェック完了"), Range("L12"))
Call shp_move(ActiveSheet.Shapes("PDF"), Range("L15"))
End If
End If
If Not Intersect(Target, Range("Z13")) Is Nothing Then
If Target = "電子有" Then
Call shp_move(ActiveSheet.Shapes("電子審査"), Range("L6"))
Call shp_move(ActiveSheet.Shapes("審査完了"), Range("L9"))
Call shp_move(ActiveSheet.Shapes("チェック完了"), Range("L12"))
Call shp_move(ActiveSheet.Shapes("消防PDF"), Range("L15"))
End If
End If
If Not Intersect(Target, Range("Y11")) Is Nothing Then
If Target = "紙申請" Then
Call shp_move(ActiveSheet.Shapes("紙審査"), Range("L6"))
Call shp_move(ActiveSheet.Shapes("紙審査完了"), Range("L9"))
Call shp_move(ActiveSheet.Shapes("紙PDF"), Range("L12"))
End If
End If
End Sub
Sub shp_move(shp As Shape, r As Range)
With r
shp.Top = .Top + (.Height - shp.Height) / 2
shp.Left = .Left + (.Width - shp.Width) / 2
End With
End Sub
があります。同じシートのセル値に対応して、図形を指定セルに移動できるコードです。
このコードだと問題があり、それを解決する方法を教えてください。
審査シートZ12には「=IF(AND(Y11="電子申請",Y12="無"),"電子無","")」の数式があり
Z13には「=IF(AND(Y11="電子申請",Y13="Web"),"電子有","")」の数式があります。又、Y11「=受付!D2」Y12「=受付!D12」Y13「=受付!E12」があり
それぞれのセル値が「シート名：受付」から引用されており、又、受付シートのセル値も同じシート内で色々な数式によりセルに反映されております。
受付シートでセル値を変更してもこのVBAコードでは上手く動作してくれません。
直接「Y11・Z12・Z13」に指定文字を入力すると上手く動作してくれます。
以前、受付シート側でのコード設定を教えて頂きましたが、受付シートには、元々複雑なVBAコードが設定されており、今回は「審査シート」にてコードを設定して図形を動かしたいでです。
何度か同じような質問をしており、恐縮ですが、
受付シートセル値に合わせて、審査シートの図形を上手く移動させる
解決方法を教えてください。
受付シートには
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tbl As Variant
Dim i As Integer
tbl = Array("D10", "D11", "E10", "E11", "F10", "F11")
With Worksheets("審査")
For i = 0 To 5
.Range("B" & 26 + i).Value = Range(tbl(i)).Value
If Range(tbl(i)).Value = "" Then
.Range("E" & 26 + i).Value = ""
Else
.Range("E" & 26 + i).Value = "後日図書の提出をお願いいたします。"
End If
Next i
End With
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("消防添").Visible = [R37] = "消防添"
途中省略
Sheets("INDX").Visible = [D2] = "電子申請"
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
があります。
受付シートを利用する場合は、元々のコードにどのように今回のコードを追加する方法を教えてください。
又、受付シートぼ場合はセルO35に「=IF(AND(D2="電子申請",D12="無"),"電子無","")」
O36に「=IF(AND(D2="電子申請",E12="Web"),"電子有","")」を設定しております。
よろしくお願いいたします。

No.2 ベストアンサー 回答者： Qchan1962 回答日時： 2022/05/26 19:00

こんばんは

現状で最も簡単な方法でやるなら
受付シートのPrivate Sub Worksheet_Change(ByVal Target As Range)
でしょうか・・・
D12とE12がどの様に入力されるのか、わからないので　正しく動くか分かりませんが、
D2セルの値がキーになるようなので
Sheets("INDX").Visible = [D2] = "電子申請"
この下に
Dim dmy
Call shape_branch(dmy)
Application.ScreenUpdating = True
で同じ受付シートのモジュールにプロシージャを2つ作成

Sub shape_branch(dmy)
Dim wS As Worksheet
Dim shpPDF As String
If Range("E12").Text = "Web" Then
shpPDF = "消防PDF"
ElseIf Range("D12") = "無" Then
shpPDF = "PDF"
End If
Set wS = Worksheets("審査")
Select Case Range("D2").Text
Case "電子申請"
Call shp_move(wS.Shapes("電子審査"), Range("L6"))
Call shp_move(wS.Shapes("審査完了"), Range("L9"))
Call shp_move(wS.Shapes("チェック完了"), Range("L12"))
Call shp_move(wS.Shapes(shpPDF), Range("L15"))
Case "紙申請"
Call shp_move(wS.Shapes("紙審査"), Range("L6"))
Call shp_move(wS.Shapes("紙審査完了"), Range("L9"))
Call shp_move(wS.Shapes("紙PDF"), Range("L12"))
End Select
End Sub

Sub shp_move(Shp As Shape, r As Range)
With r
Shp.Top = .Top + (.Height - Shp.Height) / 2
Shp.Left = .Left + (.Width - Shp.Width) / 2
End With
End Sub

条件式はD12E12の優先順位や両方ある場合など良く分からないので
直してください
Web、無は～PDFの名前の違うところだけでしたので文字列変数にしました
シェイプを戻すのはボタン？

この回答へのお礼

おはようございます。
いつもありがとうございます。
解決いたしました。
感謝いたします。

お礼日時：2022/05/27 09:13

No.1 回答者： fujillin 回答日時： 2022/05/26 17:59

こんにちは

＞受付シートでセル値を変更してもこのVBAコードでは上手く動作してくれません。
＞直接「Y11・Z12・Z13」に指定文字を入力すると上手く動作してくれます。
理由を理解しておられますか？
エクセルの計算によって値が変化した場合は、Changイベントが発生しないからです。

イベント処理で行いたければ、
1）発生元のシートでイベントをキャッチする
2）Calculateイベントでキャッチする
のどちらかになると思われます。

ただし、Calculateイベントの場合は（計算処理がまとめて行われるので）発生元のセル情報などは取得できませんので、対象セルを順にチェックする必要があると思われます。
（計算式が多く設定されていて、他の計算などが頻繁に発生するようだと、無駄に処理が動き、重くなる原因となるかもしれません）

発生元をキチンと整理できるのであれば、発生元のChangeイベントを取得する方が宜しいように思われます。
発生元がいろいろなシートに点在しているのであれば、ThisWorkbookモジュールに処理をまとめて記述するようにして、（シート、セル）を併せてチェックすればよろしいでしょう。

この回答へのお礼

回答ありがとうございます。
参考にさせて頂きます。

お礼日時：2022/05/27 09:12