Excel 2010: atualização dinâmica da lista suspensa com base nas alterações da planilha de validação da fonte de dados

Question

Eu tenho uma planilha para configurar as fontes de dados de várias listas de validação de dados. Em outras palavras, estou usando essa planilha para fornecer listas suspensas para várias outras planilhas.

Eu preciso atualizar dinamicamente todas as planilhas em uma ou várias alterações na planilha de fonte de dados. Eu posso entender que isso deve vir com a macro de eventos durante toda a pasta de trabalho.

Minha pergunta é como conseguir isso mantendo a fórmula "OFFSET" em toda a pasta de trabalho?

Thx

Para dar suporte à minha pergunta, coloquei o código que estou tentando fazer:

Fornecidas as seguintes informações:

• Estou usando essa fórmula para uma atualização pseudo-dinâmica do menu suspenso listas, por exemplo:

= OFFSET (MyDataSourceSheet! $ O $ 2; 0; 0; COUNTA (MyDataSourceSheet! O: O) -1)

• Eu examinei o capítulo do evento do livro pearson , mas estou muito noob por isso.
• Eu entendo esta macro e implementei com sucesso como um teste com a lista suspensa na mesma planilha como a fonte de dados. Meu ponto é que eu não sei como implantar este sobre uma pasta de trabalho completa.

Macro relacionada à planilha da fonte de dados:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' Macro to update all worksheets with drop down list referenced upon
' this data source worksheet, base on ref names

    Dim cell As Range
    Dim isect As Range
    Dim vOldValue As Variant, vNewValue As Variant

    Dim dvLists(1 To 6) As String 'data validation area
    Dim OneValidationListName As Variant

    dvLists(1) = "mylist1"
    dvLists(2) = "mylist2"
    dvLists(3) = "mylist3"
    dvLists(4) = "mylist4"
    dvLists(5) = "mylist5"
    dvLists(6) = "mylist6"

    On Error GoTo errorHandler

    For Each OneValidationListName In dvLists

        'Set isect = Application.Intersect(Target, ThisWorkbook.Names("STEP").RefersToRange)
        Set isect = Application.Intersect(Target, ThisWorkbook.Names(OneValidationListName).RefersToRange)

        ' If a change occured in the source data sheet
        If Not isect Is Nothing Then

            ' Prevent infinite loops
            Application.EnableEvents = False

            ' Get previous value of this cell
            With Target
                vNewValue = .Value
                Application.Undo
                vOldValue = .Value
                .Value = vNewValue
            End With

            ' LOCAL dropdown lists : For every cell with validation
            For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
                With cell
                    ' If it has list validation AND the validation formula matches AND the value is the old value
                    If .Validation.Type = 3 And .Validation.Formula1 = "=" & OneValidationListName And .Value = vOldValue Then

                        ' Debug
                        ' MsgBox "Address: " & Target.Address

                        ' Change the cell value
                         cell.Value = vNewValue



                    End If
                End With
            Next cell

            ' Call to other worksheets update macros
             Call Sheets(5).UpdateDropDownList(vOldValue, vNewValue)

            ' GoTo NowGetOut
            Application.EnableEvents = True

        End If
     Next OneValidationListName


NowGetOut:
    Application.EnableEvents = True
    Exit Sub

errorHandler:
    MsgBox "Err " & Err.Number & " : " & Err.Description
    Resume NowGetOut


End Sub

Macro UpdateDropDownList relacionado à planilha de destino:

Sub UpdateDropDownList(Optional vOldValue As Variant, Optional vNewValue As Variant)

        ' Debug
        MsgBox "Received info for update : " & vNewValue

        ' For every cell with validation
        For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
            With cell
                ' If it has list validation AND the validation formula matches AND the value is the old value
                ' If .Validation.Type = 3 And .Value = vOldValue Then
                If .Validation.Type = 3 And .Value = vOldValue Then
                    ' Change the cell value
                    cell.Value = vNewValue
                End If
            End With
        Next cell

End Sub

    

Answer 1

Eu consegui trabalhar agora, com base na seguinte configuração:

Uma planilha de fonte de dados com a configuração de evento de mudança da planilha conforme a macro abaixo. Essa macro chama a macro de planilha de destino UpdateDropDownList com 2 argumentos (valor antigo e novo) que são necessários para uma atualização dinâmica das listas suspensas.

Macro da planilha da fonte de dados (evento de alteração):

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' Macro to update all worksheets with drop down list referenced upon
' this data source worksheet, base on ref names

    Dim cell As Range
    Dim isect As Range
    Dim vOldValue As Variant, vNewValue As Variant

    Dim dvLists(1 To 6) As String 'data validation area
    Dim OneValidationListName As Variant

    dvLists(1) = "myListName1"
    dvLists(2) = "myListName2"
    dvLists(3) = "myListName3"
    dvLists(4) = "myListName4"
    dvLists(5) = "myListName5"
    dvLists(6) = "myListName6"

    On Error GoTo errorHandler

    For Each OneValidationListName In dvLists

        Set isect = Application.Intersect(Target, ThisWorkbook.Names(OneValidationListName).RefersToRange)

        ' If a change occured in the datasource worksheet
        If Not isect Is Nothing Then

            ' Prevent infinite loops
            Application.EnableEvents = False

            ' Get previous value of this cell
            With Target
                vNewValue = .Value
                Application.Undo
                vOldValue = .Value
                .Value = vNewValue
            End With

             ' Call to other worksheets update macros
             Call Sheets(5).UpdateDropDownList(vOldValue, vNewValue)

            ' GoTo NowGetOut
            Application.EnableEvents = True

        End If
    Next OneValidationListName


NowGetOut:
    Application.EnableEvents = True
    Exit Sub

errorHandler:
    MsgBox "Format Err " & Err.Number & " : " & Err.Description
    Resume NowGetOut


End Sub

Macro da planilha de destino:

Sub UpdateDropDownList(Optional vOldValue As Variant, Optional vNewValue As Variant)

On Error GoTo errorHandler

        ' Debug
        ' MsgBox "Received info for update : " & vNewValue

        ' For every cell with validation
        For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
            With cell
                ' If it has list validation AND the validation formula matches AND the value is the old value
                If .Validation.Type = 3 And .Value = vOldValue Then
                    ' Change the cell value
                    cell.Value = vNewValue
                End If
            End With
        Next cell

Exit Sub

errorHandler:
    MsgBox "Saisie Err : " & Err.Number & " : " & Err.Description
End Sub