Bloccare modifica celle Excel in base a criteri

Visto l'ottimo confronto, facciamo un po' di didattica

strCheck(0) = "REGIME FISCALE"

strCheck(1) = "TIPO INVIO"

Si può evitare caricando l'intero array:

arrCheck = Array("REGIME FISCALE", "TIPO INVIO")

in questo caso arrCheck va dichiarato Variant (vedi il codice) e LBound è 1 non 0.

Cosa che ho fatto anche per diffRow.

[CelleProtetteEsterne].Rows(cellCheck.Row - diffRow(1)).Select '... seleziona la riga del range esterno da svuotare

Selection.ClearContents '... ed elimina il contenuto

...

Ora che riguardo il codice, una prima micro-ottimizzazione potrebbe essere fatta sulle righe in rosso. Non ha senso selezionare per cancellare. Potrei usare la proprietà ClearContents direttamente.

Esattamente, già rettificato.

Non è entra in nessun ciclo quando inserisco una nuova colonna, ma le migliaia di righe nuove verranno confrontante tutte nell'intersect e non posso impedirlo.

Verranno confrontate, ma sempre una sola volta, quello che succede in realtà è che il Target che riceve, nel caso di righe o colonne intere, sarà esplorato dal ciclo For Next:

For Each cellCheck In Target

ma anche questo può essere impedito se dopo il controllo dell'intersezione iniziale riassegni il Target:

Set Target = Intersect(Target, [CelleProtetteEsterne])

circoscrivendolo alle sole celle di interesse.

Per quanto detto, io eviterei il riposizionamento, es:

Cells(cellCheck.Row, [ControlloRF].Column).Select '... sposta la selezione su "REGIME FISCALE"

in caso di Target multiplo lo faresti ogni volta, cosciente di questa cosa valuta tu. Puoi mettere ad esempio un controllo sul Count e farlo solo per il primo o l'ultimo.

Questo il codice:

Private Sub Worksheet_Change(ByVal Target As Range) 

Dim strMsg As String, arrCheck, strTitle As String, diffRow, cellCheck As Range 'Dichiaro le variabili 

    Application.EnableEvents = False 'Disabilita gli eventi 

    'Controlla se la modifica interessa una cella del range più ampio 

    If Not Intersect(Target, [CelleProtetteEsterne]) Is Nothing Then 

        Set Target = Intersect(Target, [CelleProtetteEsterne]) 

        'Inizializzazione delle variabili 

        strMsg = "La cella selezionata non prevede l'inserimento dati!" & Chr(10) & "Modificare il " 

        strTitle = "Attenzione" 

        arrCheck = Array("REGIME FISCALE", "TIPO INVIO") 

        ' assegna i delta 

        diffRow = Array([CelleProtetteInterne].Rows(1).Row - 1, [CelleProtetteEsterne].Rows(1).Row - 1, [ControlloRF].Rows(1).Row - 1) 

        For Each cellCheck In Target 

            If Cells(cellCheck.Row, [ControlloRF].Column).Value = [BloccoRF] Then 'Se il "REGIME FISCALE" non consente modifiche ... 

                If Not cellCheck.Value = "" Then '... e viene immesso un qualsiasi valore 

                    cellCheck.ClearContents '... elimina quanto inserito 

                    Cells(cellCheck.Row, [ControlloRF].Column).Select '... sposta la selezione su "REGIME FISCALE" 

                    MsgBox strMsg & arrCheck(1), vbExclamation, strTitle '... e avverte l'utente 

                End If 

            ElseIf Not Intersect(Target, [CelleProtetteInterne]) Is Nothing Then 'Altrimenti controlla il range interno 

                If Cells(cellCheck.Row, [ControlloTI].Column).Value = [BloccoTI].Value Then 'Se il "TIPO INVIO" non consente modifiche ... 

                    If Not cellCheck.Value = "" Then '... e viene immesso un qualsiasi valore 

                        cellCheck.ClearContents '... elimina quanto inserito 

                        Cells(cellCheck.Row, [ControlloTI].Column).Select '... sposta la selezione su "TIPO INVIO" 

                        MsgBox strMsg & arrCheck(2), vbExclamation, strTitle '... e avverte l'utente 

                    End If 

                End If 

            ElseIf Not Intersect(Target, [ControlloTI]) Is Nothing Then 'Altrimenti controlla se la cella selezionata è "TIPO INVIO" ... 

                If cellCheck.Value = [BloccoTI].Value Then '... se viene inserito il valore bloccante 

                    [CelleProtetteInterne].Rows(cellCheck.Row - diffRow(1)).ClearContents ' elimina il contenuto 

                    Cells(cellCheck.Row, [ControlloTI].Column).Select '... poi torna sulla cella tipo invio 

                End If 

            End If 

        Next cellCheck 

    ElseIf Not Intersect(Target, [ControlloRF]) Is Nothing Then 'Controlla se la cella selezionata è "REGIME FISCALE" 

        For Each cellCheck In Target 

            If cellCheck.Value = [BloccoRF].Value Then '... se viene inserito il valore bloccante 

                [CelleProtetteEsterne].Rows(cellCheck.Row - diffRow(2)).ClearContents 'elimina il contenuto 

                Cells(cellCheck.Row, [ControlloRF].Column).Select '... poi torna sulla cella tipo invio 

            End If 

        Next cellCheck 

    ElseIf Not Intersect(Target, [ControlloID]) Is Nothing Then '... Altrimenti 

        For Each cellCheck In Target 

            If [ControlloRF].Rows(cellCheck.Row - diffRow(3)).Value = [BloccoRF].Value Then '... se viene inserito il valore bloccante 

                [CelleProtetteEsterne].Rows(cellCheck.Row - diffRow(3)).ClearContents ' elimina il contenuto 

                Cells(cellCheck.Row, [ControlloRF].Column).Select '... poi torna sulla cella tipo invio 

            End If 

        Next cellCheck 

    End If 

    Application.EnableEvents = True 'Riattiva gli eventi 

End Sub

Ciao.

In questo codice ho aggiunto altri 3 eventi da controllare.

I primi due servono ad impedire modifiche in celle già bloccate, mentre gli altri 3 servono a cancellare i valori di celle che vengono bloccate in un secondo momento e che in precedenza erano libere e, potenzialmente, potrebbero essere state popolate.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strMsg As String, strCheck(1) As String, strTitle As String, diffRow(2) As Integer, cellCheck As Range 'Dichiaro le variabili 

Application.EnableEvents = False 'Disabilita gli eventi 

'Controlla se la modifica interessa una cella del range più ampio 

If Not Intersect(Target, [CelleProtetteEsterne]) Is Nothing Then 

    'Inizializzazione delle variabili 

    strMsg = "La cella selezionata non prevede l'inserimento dati!" & Chr(10) & "Modificare il " 

    strCheck(0) = "REGIME FISCALE" 

    strCheck(1) = "TIPO INVIO" 

    strTitle = "Attenzione" 

    diffRow(0) = [CelleProtetteInterne].Rows(1).Row - 1 '... calcola lo spostamento del range interno rispetto alla prima riga 

    For Each cellCheck In Target 

        If Cells(cellCheck.Row, [ControlloRF].Column).Value = [BloccoRF] Then 'Se il "REGIME FISCALE" non consente modifiche ... 

            If Not cellCheck.Value = "" Then '... e viene immesso un qualsiasi valore 

                cellCheck.ClearContents '... elimina quanto inserito 

                Cells(cellCheck.Row, [ControlloRF].Column).Select '... sposta la selezione su "REGIME FISCALE" 

                MsgBox strMsg & strCheck(0), vbExclamation, strTitle '... e avverte l'utente 

            End If 

        ElseIf Not Intersect(Target, [CelleProtetteInterne]) Is Nothing Then 'Altrimenti controlla il range interno 

            If Cells(cellCheck.Row, [ControlloTI].Column).Value = [BloccoTI].Value Then 'Se il "TIPO INVIO" non consente modifiche ... 

                If Not cellCheck.Value = "" Then '... e viene immesso un qualsiasi valore 

                    cellCheck.ClearContents '... elimina quanto inserito 

                    Cells(cellCheck.Row, [ControlloTI].Column).Select '... sposta la selezione su "TIPO INVIO" 

                    MsgBox strMsg & strCheck(1), vbExclamation, strTitle '... e avverte l'utente 

                End If 

            End If 

        ElseIf Not Intersect(Target, [ControlloTI]) Is Nothing Then 'Altrimenti controlla se la cella selezionata è "TIPO INVIO" ... 

            If cellCheck.Value = [BloccoTI].Value Then '... se viene inserito il valore bloccante 

                [CelleProtetteInterne].Rows(cellCheck.Row - diffRow(0)).Select '... seleziona la riga del range interno da svuotare 

                Selection.ClearContents '... ed elimina il contenuto 

                Cells(cellCheck.Row, [ControlloTI].Column).Select '... poi torna sulla cella tipo invio 

            End If 

        End If 

    Next cellCheck 

ElseIf Not Intersect(Target, [ControlloRF]) Is Nothing Then 'Controlla se la cella selezionata è "REGIME FISCALE" 

    diffRow(1) = [CelleProtetteEsterne].Rows(1).Row - 1 '... calcola lo spostamento del range esterno rispetto alla prima riga 

    For Each cellCheck In Target 

        If cellCheck.Value = [BloccoRF].Value Then '... se viene inserito il valore bloccante 

            **[CelleProtetteEsterne].Rows(cellCheck.Row - diffRow(1)).Select '... seleziona la riga del range esterno da svuotare** 

            **Selection.ClearContents '... ed elimina il contenuto** 

            Cells(cellCheck.Row, [ControlloRF].Column).Select '... poi torna sulla cella tipo invio 

        End If 

    Next cellCheck 

ElseIf Not Intersect(Target, [ControlloID]) Is Nothing Then '... Altrimenti 

    diffRow(2) = [ControlloRF].Rows(1).Row - 1 '... calcola lo spostamento del range "REGIME FISCALE" rispetto alla prima riga 

    For Each cellCheck In Target 

        If [ControlloRF].Rows(cellCheck.Row - diffRow(2)).Value = [BloccoRF].Value Then '... se viene inserito il valore bloccante 

            [CelleProtetteEsterne].Rows(cellCheck.Row - diffRow(2)).Select '... seleziona la riga del range interno da svuotare 

            Selection.ClearContents '... ed elimina il contenuto 

            Cells(cellCheck.Row, [ControlloRF].Column).Select '... poi torna sulla cella tipo invio 

        End If 

    Next cellCheck 

End If 

Application.EnableEvents = True 'Riattiva gli eventi 

End Sub

Ora che riguardo il codice, una prima micro-ottimizzazione potrebbe essere fatta sulle righe in rosso. Non ha senso selezionare per cancellare. Potrei usare la proprietà ClearContents direttamente.
è un refuso perchè usavo la selezione come test prima di provare la cancellazione vera e propria.
Ma questo di certo non velocizza granchè.

Ripeto, il codice va in palla quando elimino/aggiungo righe/colonne.

Perchè questo evento Worksheet_Change viene valutato per migliaia di celle, nonostante l'unica cosa che faccia sia:
Dichiarare delle variabili,
Fermare la ridondanza dell'evento stesso
Controllare le varie intersezioni d'interesse (qui è richiesto il tempo vero e proprio)
Riattivare gli eventi

Non è entra in nessun ciclo quando inserisco una nuova colonna, ma le migliaia di righe nuove verranno confrontante tutte nell'intersect e non posso impedirlo.

E non puoi aggiungere un controllo sul target alto 1 riga con un numero di colonne che supera la tua tabella, uscendo dal controllo dell'evento?

È necessario, non può sapere a priori cosa ti occorre: è per quello che si usa l'Intersect, proprio per escludere i casi in cui non vuoi proseguire nella verifica.

Infatti è proprio questo che fa il codice, usa l'intersect per escludere i casi. Però quando aggiungi una riga (o peggio una colonna), ci sono migliaia di celle per cui valutare l'intersect.

In altri ambienti, il trigger non scatterebbe proprio, se non si toccano le celle scatenanti l'azione.

c'è però un caso che causa lentezza, ovvero quando inserisco o elimino una riga dal foglio (per lo meno in mezzo alla tabella).

Questo perchè purtroppo VBA esegue il codice per ogni singola cella che viene modificata (e un'intera riga è bella lunga). Non ci sarebbe un modo per far scattare il trigger solo per le celle che mi interessano?

E non puoi aggiungere un controllo sul target alto 1 riga con un numero di colonne che supera la tua tabella, uscendo dal controllo dell'evento?

e mai possibile che il metodo "on change" debba coinvolgere l'intero foglio, senza essere limitato alle celle di mio interesse?

È necessario, non può sapere a priori cosa ti occorre: è per quello che si usa l'Intersect, proprio per escludere i casi in cui non vuoi proseguire nella verifica.

Poi magari posto il codice completo.

Certo, hai visto che offre spunti per migliorarsi,

ciao e grazie per tutti i riscontri.