none
Inclusão de linhas em tabela dinâmica protegida RRS feed

  • Pergunta

  • Criei uma planilha de controle no Excel utilizando Tabela Dinâmica com regra de formatação (cores). 

    Esta planilha será utilizada por outras pessoas e por isso preciso proteger algumas células. Entretanto, quanto protejo a planilha, não existe mais a possibilidade de acrescentar linhas automaticamente, que é uma das funções da tabela dinâmica, ou seja, as fórmulas da última linha não são replicadas para as novas linhas que serão preenchidas.

    Alguém sabe como solucionar este problema? 

    quinta-feira, 29 de agosto de 2013 16:45

Respostas

  • Boa noite Rachkell.

    A solução do seu problema pode ser simples, mas depende da sua necessidade de segurança. Se a proteção da planilha não exigir senha, a solução que proponho abaixo resolve perfeitamente. Mas caso haja, vc deverá também colocar senha no projeto VBA e acrescentá-la nos códigos abaixo.

    Primeiramente sua planilha terá que ser habilitada para macros (extensão xlsm, xltm, ...). Em seguida será necessário solicitar aos usuários que quando houver necessidade de aumentar a tabela deverá antes clicar em botão e informar (ou deixar isso pré-determinado) quantas linhas são necessárias.

    Partindo que não há senha, sugiro o código abaixo. Cole em um módulo comum:

    Sub AumentarTabLista()
    '
    ' Redimensiona (aumenta) a Tabela com o Tamanho desejado
    '
    
    Set pCadastro = ActiveSheet
    
    Application.ScreenUpdating = False
    Call Desproteger
    
    'On Error Resume Next
    Li = Range(TabLista).Row                    'Linha Inicial
    Ci = Range(TabLista).Column                 'Coluna Inicial
    nCi = Range(TabLista).Columns.Count         'Número de Colunas
    nLi = Range(TabLista).Rows.Count            'Número de Linhas na Tabela
    
    Lf = Li + nLi - 1                           'Nº da última linha da tabela
    
    'Número de Linhas a inserir
    Título = "Redimensionamento da Tabela"
    Mensagem = "Digite o Número de Linhas que deseja inserir:"
    nL = InputBox(Mensagem, Título)
    
    If nL = "" Then
        Call Proteger           'Atual
        Application.ScreenUpdating = True
        Exit Sub
    End If
    
    nL = Val(nL)
    
    If nL <= 0 Or Not IsNumeric(nL) Then
        MsgBox "Número de Linhas Inválido! Fim da Execução!", vbCritical
        Call Proteger           'Atual
        Application.ScreenUpdating = True
        Exit Sub
    End If
    
    'Desabilitando e rabilitando a linha de totais, caso já esteja ativa (Para evitar possíveis incompatibilidades com as formatações condicionais)
    '>
    With pCadastro.ListObjects(TabLista)
        LinTotal = .ShowTotals   'Verifica se existe a Linha de Total
        
        If LinTotal Then .ShowTotals = False    'Desabilitando
        
        'Redimensionando a Tabela Filtrada
        .Resize Range(Cells(Li - 1, Ci), Cells(Lf + nL, Ci + nCi - 1))
        
        If LinTotal Then .ShowTotals = True     'Reabilitando
    End With
    '<
    Call Proteger           'Atual
    Application.ScreenUpdating = True
    End Sub

    Sub Proteger(Optional pPlan)
    '
    ' Função para padronizar a forma como as planilhas são protegidas.
    '
    '   pPlan =>  Opcional. Planilha de interesse
    
    If IsMissing(pPlan) Then Set pPlan = ActiveSheet
    
    'Application.ScreenUpdating = False
    
    'Escolha aqui as características desejadas
    pPlan.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFiltering:=True, AllowSorting:=True
    
    'Application.ScreenUpdating = True
    
    End Sub
    
    Sub Desproteger(Optional pPlan)
    '
    ' Poderia ser utilizada para utilizar senhas (não habilitado).
    '
    '   pPlan =>  Opcional. Planilha de interesse
    
    If IsMissing(pPlan) Then Set pPlan = ActiveSheet
    
    'Application.ScreenUpdating = False
    pPlan.Unprotect
    'Application.ScreenUpdating = True
    
    End Sub

    Coloque um botão, ou imagem, na planilha e atribua a Macro "AumentarTabLista".

    Conseguiu entender?

    Um abraço.


    Filipe Magno


    sábado, 31 de agosto de 2013 01:41

Todas as Respostas

  • Você está falando de um relatório de tabela dinâmica ou de uma lista (objeto ListObject, criado a partir do menu Página Inicial)?

    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    quinta-feira, 29 de agosto de 2013 23:23
    Moderador
  • Olá Felipe, 

    Acho que não chega a ser um relatório. Tentei colocar a imagem da tabela pra você entender mas acredito que por ser nova no fórum apenas posso inserir texto.

    Bem... quando eu falo em tabela dinâmica me refiro a ação "Inserir" / "Tabela Dinâmica". Minha tabela contém algumas células preenchidas na cor verde pois contém regra de formatação e células contendo uma pequena fórmula. 

    Se eu digitar qualquer valor na linha logo abaixo da tabela, será criada uma nova linha copiando a formatação e fórmulas das células da tabela dinâmica, que ficam acima. Mas quando eu protejo a planilha, o "indicador" do canto inferior direito, que permite ampliar as linhas da tabela dinâmica, some e não é acrescentada uma nova linha à tabela caso eu digite qualquer valor abaixo.

    Perdão se não me expressei bem, mas não possuo conhecimento avançado em Excel.

    Obrigada!

    sexta-feira, 30 de agosto de 2013 12:03
  • Boa noite Rachkell.

    A solução do seu problema pode ser simples, mas depende da sua necessidade de segurança. Se a proteção da planilha não exigir senha, a solução que proponho abaixo resolve perfeitamente. Mas caso haja, vc deverá também colocar senha no projeto VBA e acrescentá-la nos códigos abaixo.

    Primeiramente sua planilha terá que ser habilitada para macros (extensão xlsm, xltm, ...). Em seguida será necessário solicitar aos usuários que quando houver necessidade de aumentar a tabela deverá antes clicar em botão e informar (ou deixar isso pré-determinado) quantas linhas são necessárias.

    Partindo que não há senha, sugiro o código abaixo. Cole em um módulo comum:

    Sub AumentarTabLista()
    '
    ' Redimensiona (aumenta) a Tabela com o Tamanho desejado
    '
    
    Set pCadastro = ActiveSheet
    
    Application.ScreenUpdating = False
    Call Desproteger
    
    'On Error Resume Next
    Li = Range(TabLista).Row                    'Linha Inicial
    Ci = Range(TabLista).Column                 'Coluna Inicial
    nCi = Range(TabLista).Columns.Count         'Número de Colunas
    nLi = Range(TabLista).Rows.Count            'Número de Linhas na Tabela
    
    Lf = Li + nLi - 1                           'Nº da última linha da tabela
    
    'Número de Linhas a inserir
    Título = "Redimensionamento da Tabela"
    Mensagem = "Digite o Número de Linhas que deseja inserir:"
    nL = InputBox(Mensagem, Título)
    
    If nL = "" Then
        Call Proteger           'Atual
        Application.ScreenUpdating = True
        Exit Sub
    End If
    
    nL = Val(nL)
    
    If nL <= 0 Or Not IsNumeric(nL) Then
        MsgBox "Número de Linhas Inválido! Fim da Execução!", vbCritical
        Call Proteger           'Atual
        Application.ScreenUpdating = True
        Exit Sub
    End If
    
    'Desabilitando e rabilitando a linha de totais, caso já esteja ativa (Para evitar possíveis incompatibilidades com as formatações condicionais)
    '>
    With pCadastro.ListObjects(TabLista)
        LinTotal = .ShowTotals   'Verifica se existe a Linha de Total
        
        If LinTotal Then .ShowTotals = False    'Desabilitando
        
        'Redimensionando a Tabela Filtrada
        .Resize Range(Cells(Li - 1, Ci), Cells(Lf + nL, Ci + nCi - 1))
        
        If LinTotal Then .ShowTotals = True     'Reabilitando
    End With
    '<
    Call Proteger           'Atual
    Application.ScreenUpdating = True
    End Sub

    Sub Proteger(Optional pPlan)
    '
    ' Função para padronizar a forma como as planilhas são protegidas.
    '
    '   pPlan =>  Opcional. Planilha de interesse
    
    If IsMissing(pPlan) Then Set pPlan = ActiveSheet
    
    'Application.ScreenUpdating = False
    
    'Escolha aqui as características desejadas
    pPlan.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFiltering:=True, AllowSorting:=True
    
    'Application.ScreenUpdating = True
    
    End Sub
    
    Sub Desproteger(Optional pPlan)
    '
    ' Poderia ser utilizada para utilizar senhas (não habilitado).
    '
    '   pPlan =>  Opcional. Planilha de interesse
    
    If IsMissing(pPlan) Then Set pPlan = ActiveSheet
    
    'Application.ScreenUpdating = False
    pPlan.Unprotect
    'Application.ScreenUpdating = True
    
    End Sub

    Coloque um botão, ou imagem, na planilha e atribua a Macro "AumentarTabLista".

    Conseguiu entender?

    Um abraço.


    Filipe Magno


    sábado, 31 de agosto de 2013 01:41
  • Olá Filipe!

    Acho que você explicou bem mas me falta conhecimento o mínimo de conhecimento para o uso de Macros. Então vou detalhar o que fiz.

    1) Abri o Visual Basic e o código da Plan3;

    2) No final dos códigos, colei as três funções que me mandou (AumentarTabLista, Proteger e Desproteger)

    3) Atribui a macro "AumentarTabLista" a um botão de Controle de Formulários;

    4) Quando cliquei no botão é retornado o erro 400;

    5) Imagino que isso aconteceu porque não alterei o nome da planilha que está como pPlan no código;

    6) Troquei por Plan3 mas o erro continuou;

    Obs: percebi que quando clico no botão, apesar do erro aparecer, a planilha é desprotegida.

    sexta-feira, 6 de setembro de 2013 16:36
  • Bom dia Rachkell.

    Olhando o código, percebi que faltou uma linha: é preciso associar à variável "TabLista" o nome de sua tabela. Exemplo:

    Sub AumentarTabLista()
    '
    ' Redimensiona (aumenta) a Tabela com o Tamanho desejado
    '
    
    Set pCadastro = ActiveSheet
    TabLista = "Tabela1"        '<<<<<Insira aqui o nome de sua Tabela!!!
    
    Application.ScreenUpdating = False
    Call Desproteger
    
    '(...)
    

    Cole o código dentro de um Módulo comum e não dentro do código da planilha. Para inserir um módulo faça como a imagem abaixo:

    Será então criado o "Módulo1":

    Cole seu código lá.

    Outra opção é pedir para gravar uma Macro. Nesse caso o módulo é criado automaticamente.

    Não é necessário alterar a variável "pPlan". Caso não seja fornecida é utilizada a Aba que estiver ativa.

    Qualquer dúvida é só perguntar.

    Um abraço.


    Filipe Magno


    • Editado FilipeMagno sábado, 7 de setembro de 2013 15:44 Reinserção das figuras
    sábado, 7 de setembro de 2013 15:40