Change Worksheet_Change Event to normal macro

[nordicdust]

I need to change this Change_Event to a sub I can call from other sub

Is it possible

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cRow As Long
Dim eRng As Range


cRow = Target.Row
Set eRng = Range("F8:CX2000")

If Not Intersect(Target, eRng) Is Nothing Then
    With Application
        .EnableEvents = False

        With Sheet70 '<<--- Adjust the sheet here
            .Unprotect "Password"

            .Cells(cRow, 102) = IIf(Application.CountA(Me.Cells(cRow, 6).Resize(1, 101)) > 0, Cells(cRow, 5), "")
     .Protect "Password"
    End With

     .EnableEvents = True
     End With
End If
End Sub

[AlphaFrog]

The Worksheet_Change event procedure is triggered by changes to the worksheet. If your other sub makes changes to the worksheet, it is in effect calling the Worksheet_Change procedure.


If you want to make it a standalone procedure, You could use just this part of the code.

        With Sheet70 '<<--- Adjust the sheet here
            .Unprotect "Password"

            .Cells(cRow, 102) = IIf(Application.CountA(Sheets("Sheet1").Cells(cRow, 6).Resize(1, 101)) > 0, Sheets("Sheet1").Cells(cRow, 5), "")
     .Protect "Password"

Change the Sheet1 to the name of the sheet that was changed. And define the cRow as the row number that your code changed.

[nordicdust]

Thanks AlphaFrog
I get this Error.
I am not shure how I define the cROw


1.JPG

[AlphaFrog]

Target is not defined. In the Worksheet_Change procedure, Target is the cell(s) that were changed. In your procedure, you have to specify what cells were changed. You have to define Target as a range of cell(s).

[nordicdust]

Thanks for your help,

I am not shure on how to define that. It can be any Cell in Range("F8:CW1000")

Sub PivotDate()

Dim cRow As Long
Dim eRng As Range
Dim Target As Range
Dim Data As Object
cRow = ThisWorkbook.Sheets("DATA").Range("F8:CX1000")
Set eRng = ThisWorkbook.Sheets("DATA").Range("F8:CX1000")
Set Target.Row = ThisWorkbook.Sheets("DATA").Range("F8:CX1000")
cRow = Target.Row

    With Application
        .EnableEvents = False
        With Sheet70 '(DATA)
            .Unprotect "*DM2285dm2018*"

            .Cells(cRow, 102) = IIf(Application.CountA(Sheets("DATA").Cells(cRow, 6).Resize(1, 101)) > 0, Sheets("DATA").Cells(cRow, 5), "")
     .Protect "*DM2285dm2018*"
    End With

     .EnableEvents = True
     End With
End Sub