シートの速度を落とし、vbaのif関数の後に他のすべてのコンテンツを消去します

Aug 22 2020

私は、果物が選択されたセルがないかのように、すべてのセルを保護してロックしたシートを持っていB2ます。私は次のようなコードを書きました：セルに「Mango」と入力B2すると、シートは保護されなくなり、セルのロックが解除されJ2、ワークシートが再び保護されます。次に、J2「15」のようにセルに数値を入力すると、行15に行が挿入されます。次に、をマージしA15:C15ます。それはうまく動作します。ただし、問題は、行を挿入すると、その行の下にある他のすべてのコンテンツが消えてしまうことです。さらに、このコードでは私のシートが欠けています。私に何ができる。助けてください。コードは参照用に提供されています：

Private Sub Worksheet_Change(ByVal Target As Range)

Dim inputCell As Integer
Dim copyRange As String
Dim ws As Worksheet
Dim validateCell As String
Dim fruitType As String

inputCell = Range("J2").Value
copyRange = "A" & inputCell
Set ws = Worksheets("Sheet1")
validateCell = Range("B2").Value
fruitType = "Mango"

If validateCell = fruitType Then
   ws.Unprotect Password:="mehedi"
   Range("J2").Locked = False
   ws.Protect Password:="mehedi"
End If

If Range("J2").Value >= 5 Then
   ws.Unprotect Password:="mehedi"
   Range(copyRange).EntireRow.Insert
   Range(copyRange & ":C" & inputCell).Merge
   Range(copyRange & ":C" & inputCell).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
   Range("D" & inputCell & ":F" & inputCell).Merge
   Range("D" & inputCell & ":F" & inputCell).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
End If
End Sub

@FlexYourDataの更新

回答

2 FlexYourData Aug 22 2020 at 13:13

範囲Worksheet_Changeを参照するために使用する場合は重要Targetです。

ダミーデータを設定しました。J2空白でした。Mangoと入力B2して、サブルーチンにステップインしました。開始すると、inputCellはゼロであることに注意してください（J2空白であるため）。

したがって、copyRange有効なアドレスではない「A0」になります。これはここでの問題の原因ではありませんが、他の場所で問題を引き起こす可能性があります。

Targetどのセルが編集されたかを確認するために使用できます。したがって、状況に応じて、次のようなものを使用します。

If Target.AddressLocal = "$J$2" Then
    'do something when J2 was changed
    
ElseIf Target.AddressLocal = "$B$2" Then
    'do something when B2 was changed
    
Else
    'do something else, or nothing

End If

現在のコードがを呼び出すたびに.EntireRow.Insert、Worksheet_Changeイベントが再度呼び出され、別の行が挿入され、イベントが再度呼び出され、別の行が発生します。実際、あなたのコードはこのような行を挿入し続け、以下のデータは消えたように見えると思います。これも速度低下の原因となる可能性があります。

したがって、Target特定のセルを変更した場合にのみマクロがアクションを実行するように（行を挿入しているときは実行しないように）、常に参照する必要があります。

検討したいもう1つの項目は、パスワードをこのシートの定数として、または別のモジュールのグローバル定数として配置することです。その後、ユーザーにそのパスワードを見せたくない場合は、ここで説明するように、VBAプロジェクトをロックする必要があります。これは、VBAプロジェクトで1回入力するだけでよいので、タイプミスの機会が少なくなるので、少し役立ちます。

ワークシートの後ろのコードに貼り付けて、これが機能するかどうかを確認します。Option Explicit変数がDim'dでない限り、システムが変数を受け入れないように強制するため、通常は良い考えです。

Option Explicit

Const pwd As String = "mehedi"

Private Sub Worksheet_Change(ByVal Target As Range)

Dim inputCell As Integer
Dim copyRange As String
Dim ws As Worksheet
Dim validateCell As String
Dim fruitType As String

'this is just giving a short name for the current sheet
Set ws = ActiveSheet

'set this at the top
fruitType = "Mango"

If Target.AddressLocal = "$J$2" Then
    'do something when J2 was changed
    inputCell = Target

    'only take action if the condition is met
    If inputCell >= 5 Then
        copyRange = "A" & inputCell
        
        ws.Unprotect Password:=pwd
        
        ws.Range(copyRange).EntireRow.Insert 'causes this event to fire again, so be careful~
        
        ws.Range(copyRange & ":C" & inputCell).Merge
        ws.Range(copyRange & ":C" & inputCell).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        
        ws.Range("D" & inputCell & ":F" & inputCell).Merge
        ws.Range("D" & inputCell & ":F" & inputCell).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        
        'perhaps you need this here as well?
        ws.Protect Password:=pwd
    End If
    
ElseIf Target.AddressLocal = "$B$2" And Target.Value = fruitType Then
    'only take action if the right fruit was entered into the right cell
    
        ws.Unprotect Password:=pwd
        ws.Range("J2").Locked = False
        ws.Protect Password:=pwd


End If

End Sub

編集：

上記のコードは、この一連の手順で問題なく機能します。

B2を除くすべてのセルがロックされています
B2に「マンゴー」と入力
シートは保護されていません、J2はロック解除されています、シートは保護されています
J2に15を入力します
行が現在の行15の上に挿入され、A15：C15がマージされて境界線が与えられ、D15：F15がマージされて境界線が与えられます

現時点で私が考えることができる唯一のことは、あなたが述べていないあなたの問題についての詳細があるということです。その場合、この問題をデバッグする必要があります。