Excel desejo aplicar macro excel/vba em tabela para automatizar operação

Nilton Cézar · 19 de abril de 2021

Boa noite, Midori.

Infelizmente, vejo que você não pode mais me ajudar.

Mas, gostaria de te agradecer. Na verdade, não tenho palavras para te agradecer.

Se não fosse por você, não teria feito quase nada do projeto.

Pois bem, vou encerrar este tópico. Mas, continuarei aqui, para tentar concluir nosso projeito.

Muito obrigado! Você é !

Nilton Cézar · 29 de abril de 2021

Olá, pessoal!

Estou tentando concluir um RELATÓRIO DE AUDIÊNCIA baseado em textos pré-definidos e que contém uma tabela (não intervalo de dados).

Infelizmente, sou muito leigo em VBA. Por isso, peço ajuda dos colegas.

Pois bem, nesse projeto, pretendo OCULTAR linhas e, posteriormente, reexibi-las, para adicionar informações específicas, após escolher o tipo benefício/ação correspondente, listado numa validação de dados, inserida em uma determinada célula.

Para isso, já tenho uma macro, já com os devidos ajustes (mas, nada impede que outra seja sugerida, para fazer a mesma operação acima). Ela foi elaborada da seguinte forma:

No projeto, foi criada uma nova Aba com o nome "Benefício". Na coluna A dessa planilha, contém o tipo de benefício; e na coluna B, o intervalo de linhas, no formato: Inicio;Fim. Exemplo:

Aposentadoria por invalidez urbana - 21;25

Auxílio-reclusão urbano - 63;65.

Dessa forma, a coluna B da planilha Benefício vai informar o intervalo das linhas e, em seguida, é só pesquisar o intervalo com base no benefício selecionado.

Detalhe: para os benefícios cuja seleção não deve aparecer nenhuma linha, foi colocada a letra “N” (de nenhuma linha), na coluna B da planilha Benefício.

Assim, para isso foi criada a função abaixo:

Function BuscaIntervalo(Beneficio As String) As Variant

Dim Intervalo As Variant

On Error Resume Next

Intervalo = Replace(WorksheetFunction.VLookup( _

Beneficio, Sheets("Benefício").[A:B], 2, 0), ";", ":")

If Err.Number <> 0 Then

Intervalo = False

End If

BuscaIntervalo = Intervalo

End Function

Logo após, é só chamar a função no evento Change da planilha, p.ex.:

Private Sub Worksheet_Change(ByVal Target As Range)

Range("20:65").EntireRow.Hidden = True

If Target.Address = "$D$9" Then

Dim Intervalo As Variant

Intervalo = BuscaIntervalo(Target.Value)

If UCase(Intervalo) <> "N" Then

If Intervalo <> False Then

Range(Intervalo).EntireRow.Hidden = False

Else

Range("63:65").EntireRow.Hidden = False

End If

End Sub

Portanto, com base nos códigos acima, é esperado o seguinte:

As linhas 63 a 65 serão exibidas para qualquer texto que não tenha cadastro na planilha Benefício.
A macro não vai exibir nenhuma linha, quando o valor do benefício, na coluna B, for igual a letra N.
Nos demais casos, ela vai exibir determinado intervalo de linhas, segundo o benefício escolhido.

Se necessário, é possível ver todas as informações sobre essa macro, postadas a partir de 7 de abril, nesse mesmo tópico.

Então, vejamos. As instruções acima deram certo EM PARTE. Vou explicar.

Por exemplo, quando selecione o benefício de Pensão por Morte Rural, aparecem as linhas 27 a 49, como previsto pelo evento Change.

Contudo, quando vou digitar as informações específicas para esse benefício, nas linhas reexibidas, as linhas recolhem (ou seja, voltam a ficar ocultas), assumindo a condição esperada para os benefícios do Grupo 1, aqueles que tem a letra N e que não aparecem linhas.

Porém, não era para fazer isso, ou seja, era para ficar com as linhas 27 a 49 exibidas (e disponíveis para serem inseridas informações).

Curiosamente, isso também ocorre, quando deleto dados em célula, em qualquer parte da planilha. Até mesmo deletando células vazias.

Para melhor compreensão dos fatos, estou enviando uma cópia do meu projeto em anexo.

Desculpe a quantidade de informações. Mas, acho que elas são úteis para explicar o funcionamento da macro e descrever o problema apresentado.

Assim, solicito a ajuda dos colegas, para fazer o ajuste nessa macro ou, caso necessário, podem até sugerir outras macros, para OCULTAR/REEXIBIR linhas no projeto.

Ficaria muito grato.

Relatório de Audiências-Em elaboração (Salvo automaticamente) - TESTE-OCULTAR LINHAS.rar

Midori · 29 de abril de 2021

1 hora atrás, Nilton Cézar disse:

Contudo, quando vou digitar as informações específicas para esse benefício, nas linhas reexibidas, as linhas recolhem (ou seja, voltam a ficar ocultas)

Isso acontece porque o evento Change oculta as linhas independente da célula que está sendo editada. Para ocultar/exibir apenas quando a célula D9 for editada coloque esse comando no If,

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$9" Then
        Dim Intervalo As Variant
        
        Range("20:65").EntireRow.Hidden = True
.
.
.

Nilton Cézar · 29 de abril de 2021

Deu certo! E pensar que eu vinha ocultando manualmente antes!

Parece que as linhas de código do evento Change foram apenas invertidas. Ficando agora o IF e o Dim antes de Range("20:65").EntireRow.Hidden = True.

1 hora atrás, Midori disse:


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$9" Then
        Dim Intervalo As Variant
        
        Range("20:65").EntireRow.Hidden = True
.
.
.

Agora está funcionado perfeitamente.

_______________________________________________________________________

Pessoal, estou com um PROBLEMINHA quanto à visibilidade do texto dentro da linha (ou célula). Vou explicar.

Quando o TEXTO escolhido/digitado é GRANDE, todo seu conteúdo NÃO fica visível na respectiva célula da tabela. É possível perceber isso no arquivo enviado anteriormente.

Já apliquei o recurso de Quebra de Texto Automática, na célula (clicando em Página Inicial > ícone Quebrar Texto Automaticamente); PORÉM o texto não se ajusta na célula.

Acredito que o formato Tabela não permite esse alinhamento automático do texto à célula.

Manualmente é possível fazer esse alinhamento. Basta arrastar a altura da linha correspondente até que o texto caiba completamente dentro da célula, sem cortes.

Mas, NÃO gostaria de deixar visíveis os números das linhas. Além disso, uma macro seria o ideal para um usuário leigo fazer esse ajuste.

Portanto, solicito a ajuda dos colegas, para tentar deixar visível todo conteúdo nas células, ajustando automaticamente as respectivas linhas, para que o texto fiquem sem cortes.

A propósito, creio que, melhor do que aplicar a uma linha individualmente, mais interessante seria fazer o ajuste automático de TODAS as linhas da tabela (é só um palpite!).

Nesse caso, seria uma macro vinculada apenas à tabela.

Desde já, agradeço à ajuda.

Nilton Cézar · 2 de maio de 2021

Olá, pessoal.

Como eu havia postado anteriormente, estava buscando uma macro para deixar todo o texto visível na célula da Tabela do meu projeto.

Para isso, encontrei uma macro, para fazer o ajuste da altura das linhas na Tabela; porém não deu certo. Vejamos:

'Ajuste automático de linha
Public Sub Redimencioanamento_linha_automático()

Rows(Range("TabelaPensãoPorMorte")).AutoFit

End Sub

Não sei o que deu errado.

A propósito, caso necessário, a descrição da macro acima está bem detalhada na postagem anterior. E, na postagem de quinta (29/04), enviei, em anexo, uma cópia do meu projeto.

Sendo assim, gostaria da ajuda dos colegas, para criar uma macro, para tentar deixar visível todo conteúdo nas células, ajustando automaticamente as respectivas linhas, para que o texto fiquem sem cortes na tabela "TabelaPensãoPorMorte".

Agradeço quem puder me ajudar.

Midori · 3 de maio de 2021

21 horas atrás, Nilton Cézar disse:

Como eu havia postado anteriormente, estava buscando uma macro para deixar todo o texto visível na célula da Tabela do meu projeto.

Veja se assim resolve,

Range("TabelaPensãoPorMorte").EntireRow.AutoFit

Nilton Cézar · 4 de maio de 2021

19 horas atrás, Midori disse:
Veja se assim resolve,
Range("TabelaPensãoPorMorte").EntireRow.AutoFit

Midori, ficou muito bom! E o interessante é que o ajuste ficou personalizado. Ou seja, cada linha é redimensionada conforme o tamanho do texto na célula.

Muito obrigado pela macro.

Estou com uma dúvida.

Meu projeto contêm células bloqueadas. Mas, para executar algumas macros, será necessário o desbloqueio de algumas células.

Coloquei como exemplo uma macro com linhas de comando de desbloqueio/bloqueio de células (em azul).

Pois bem, minha dúvida é apenas quanto à montagem da macro.

Nesse caso, os códigos de DESBLOQUEIO ficam ANTES da macro principal, e os de BLOQUEIO, logo APÓS, conforme a descrição abaixo?

Sub RelAudRELATÓRIO_InserirLinha()

'Variável para desbloquear a planilha.

Dim pswStr As String

'Update by ExtendOffice 20181106

pswStr = "123456"

On Error Resume Next

Application.ScreenUpdating = False

ActiveSheet.Unprotect Password:=pswStr

‘Macro para inserir linha

Range("TabelaRelatório").ListObject.ListRows.Add AlwaysInsert:=True

'Após, a planilha volta a ser bloqueada automaticamente.

'ActiveSheet.Protect Password:=pswStr, DrawingObjects:=False, _

Contents:=True, Scenarios:=False, _

AllowFormattingCells:=True, AllowFormattingColumns:=True, _

AllowFormattingRows:=True, AllowInsertingColumns:=True, _

AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _

AllowDeletingColumns:=True, AllowDeletingRows:=True, _

AllowSorting:=True, AllowFiltering:=True, _

AllowUsingPivotTables:=True

End Sub

Agradeço se puder me tirar essa dúvida.

Midori · 4 de maio de 2021

5 horas atrás, Nilton Cézar disse:

Nesse caso, os códigos de DESBLOQUEIO ficam ANTES da macro principal, e os de BLOQUEIO, logo APÓS, conforme a descrição abaixo?

Seu código terá que desproteger a planilha antes de qualquer edição de célula bloqueada, isso vale para escrita/edição da tabela e inclusão/exclusão de linhas.

Pode fazer como mostrou, mas acho melhor criar as Sub para proteger e desproteger (com o parâmetro da senha) já que sua planilha deve fazer isso diversas vezes. Aí é só chamar os procedimentos antes e depois de cada operação.

Nilton Cézar · 4 de maio de 2021

2 horas atrás, Midori disse:

Pode fazer como mostrou, mas acho melhor criar as Sub para proteger e desproteger (com o parâmetro da senha) já que sua planilha deve fazer isso diversas vezes. Aí é só chamar os procedimentos antes e depois de cada operação.

Midori, confesso que não entendi as explicações.

Acho que não expliquei bem. E creio que não fui feliz no exemplo.

Vejamos. A tabela do meu projeto ficará desbloqueada, bem como todas as células abaixo dela, já que a tabela poderá se estender por várias linhas abaixo.

Assim, pretendo deixar bloqueada apenas as outras células, como algumas células que ficarão acima da tabela, pois, quanto a estas, não permitirei edição, como, por exemplo, onde fica os nomes PROCESSO, AUTOR(A), DER, INDEFERIMENTO, etc, conforme imagem em anexo.

Dessa forma, as macros vinculadas à tabela NÃO necessitarão de códigos para desbloquear/bloquear a planilha, a exemplo das macros INSERIR ÚLTIMA LINHA, EXCLUIR VÁRIAS LINHAS, REDIMENCINAR LINHAS.

Assim, acho que apenas a macro de deletar informações inseridas no Relatório e o evento Change (para ocultar/reexibir linhas) precisarão da macro para desbloquear/bloquear planilha.

Com base nisso, vou utilizar, como exemplo, para melhor compreensão, o evento Change, acima citado (coloquei em azul os códigos para desbloquear/bloquear). Vejamos:

'Função para pesquisar o intervalo com base no benefício e aplicar no EVENTO CHANGE para ocultar/reexibir linhas.

Function BuscaIntervalo(Beneficio As String) As Variant
Dim Intervalo As Variant

On Error Resume Next

Intervalo = Replace(WorksheetFunction.VLookup( _
Beneficio, Sheets("Benefício").[A:B], 2, 0), ";", ":")

If Err.Number <> 0 Then
Intervalo = False
End If
BuscaIntervalo = Intervalo

End Function

_________________________________________________

Private Sub Worksheet_Change(ByVal Target As Range)

'Variável para desbloquear a planilha.

Dim pswStr As String

'Update by ExtendOffice 20181106

pswStr = "123456"

On Error Resume Next

Application.ScreenUpdating = False

ActiveSheet.Unprotect Password:=pswStr

If Target.Address = "$D$13" Then
Dim Intervalo As Variant

Range("24:75").EntireRow.Hidden = True
  Intervalo = BuscaIntervalo(Target.Value)

'De acordo cm a condição IF abaixo, a macro não vai exibir nenhuma linha quando o valor do benefício na coluna B for igual a N (de nenhuma linha).
If UCase(Intervalo) <> "N" Then

'Conforme a condição IF a seguir, as linhas abaixo serão exibidas para qualquer texto que não tenha cadastro na planilha Benefício.
If Intervalo <> False Then
Range(Intervalo).EntireRow.Hidden = False
Else
Range("73:75").EntireRow.Hidden = False
End If
End If
End If

'Após, a planilha volta a ser bloqueada automaticamente.

'ActiveSheet.Protect Password:=pswStr, DrawingObjects:=False, _

Contents:=True, Scenarios:=False, _

AllowFormattingCells:=True, AllowFormattingColumns:=True, _

AllowFormattingRows:=True, AllowInsertingColumns:=True, _

AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _

AllowDeletingColumns:=True, AllowDeletingRows:=True, _

AllowSorting:=True, AllowFiltering:=True, _

AllowUsingPivotTables:=True

End Sub

Acho que agora ficou melhor de entender.

Sendo assim, os códigos de DESBLOQUEIO ficam ANTES da macro principal, e os de BLOQUEIO, logo APÓS, conforme acima mostrado?

Agradeço pelas orientações.

Midori · 4 de maio de 2021

56 minutos atrás, Nilton Cézar disse:

Dessa forma, as macros vinculadas à tabela NÃO necessitarão de códigos para desbloquear/bloquear a planilha, a exemplo das macros INSERIR ÚLTIMA LINHA, EXCLUIR VÁRIAS LINHAS, REDIMENCINAR LINHAS.

Mesmo com algumas células desbloqueadas, antes de rodar essas macros também tem que desproteger e na sua planilha já tem os comandos para isso nessas macros. Isso é necessário quando a lista de permissões não tem essas opções marcadas.

57 minutos atrás, Nilton Cézar disse:

Com base nisso, vou utilizar, como exemplo, para melhor compreensão, o evento Change, acima citado (coloquei em azul os códigos para desbloquear/bloquear).

A planilha é desprotegida antes das ocultar/exibir as células, está correto. Assim como no caso das macros para inserir/excluir linhas.

Só no caso da edição da tabela não terá que desproteger, já que a tabela está desbloqueada.

Nilton Cézar · 5 de maio de 2021

Midori, obrigado pelas explicações.

Encontrei aquela macro desbloqueio/bloqueio da internet.

Para aproveitar a postagem anterior, esqueci de perguntar se a macro acima está com o código correto. Mas, enfim.

Quando eu tiver oportunidade, irei fazer as configurações do Relatório de Audiência.

Espero que não tenha mais dúvidas ou seja necessário algum incremento de automoção.

Estando tudo concluído, colocarei tópico como resolvido.

Obrigado, mais uma vez, Midori.

Nilton Cézar · 6 de maio de 2021

Em 05/05/2021 às 08:52, Nilton Cézar disse:

Espero que não tenha mais dúvidas ou seja necessário algum incremento de automação.

Parece que falei cedo demais!

Pretendia apenas usar o gravador de macro, para deletar as informações do Relatório, mas percebi que isso não funciona com a Tabela do citado Relatório.

É que a tabela pode assumir várias tamanhos. Ou seja, pode ter poucas linhas ou várias linhas. Vai depender da quantidade de dados inseridos. Assim, uma macro que delete até o final da guia não faz sentido!

Pois bem, montei essa macro da internet, a fim de deletar as informações da tabela:

Sub deletar()

Dim Resposta As Integer
Resposta = MsgBox("Deseja realmente exluir as informações?", VBA.vbYesNo, "Excluir!")
If Resposta <> VBA.vbYes Then
Exit Sub
Else
Range("TabelaRelatórioAudiência").EntireRow.Delete
End If

End Sub

Contudo, essa macro deu pane total no projeto. Primeiro, os ícones de inserir última linha e excluir última linha ficaram sem as macros anteriormente atribuídas.

Após restabelecer as macros acima, quando clico no ícone de inserir última linha, o cursor vai para a última linha da guia (1.048.576).

Além do mais, ela está excluindo também todas as linhas da tabela (exceto a primeira). Era para excluir somente as informações constantes da tabela.

Sendo assim, gostaria da ajuda, para tentar criar essa macro, para deletar apenas as informações da tabela, com uma msgbox de aviso de exclusão.

Ao final, pretendo criar uma macro para chamar 1) a macro acima solicitada e 2) a macro de deletar dados fora da tabela obtida por gravação.

Agradeço a ajuda!

Relatório de Audiências-Em elaboração (Salvo automaticamente) - TESTE-OCULTAR LINHAS.rar

Nilton Cézar · 17 de maio de 2021

Olá, pessoal.

Vim fazer umas reconsiderações.

Pois bem, sobre a informação a seguir:

Em 06/05/2021 às 09:50, Nilton Cézar disse:

Contudo, essa macro deu pane total no projeto. Primeiro, os ícones de inserir última linha e excluir última linha ficaram sem as macros anteriormente atribuídas.

.

.

.

Além do mais, ela está excluindo também todas as linhas da tabela (exceto a primeira). Era para excluir somente as informações constantes da tabela.

Na verdade, cometi um equívoco, pois as macros de inserir última linha e excluir última linha, presentes no projeto do Relatório e acima referidas, diziam respeito a duas macros obsoletas, que inicialmente não executavam os comandos da forma como eu pretendia.

Portanto, podem desconsiderar essa informação.

A propósito, sobre a necessidade da macro abaixo:

Em 06/05/2021 às 09:50, Nilton Cézar disse:

Sendo assim, gostaria da ajuda, para tentar criar essa macro, para deletar apenas as informações da tabela, com uma msgbox de aviso de exclusão.

Quanto a isso, já foi solucionado.

Por ora, deixarei em aberto o tópico. Caso conclua meu projeto, colocarei o status resolvido nele.

Ficam, portanto, os registros.

Obrigado.