Como evitar exclusão ou alteração de valores referenciados no Excel?

Question

Eu tenho uma planilha do Excel que contém IDs únicos, e outra planilha que contém entradas que referenciam por valor (via Validação de Dados - Lista) aquelas IDs da primeira planilha.

Eu ativei a Validação de dados na segunda planilha, o que impede que os valores sejam inseridos na coluna de ID, se eles não existirem na primeira planilha.

No entanto, existe uma falha: o Excel ainda permitirá que um valor inválido seja introduzido na coluna de ID na segunda folha, tendo o valor de ID removido ou alterado na primeira folha depois de ser inserido na segunda folha.

Como posso forçar o Excel a não permitir a alteração ou a remoção do valor de ID na primeira folha, desde que exista um valor de ID correspondente na coluna validada na 2ª folha?

    

Answer 1

Como @ForwardEd apontou, isso requer algum VBA. O código a seguir será disparado quando algo for alterado em sua lista de IDs exclusivos e verificar se o valor antigo existia na outra lista (sua segunda folha). Se existir, a alteração será desfeita.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vOld As Variant
    Dim vNew As Variant

    If Not Intersect([ProductListItemID], Target) Is Nothing Then
        vNew = Target.Value
        Application.EnableEvents = False
        Application.Undo
        vOld = Target.Value
        If WorksheetFunction.CountIf([OrdersItemID], Target) > 0 Then
            MsgBox "Change disallowed"
        Else
            Target.Value = vNew
            'MsgBox "Change OK"
        End If
        Application.EnableEvents = True
    End If
End Sub

Isso deve estar no código da Planilha1. Estou assumindo dois intervalos nomeados:

ProductListItemID (List of items on Sheet1 to be protected)
OrdersItemID (List of ItemID's in 2nd sheet)

Eu incluí os dois intervalos em uma planilha por conveniência:

Explicação: Quando uma alteração é feita na Planilha1, a macro verifica se ela está no intervalo ProductListItemID. Se assim for, ele pega o valor alterado (vNew), em seguida, faz um desfazer e pega o valor anterior (vOld). Em seguida, ele verifica se o valor vOld existia no intervalo OrdersItemID. Se estiver, a célula será deixada no valor antigo, caso contrário, o novo valor será restaurado.

    

Answer 2

Aqui está uma alternativa não VBA usando a formatação condicional. Ele depende do usuário saber o que fazer, mas se você deixar claro que há um erro, você deve estar bem (dependendo de seus usuários).

1) Adicione a fórmula para detectar erros. Na Folha1, célula B2 digite: =IF(OR(MAX(COUNTIF(ProductListItemId,ProductListItemId))>1,MIN(COUNTIF(ProductListItemId,OrdersItemID))=0),"ERROR","ok") Esta é uma fórmula de matriz, então você deve pressionar Ctrl-Shift-Enter em vez de apenas Enter. A fórmula será cercada por {}.

2) Formatação condicional: Sevocêaplicaraformataçãocondicionalàpáginainteiraouaumintervalograndeosuficiente,eladeverásersuficienteparainterromperousuárioemsuastrilhas.Vocêpodeadicionarinstruçõespara"desfazer" as alterações e tentar novamente.

Devo salientar que a fórmula em B2 também verifica se há duplicatas na sua Lista de Produtos (itens na Folha 1).

Resultado:

    

Answer 3

Uma solução simples é usar o evento Worksheet_SelectionChange . Eu vou ser um pouco insolente aqui e usar a imagem que Rob Gale preparou. Eu reproduzi esta imagem nesta resposta caso algo aconteça com a resposta de Rob.

O que o código abaixo deve fazer é bloquear as células se uma ID de item correspondente for encontrada na Folha 2. E, da próxima vez que uma célula for selecionada, ela deverá desbloquear as células para edições futuras.

PrivateSubWorksheet_SelectionChange(ByValTargetAsRange)DimtRangeToProtectasRangeSettRangeToProtect=Intersect([ProductListItemID],Target)IfNottRangeToProtectIsNothingThenDimtCellasRangeDimtItemFoundasBooleantItemFound=FalseForEachtCellintRangeToProtect'"Target" can be multiple cells. Must always hand this.
           tItemFound = tItemFound OR WorksheetFunction.CountIf([OrdersItemID],tCell) > 0
        Next tCell
        if tItemFound Then 
            tRangetoProtect.Locked = True
            Me.Protect UserInterfaceOnly = True
            ' Use this in conjunction with worksheet.Protect UserInterfaceOnly := True
    Else
        [ProductList].Locked = False ' Open this up for future checks and editing - remove any existing locks
    End If
End Sub

Esta é apenas uma solução aproximada - pode ser refinada com base no contexto do seu negócio. Além disso, eu não testei isso, então sua milhagem pode variar.