none
Como Proteger uma coluna de uma Tabela RRS feed

  • Pergunta

  • Bom dia.

    Estou usando uma Tabela na planilha e numa das colunas tem formulas,e nas outras formatacao de celulas e validacoes de dados.Toda vez que acrescento mais registros via VBA ou manualmente, a Tabela copia automaticamente as formulas e as formatacoes e validacoes de dados da linha superior e na coluna onde tem formulas gostaria de deixar protegido para evitar de o usuario apagar ou deletar por engano.

    Quando protegi a planilha, a Tabela deixou de funcionar como Tabela, tem como solucionar esses casos?

    Desde ja agradeco a quem possa responder.

    Tadao

    sexta-feira, 8 de abril de 2016 11:03

Respostas

  • Boa tarde Tadao.

    Realmente, a minha primeira resposta não está correta: Tabelas em planilhas protegidas não podem ser redimensionadas.

    Quanto à segunda resposta, esta sim funciona. A respeito de mudar o nome da Tabela, confesso que nunca vi isso acontecer. Qual comando está utilizando? Eu uso algo como o que está abaixo e para mim sempre funciona (lembre-se de desproteger e proteger):

    '...
    Set TabCadastro = ActiveSheet.ListObjects(1)        'Escolha da Tabela
    
    
    Li = Range(TabCadastro).Row                    'Linha Inicial
    Ci = Range(TabCadastro).Column                 'Coluna Inicial
    ncI = Range(TabCadastro).Columns.Count         'Número de Colunas
    nLi = Range(TabCadastro).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, 1)
    
    If nL = vbNullString Then GoTo Fim
    
    nL = Val(nL)
    
    If nL <= 0 Or Not IsNumeric(nL) Then
        MsgBox "Número de Linhas Inválido! Fim da Execução!", vbCritical
        GoTo Fim
    End If
    
    With TabCadastro
        If .ShowTotals Then nL = nL + 1          'Verifica se existe a Linha de Total (necessita adaptação)
        
        .Resize Range(Cells(Li - 1, Ci), Cells(Lf + nL, Ci + ncI - 1))
    End With
    
    '...

    Consegui ajudar?

    Abraço.


    Filipe Magno

    segunda-feira, 11 de abril de 2016 16:20
  • Boa noite Tadao.

    Não precisa se desculpar. Estamos todos aprendendo e nem sempre temos como responder rapidamente.

    Quanto aos seus problemas, na verdade só identifiquei um na sua planilha: os eventos associados à coluna "E" (e não "F" como você citou) não são disparados porque existe uma caixa suspensa nesta coluna. Neste caso, para permitir o evento clique é necessário marcar uma opção no momento de proteger a planilha, como a figura abaixo:

    Dessa forma a lista passa a ser acessada apenas quando se clica na seta de exibição e não mais em qualquer parte da célula.

    Quanto à coluna "C" não consegui reproduzir o erro, para mim funcionou da mesma maneira. É essa coluna mesmo?

    Abraço.


    Filipe Magno

    quinta-feira, 14 de abril de 2016 02:44
  • Boa noite Antônio!

    A explicação é bem simples, mas eu nunca tinha passado, pois não uso a opção que causa esse erro: quando vc efetua o duplo clique numa célula que está bloqueada e a célula selecionada não está bloqueada, ocorre a edição dessa célula selecionada, similar a apertar F2 (é o que ocorre na Planilha 1). Porém, quando a opção de ocultar a fórmula está marcada, conforme figura abaixo, a edição da célula não é possível (por estar bloqueada) e por isso o conteúdo é apagado (é o que ocorre na Planilha 2). Note que é o mesmo comportamento que apertar F2 ou clicar na Barra de Fórmulas.

    Obs.: Vc compartilhou sua pasta no OneDrive e não apenas a planilha.

    Compreendeu?

    Abraço.


    Filipe Magno

    quinta-feira, 28 de abril de 2016 01:06

Todas as Respostas

  • Antônio,

    não entendi a parte que "Quando protegi a planilha, a Tabela deixou de funcionar como Tabela,". Poderia explicar melhor?

    Aproveitando, lembro que quando uma planilha está protegida, os conteúdos bloqueados não podem ser alterados, ou seja, caso a parte de baixo de uma tabela esteja com células protegidas a tabela não pode ser expandida sobre elas sem antes desproteger a planilha.

    Abraço.


    Filipe Magno

    sábado, 9 de abril de 2016 00:40
  • Na Fig 1 mostra uma tabela de Nome Tabela1 que tem o cadastro dos funcionarios masculinos, com colunas de Nomes,Codigo, endereco,Situacao,Sexo,Transferencia e Ordenacao.

    sc

    Fig1


    • Editado Antonio Tadao kano segunda-feira, 11 de abril de 2016 11:32 Dados desnecessarios
    domingo, 10 de abril de 2016 07:54
  • Boa tarde Antônio.

    Acho que já respondi seu questionamento, se entendi bem o que precisa. Se deseja que uma tabela seja expandida, tanto automaticamente ao escrever na primeira linha abaixo dela, quanto manualmente, sua planilha não pode estar protegida.

    Se este for o caso a solução é extremamente simples: adicione um botão para redimensionar a tabela para o tamanho desejado (número de linhas a acrescentar), incluindo um comando para desproteger a planilha antes e outro para voltar a proteger após o redimensionamento. Essa é a forma que sempre utilizo.

    Era isso?


    Filipe Magno

    domingo, 10 de abril de 2016 20:18
  • Oh Felipe, desculpe, ........talvez eu nao tenha entendido o seu raciocinio por falta de mais conhecimento do Excel da minha parte, por isso fiz uma longa historia que por sua vez ja foi editada.Pode ser que ainda nao assimilei bem o conceito de protecao.<o:p></o:p>

    O que eu fiz foi selecionar a planilha inteira e em formatar celulas-->protecao-->retireio tic do bloqueado. Em seguida, selecionei o intervalo G4:G18 (que corresponde a coluna onde tem as formulas na Tabela da Fig 1) e em formatar celulas -->protecao-->coloquei o tic no bloqueado. Apos isso ativei a protecao das celulas, ficando somente o intervalo G4:G18 protegido. Dai a marquinha de indicacao da tabela que fica no canto inferior a direita sumiu.<o:p></o:p>

    Pela sua primeira resposta, onde voce diz que se parte de baixo de uma tabela esteja com celulas protegidas a tabela nao pode ser expandida,.....mas onde as celulas estao protegidas sao celulas G4:G17, todas as celulas da linha 18 que e a parte de baixo da tabela nao estao protegidas.<o:p></o:p>

    Aproveitando, lembro que quando uma planilha está protegida, os conteúdos bloqueados não podem ser alterados, ou seja, caso a parte de baixo de uma tabela esteja com células protegidas a tabela não pode ser expandida sobre elas sem antes desproteger a planilha.<o:p></o:p>

     

    Quanto a sua segunda resposta de :<o:p></o:p>

    Se este for o caso a solução é extremamente simples: adicione um botão para redimensionar a tabela para o tamanho desejado (número de linhas a acrescentar), incluindo um comando para desproteger a planilha antes e outro para voltar a proteger após o redimensionamento. Essa é a forma que sempre utilizo.

    <o:p></o:p>

    ,acho que entendi o raciocinio de redimencionar a tabela e aumentar linhas vazias no fim da tabela, fiz isso manualmente mas toda vez que redimenciona a tabela o nome da tabela muda e os eventos nao rodam mais.Toda vez que redimensiona a tabela teria que mudar o nome da tabela na rotinas de eventos?.Mudando o nome da tabela da rotina nos codigos dos eventos, os eventos  e as validacoes de dados funcionam, mas as formatacao de celulas e as formulas das celulas nao sao copiadas nas novas linhas.<o:p></o:p>

    Tadao<o:p></o:p>

    segunda-feira, 11 de abril de 2016 15:59
  • Boa tarde Tadao.

    Realmente, a minha primeira resposta não está correta: Tabelas em planilhas protegidas não podem ser redimensionadas.

    Quanto à segunda resposta, esta sim funciona. A respeito de mudar o nome da Tabela, confesso que nunca vi isso acontecer. Qual comando está utilizando? Eu uso algo como o que está abaixo e para mim sempre funciona (lembre-se de desproteger e proteger):

    '...
    Set TabCadastro = ActiveSheet.ListObjects(1)        'Escolha da Tabela
    
    
    Li = Range(TabCadastro).Row                    'Linha Inicial
    Ci = Range(TabCadastro).Column                 'Coluna Inicial
    ncI = Range(TabCadastro).Columns.Count         'Número de Colunas
    nLi = Range(TabCadastro).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, 1)
    
    If nL = vbNullString Then GoTo Fim
    
    nL = Val(nL)
    
    If nL <= 0 Or Not IsNumeric(nL) Then
        MsgBox "Número de Linhas Inválido! Fim da Execução!", vbCritical
        GoTo Fim
    End If
    
    With TabCadastro
        If .ShowTotals Then nL = nL + 1          'Verifica se existe a Linha de Total (necessita adaptação)
        
        .Resize Range(Cells(Li - 1, Ci), Cells(Lf + nL, Ci + ncI - 1))
    End With
    
    '...

    Consegui ajudar?

    Abraço.


    Filipe Magno

    segunda-feira, 11 de abril de 2016 16:20
  • Ola Felipe, obrigado pelo pronto retorno.

    Em primeiro lugar, gostaria de saber se o processo de protecao que eu fiz esta correto ou nao, pois depois que ativo a protecao das celulas a marquinha da tabela no canto direito inferior some.

    O que eu fiz para dizer que a tabela muda de nome e que eu estava redimensionando manualmente a tabela para testar o que voce respondeu pois ainda nao sabia como redimensionar via VBA, e toda vez que quebrava a tabela e refazia com nova dimensao, o excel colocava automaticamente um nome gerado por ele.

    Num dos eventos uso o codigo .....

    If Not Intersect(Range("Tabela1[Situacao]"), Target) Is Nothing Then

    ......e onde tem o nome Tabela 1 , com o redimensionamento (feito manualmente) ficava com outro nome e o evento nao funcionava mais, renomeando a tabela como Tabela1, dai funciona. No lugar do nome Tabela1 (no codigo acima) tentei colocar Activesheet.ListObject(1) que deve significar a primeira Tabela encontrada na planilha ativa, assim independente de qualquer nome, ja que tenho so uma Tabela na planilha, esperava funcionar mas sem sucesso.

    Depurei o seu codigo, acrescentando no inicio Activesheet.UnProtect e na linha  antes do End Sub, duas linhas como  fim:  e  Activesheet.Protect .

    Partindo do principio de que a planilha esteja com a protecao ativada e apertando o F8, inicialmente desativa a protecao(voltando a ter a marquinha que a tabela esta ativa) e funciona bem ate chegar no End With.Ate chegar ai, vi que copiou a formula da coluna G das linhas anteriores nas novas linhas e na coluna E onde tem validacao de dados lista, tambem esta Ok. Continuei a apertar o F8 e depois da linha de ativacao da protecao, a marquinha de ativacao da tabela sumiu, e End Sub.

    Fui colocar dados na nova linha e vi que os eventos das colunas C e E nao funcionam, porque sera?

    Tadao

    terça-feira, 12 de abril de 2016 02:50
  • Boa noite Antonio.

    Sobre a marquinha, este é o comportamento esperado mesmo, indicando que a Tabela não pode ser redimensionada com a planilha protegida, fique tranquilo.

    Agora sobre a mudança no nome da Tabela confesso que ainda não compreendo. Fiz inúmeros testes (Excel 2010) e não consegui reproduzir o problema, inclusive utilizando o evento que citou. Conseguiria disponibilizar uma planilha de teste onde o problema acontece (poste no OneDrive, se possível)? Se possível poste uma pasta de trabalho em branco, contendo apenas a tabela e o evento que é disparado.

    Fico aguardando.


    Filipe Magno

    quarta-feira, 13 de abril de 2016 01:20
  • Ola Felipe, desculpe a demora pois eu nao tenho muito traquejo nesse OnDrive e nao sei se fiz o link certo,ta.

    https://onedrive.live.com/redir?resid=7EE47A230E07387C!725&authkey=!ALQB_Oyx3s1u1Gs&ithint=file%2cxlsm

    Pelo que voce explanou, entao quer dizer que com a planilha protegida nao pode ser redimensionada,Ok, entao desprotege redimensiona e volta a proteger para que o ususario possa inserir dados,certo.......mas quando deixa a planilha protegida para o usuario inserir dados nas linhas redimensionadas, os eventos das colunas C e F que antes de proteger funcionavam, nao funcionam mais. Tente preencher o Cadastro Homens sem protecao e com protecao que voce vai notar que na coluna de codigo aciona o evento Private Sub Worksheet_Change(ByVal Target As Range) para fazer a validacao de dados do codigo, e depois de redimensionado com nova linha, nesa nova linha os eventos nao funcionam mais.

    Quanto a Tabela mudar de nome, acho que deve ter um mal entendido, o que eu disse ou queria dizer e que tinha uma tabela com um nome e redimensionei manualmente clicando no icome redimensionar e selecionei manualmente a nova area redimensionada( com linhas adicionais) e ativei a protecao e fui testar dai notei que o nome da planilha tinha mudado de nome pelo excel, ok. Me desculpe pelo transtorno.

    ,acho que entendi o raciocinio de redimencionar a tabela e aumentar linhas vazias no fim da tabela, fiz isso manualmente mas toda vez que redimenciona a tabela o nome da tabela muda e os eventos nao rodam mais.Toda vez que redimensiona a tabela teria que mudar o nome da tabela na rotinas de eventos?.

    O que eu fiz para dizer que a tabela muda de nome e que eu estava redimensionando manualmente a tabela para testar o que voce respondeu pois ainda nao sabia como redimensionar via VBA, e toda vez que quebrava a tabela e refazia com nova dimensao, o excel colocava automaticamente um nome gerado por ele.

    Tadao

    quarta-feira, 13 de abril de 2016 15:39
  • Oi Felipe, desculpe que no codigo do evento Activate da planilha Calendario tem um erro como mostra a figura 1.

    Tadao

    Fig 1

    quarta-feira, 13 de abril de 2016 18:03
  • Boa noite Tadao.

    Não precisa se desculpar. Estamos todos aprendendo e nem sempre temos como responder rapidamente.

    Quanto aos seus problemas, na verdade só identifiquei um na sua planilha: os eventos associados à coluna "E" (e não "F" como você citou) não são disparados porque existe uma caixa suspensa nesta coluna. Neste caso, para permitir o evento clique é necessário marcar uma opção no momento de proteger a planilha, como a figura abaixo:

    Dessa forma a lista passa a ser acessada apenas quando se clica na seta de exibição e não mais em qualquer parte da célula.

    Quanto à coluna "C" não consegui reproduzir o erro, para mim funcionou da mesma maneira. É essa coluna mesmo?

    Abraço.


    Filipe Magno

    quinta-feira, 14 de abril de 2016 02:44
  • Ohhh ...Felipe, agora esta funcionando Beleza. O nome da coluna realmente e o E como voce diz, e quanto a Validacao da coluna C, agora esta funcionando,(nao sei porque antes nao funcionava).

    Esse problema de deixar clicado o "Editar Objetos", lembro que antes deu um problema tambem e foi resolvido e esclarecido com suporte desse  Forum. Acho que sempre que vou proteger vou deixar clicado essa opcao pra no futuro nao dar o mesmo problema,nao?

    Mais uma vez o meu muito Obrigado..............com o seu reparo, ...posso continuar com a minha caminhada.........

    Tadao

    quinta-feira, 14 de abril de 2016 04:31
  • Ola Felipe, antes de mais nada, agradeco pelos seus suportes.

    Estava pensando em fazer nova postagem, mas como o assunto e sobre protecao resolvi escrever aqui.

    O que esta acontecendo e que numa planilha que estou fazendo, tem uma  area delimitada que ao dar dois clicks dispara um evento que muda os dados da celula ativa, e esta funcionado beleza conforme a sua orientacao de proteger com Editar Objetos ativado.

    Mas num belo dia fui dar dois clicks fora da area delimitada, os dados da celula clicada sumiram.Para tentar verificar o problema, deletei toda rotina do eventos, mas continuou a permanecer o problema.Resolvi fazer uma copia dessa planilha em outra pasta com nome Sheet2 e limpar todo macro ou rotinas de evento para ver se resovia, mas nao resolveu......da dois clicks, apada dados........porque sera? Nessa nova pasta onde copiei a planilha, criei uma nova planilha(Sheet1)com os mesmos ambientes,mas nao da problemas. Anexo link do arquivo onde tem a planilha sheet1 sem problema e sheet2 com problema. Acho que o problema nao esta em proteger ou nao proteger, deve ser outra coisa que nao sei descobrir. Se puder me ajudar agradeco

    Tadao

    https://onedrive.live.com/redir?resid=7EE47A230E07387C!576&authkey=!AALmg5PX3xfhTC4&ithint=folder%2cxlsx

    quarta-feira, 27 de abril de 2016 01:56
  • Boa noite Antônio!

    A explicação é bem simples, mas eu nunca tinha passado, pois não uso a opção que causa esse erro: quando vc efetua o duplo clique numa célula que está bloqueada e a célula selecionada não está bloqueada, ocorre a edição dessa célula selecionada, similar a apertar F2 (é o que ocorre na Planilha 1). Porém, quando a opção de ocultar a fórmula está marcada, conforme figura abaixo, a edição da célula não é possível (por estar bloqueada) e por isso o conteúdo é apagado (é o que ocorre na Planilha 2). Note que é o mesmo comportamento que apertar F2 ou clicar na Barra de Fórmulas.

    Obs.: Vc compartilhou sua pasta no OneDrive e não apenas a planilha.

    Compreendeu?

    Abraço.


    Filipe Magno

    quinta-feira, 28 de abril de 2016 01:06
  • Ohhh...Felipe,.... uma coisa simples para voce, mas eu ja estava ficando com os cabelos brancos......muito obrigado. Entao nesses casos para nao ocorrer o imprevisto, eh so desmarcar o Ocultas no Formatar Celulas,certo?

    Agradeco pela Obs. no final sobre compartilhamento, eh que nao uso ou entendo muito de OneDrive e estava cismado de que tinha feito algo errado. Se possivel poderia explicar melhor para nao fazer mais coisa errada?. Por exemplo quando voce falou em planilha quer dizer WorkSheet?, e a pasta que voce referiu e workbook,Arquivo do Excel?(Desculpe, estou muito tempo fora do Brasil). A intencao foi enviar o link do arquivo do Excel, mas nao foi isso que mandei?, ou sera que foi o Diretorio inteiro onde estao todos arquivos?.Eu tive dificuldade para conseguir o link, quando apareceu coisa parecida mandei,pois o meu excel esta tudo em japones e ultimamente, depois que mandei o link comecou a aparecer mensagem como abaixo quando ligo o computador. A mensagem do Microsoft OneDrive diz que Nao da para compartilhar alem disso o arquivo particular.Devido a uma Mudanca feita pelo setor de IT, seu computador deixou de fazer arquivos compartilhados. Fucaria muito grato se puder desvendar mais essa duvida.Meu Office nao e professional.

    Tadao

    quinta-feira, 28 de abril de 2016 08:05
  • Boa noite Antonio.

    Desculpe a demora para responder. Vamos lá nas suas perguntas:

    "Entao nesses casos para nao ocorrer o imprevisto, eh so desmarcar o Ocultas no Formatar Celulas,certo?"

    Para evitar o comportamento citado sim, mas caso precise ocultar as fórmulas não vejo outra solução.

    "Por exemplo quando voce falou em planilha quer dizer WorkSheet?, e a pasta que voce referiu e workbook,Arquivo do Excel?"

    Desculpe, acabei misturando os conceitos: quando disse pasta estava me referindo ao diretório e planilha ao arquivo do Excel. Realmente fica estranho porque os conceitos para arquivos do Excel são como você mencionou. O que eu quiz dizer é que bastava compartilhar apenas um arquivo do Excel e não o diretório completo.

    Quanto à mensagem do OneDrive essa já não consigo te ajudar. Eu praticamente não o utilizo e nunca vi nada parecido.

    Se tiver mais alguma dúvida não exite em perguntar.

    Abraço.


    Filipe Magno



    • Editado FilipeMagno sábado, 30 de abril de 2016 18:28
    sábado, 30 de abril de 2016 02:15
  • Ola Felipe, obrigado pelo retorno. Nao tinha percebido que tinha compartilhado o diretorio, vou tomar mais cuidado da proxima vez e agradeco pela observacao.

    Tadao

    sábado, 30 de abril de 2016 05:14