Call Private Sub Worksheet_Change

[BrianAll]

Hello,

I need to call from Macro1 in Module 1 the "Private Sub Worksheet_Change(ByVal Target As Range)".
I tried with:
Application.Run "Sheet1.Worksheet_Change"
but it doesn't work.

Any help?

Thanks!

[Olly]

Put your code in a separate subroutine. Call that subroutine from both the worksheet change event, and from Macro 1.

e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
    Call foo
End Sub

Sub foo()
    'your code here
    Debug.Print "foo happened"
End Sub

Sub Macro1()
    Call foo
End Sub

[dotchiejack]

I need to call from Macro1 in Module 1 the "Private Sub Worksheet_Change(ByVal Target As Range)".

This is not possible.
post your Worksheet change code so that helpers can change it to work in a module.

[KOKOSEK]

Private Sub Worksheet_Change(ByVal Target As Range)
    Call macro1
End Sub

[dotchiejack]

When I read the question I think TS wants the opposite.

Sub macro1
Call Worksheet_Change
end sub

this is not possible

[KOKOSEK]

When I read the question I think TS wants the opposite.

Sub macro1
Call Worksheet_Change
end sub

this is not possible

You right. Sorry.
I can't get idea of that. What kind of results is expected?

[BrianAll]

Hello,

well, Macro 1 checks if all the column headers have an expected value "string".
If not, an error MsgBox is shown.
If an error is found I want to give the user to correct the wrong column header name.
I can do it with

Application.wait

but this doesn't allow the user to type on the Sheet.
The best solution would be:
- Error Found
- MsgBox Shown
- Let the user type on the Sheet to correct the mistake
- The Worksheet "recognize" the Change through

Private Sub Worksheet_Change(ByVal Target As Range)

- The check routine (

Go to ControllaColonne

) is performed again.

Sub Macro1()

Dim ArrayTitoli(13) As String
Dim Result As Integer
    
'   Declare Array with correct column headers
    ArrayTitoli(0) = "Anno Stagione"
    ArrayTitoli(1) = "Tema"
    ArrayTitoli(2) = "Fase di Nascita cod"
    ArrayTitoli(3) = "Classe"
    ArrayTitoli(4) = "Articolo"
    ArrayTitoli(5) = "Colore"
    ArrayTitoli(6) = "Quantità"
    ArrayTitoli(7) = "0 - DA RICEVERE"
    ArrayTitoli(8) = "1 - DA LANCIARE"
    ArrayTitoli(9) = "2 - TAGLIO"
    ArrayTitoli(10) = "3 - PREPARAZIONE"
    ArrayTitoli(11) = "4 - ASSEMBLAGGIO"
    ArrayTitoli(12) = "5 - RIFINITURE"
    ArrayTitoli(13) = "CHIUSE"

    
ControllaColonne:
    For i = 0 To 13
    With Range("A1")
        If .Offset(0, i) <> ArrayTitoli(i) Then
        Result = MsgBox("Verificare che i titoli corrispondano all'estrazione corretta." _
        & Chr(13) & "L'ordine corretto è il seguente:" _
        & Chr(13) & "" _
        & Chr(13) & "Anno Stagione" _
        & Chr(13) & "Tema" _
        & Chr(13) & "Fase di Nascita cod" _
        & Chr(13) & "Classe" _
        & Chr(13) & "Articolo" _
        & Chr(13) & "Colore" _
        & Chr(13) & "Quantità" _
        & Chr(13) & "0 - DA RICEVERE" _
        & Chr(13) & "1 - DA LANCIARE" _
        & Chr(13) & "2 - TAGLIO" _
        & Chr(13) & "3 - PREPARAZIONE" _
        & Chr(13) & "4 - ASSEMBLAGGIO" _
        & Chr(13) & "5 - RIFINITURE" _
        & Chr(13) & "CHIUSE" _
        & Chr(13) _
        & Chr(13) & "Errore nell colonna: " & i + 1 & ".", vbCritical, "Colonne non Corrispondenti")
        End If
    End With
    Next
    
    
'   ****PROBLEM****
    If Result > 0 Then
    Application.Run "Sheet1.Worksheet_Change", Range("A1")
    'Application.Wait (Now + TimeValue("0:00:10"))
    'GoTo ControllaColonne
    End If

End Sub

Thanks!

[YasserKhalil]

Try this

Sub Test()
    Application.Run "Sheet1.Worksheet_Change", Range("A1")
End Sub

[YasserKhalil]

I think it will be useful if you attached a sample workbook ...

[jindon]

BrianAll‎,


Try something like

    Run "sheet1.worksheet_change", Sheet1.Range("a1")

Change a1 to suite if needed.

[BrianAll]

It works!
Thanks jindon