O intervalo de intersecção VBA só deve funcionar em folhas com o intervalo de destino definido, mas ignorar esse pedido

Question

Para reinicializar um menu suspenso filho (suspenso 2 dependendo da escolha feita no menu suspenso 1), eu tenho o código abaixo em "ThisWorkbook".

Deverá limpar o conteúdo da lista suspensa filho se uma célula de um intervalo específico (onde está a lista suspensa 1) for alterada. Ele funciona bem na planilha onde esse nome de intervalo existe (o nome do intervalo é "RSTcabFINISHING").

PROBLEMA

em qualquer outra planilha (= planilha onde este nome de intervalo não existe), este código está limpando o conteúdo de quaisquer 3 células diretamente para uma célula que eu mudo o conteúdo.

A solução seria colocar o código apenas na folha que precisa, suponho. Mas (diga-me Se eu estiver errado), parece fazer mais sentido colocar o código em "ThisWorkbook", já que a planilha com o intervalo descrito acima está sujeita a ser multiplicada (= copy) ...

Não tenho certeza se tudo isso faz sentido ... Eu acho que a pergunta é "o que devo fazer para evitar que esse conteúdo de compensação aconteça em qualquer outro lugar além do que eu quero?".

Thx!

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Initialize cabinet finishing board
On Error Resume Next
If Not Intersect(Target, Range("RSTcabFINISHING")) Is Nothing Then
    Application.EnableEvents = False
    Target.Offset(0, 1).Resize(, 3).ClearContents
End If

Exit Sub
End Sub

    

Answer 1

Ok, o (s) seu (s) problema (s) são provenientes principalmente do uso de On Error Resume Next .

1. Sua instrução if lançará um erro se o intervalo não existir. Isso significa que não será avaliado e, em vez disso, você passará para a próxima linha devido ao currículo seguinte.

2. Agora desativará os eventos. Assim, o seu evento só será acionado uma vez, já que você nunca parece reativá-lo.

3. Agora, limpará todo o conteúdo, sem nunca ter testado sua interseção.

Então, precisamos fazer duas coisas:

1. Corrigir a avaliação
2. Desconecte o Application.EnableEvents = false ou, pelo menos, corrija-o. A maioria dos eventos só é gerada pelo usuário fazendo alterações em vez do script VBA, portanto, é possível ignorar isso completamente.

A melhor maneira de corrigir isso seria chamar uma função mais segura que verifique se o intervalo existe e retorna verdadeiro ou falso, dependendo desse resultado.

Algo parecido com isso pode ser mais útil para você:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Initialize cabinet finishing board
    If RangeExists("RSTcabFINISHING") Then          'See below for what this does. We only progress if we get true.
        If Not Intersect(Target, Range("RSTcabFINISHING")) Is Nothing Then
            Target.Offset(0, 1).Resize(, 3).ClearContents
        End If
    End If
End Sub

Function RangeExists(RangeName As String)
    On Error GoTo NotValid:             'If we fail, jump to "NotValid"
    If Range(RangeName).Rows >= 0 Then  'Test if the range exists - if it doesn't we'll get an error, if it does it
                                        'will have 0 or more rows
        RangeExists = True              'We didn't error and we do have 0 or more rows, so the function is true
    End If
    Exit Function                       'Break out of the function before we hit our error handler
NotValid:                               'We only get this far if an error was thrown (Range doesn't exist)
    RangeExists = False                 'Flag the function as false before returning
End Function