Guru do Excel e Power BI

Boa Tarde!
Necessito de uma Macro que não permita alterações em células que contenham fórmulas.
OBS: Aplicáveis a toda planilha
Desde já agradeço

Olá Saulo,

Segue a planilha. A macro atua na planilha que estiver ativa ao ser executada, e verifica se a proteção está ou não ativa. Se estiver, a retira. Se não estiver, protege.

A fonte das fórmulas é vermelha com proteção, e preta sem, apenas para que você veja o funcionamento da macro. Após os testes, desproteja as fórmulas e apague da macro as 2 linhas que iniciam com Formulas.Font.ColorIndex.

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo", acima e a direita.

Jimmy San Juan

Prezado Jimmy 15 Out 2019 às 19:58 Bom Dia!
Primeiramente Obrigado Por Dispor de seu Tempo em Ajudar-me!
Então...
A ideia é essa mesmo, porém a macro está condicionada ao click no botão “Bloqueia/Desbloqueia”, o desejado é que essa funcionalidade fique implícita na planilha, ou seja, a macro atuaria na planilha ativa sem necessidades de ação do clique no botão “Bloqueia/Desbloqueia” fazendo a mesma função, ou seja, bloqueando alterações somente em células que contenham fórmulas.

Ok Saulo,

Algumas perguntas:

- As fórmulas são fixas, sempre as mesmas, ou a planilha vai crescendo ou sendo alterada e as fórmulas vão sendo colocadas em outros lugares?

- Se forem fixas, basta rodar a macro uma vez, e nunca mais, correto?

- Se forem mais dinâmicas, você gostaria que ao digitar uma dela, a macro de imediato a bloqueasse? É isso?

- E se você tiver que alterar uma delas, como gostaria que isso funcionasse? Da forma que está, basta desproteger a planilha, alterar/apagar a fórmula, e proteger novamente. Assim te atende?

Nas mensagens acima que você julgar que ajudaram de alguma forma na solução do problema, peço que dê um Like, clicando no botão com o "positivo", acima e a direita. Obrigado.

Jimmy San Juan

Bom dia Jimmy,

Estou aproveitando esse código para aplicar em algumas planilhas que tenho aqui, como eu faço para ele ignorar quando a pasta não tiver fórmulas?

Estou usando esse código aqui

Prezado Jimmy 16 Out 2019 às 11:30
Primeiramente Obrigado Por Dispor de Seu Tempo Para Ajudar-me.
Apliquei sua solução no arquivo anexo na planilha “Registro Pendencia NC” , porém ela (sua solução) só protege a planilha “Registro Pendencia NC” de forma manual, ou seja, tenho que clicar no botão “Bloqueio/Desbloqueio Planilha” para bloquear/desbloquear a planilha todas as vezes que um novo dado é inserido através da planilha “Inserir Pendencia NC” .
Se possível, teria como fazer o bloqueio de forma automática, ou seja, agregar sua solução a planilha “Inserir Pendencia NC” de forma que sempre após uma nova inserção é realizada através do botão “INSERIR” esses de dados também seriam protegidos sem necessidades de clicar no botão “Bloqueio/Desbloqueio Planilha”
Existe sim a necessidade de atualizações de formulas bem como criação de novas funcionalidades, então se for possível manteríamos o botão “Bloqueio/Desbloqueio Planilha “ na planilha “Registro Pendencia NC” com um detalhe (se possível), colocar uma senha dentro da macro de forma que ao tentar desbloquear exigirá a digitação da senha: modular2017
Favor avaliar e se possível atender as solicitações.
Desde já agradeço

Olá Rit,

Teste isso:
Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo", acima e a direita.

Jimmy San Juan

Prezado Jimmy 16 Out 2019 às 11:30
Primeiramente Obrigado Por Dispor de Seu Tempo Para Ajudar-me.
Apliquei sua solução no arquivo anexo na planilha “Registro Pendencia NC” , porém ela (sua solução) só protege a planilha “Registro Pendencia NC” de forma manual, ou seja, tenho que clicar no botão “Bloqueio/Desbloqueio Planilha” para bloquear/desbloquear a planilha todas as vezes que um novo dado é inserido através da planilha “Inserir Pendencia NC” .
Se possível, teria como fazer o bloqueio de forma automática, ou seja, agregar sua solução a planilha “Inserir Pendencia NC” de forma que sempre após uma nova inserção é realizada através do botão “INSERIR” esses de dados também seriam protegidos sem necessidades de clicar no botão “Bloqueio/Desbloqueio Planilha”
Existe sim a necessidade de atualizações de formulas bem como criação de novas funcionalidades, então se for possível manteríamos o botão “Bloqueio/Desbloqueio Planilha “ na planilha “Registro Pendencia NC” com um detalhe (se possível), colocar uma senha dentro da macro de forma que ao tentar desbloquear exigirá a digitação da senha: modular2017
Favor avaliar e se possível atender as solicitações.
Desde já agradeço

Olá Saulo,

a) você quer que as fórmulas das duas planilhas sejam protegidas, ou só da "Inserir Pendencia NC", ou só da "Registro Pendencia NC"?

b) existe alguma região da planilha onde essas fórmulas necessitam de proteção, ou elas podem ocorrer na planilha toda? Pergunto porque se a macro puder restringir sua faixa de atuação, ficará mais rápida.

Saulo, nas mensagens acima que você julgar que ajudaram de alguma forma na solução do problema, peço que dê um Like, clicando no botão com o "positivo", acima e a direita. Obrigado.

Jimmy San Juan

Prezado Jimmy 17 Out 2019 às 10:17
Primeiramente Muito Obrigado Por Dispor de Seu Tempo Para Ajudar-me.
Seguem Respostas:
a) Se possível as duas planilhas ("Inserir Pendencia NC" e "Registro Pendencia NC") devem ter as áreas que contenham Fórmulas protegida;
b) A proteção deve ocorrer em toda planilha onde houver células com quaisquer tipos de fórmulas.
Lembrando: Existe sim a necessidade de atualizações de formulas bem como criação de novas funcionalidades, então se for possível manteríamos o botão “Bloqueio/Desbloqueio Planilha “ na planilha “Registro Pendencia NC” com um detalhe (se possível), colocar uma senha dentro da macro de forma que ao tentar desbloquear exigirá a digitação da senha: modular2017
Favor avaliar e se possível atender as solicitações.
Desde já agradeço

Olá Saulo,

Amanhã retomarei seu tópico. Desculpe a demora.

Me diga apenas se você ainda tem a necessidade, ou já resolveu?

Jimmy San Juan

Prezado Jimmy 06 Nov 2019 às 00:19
Sim, Ainda Tenho Muita Necessidade e Não consegui resolução.
Se possível, peço que continue a análise e resolução do meu problema.
Fico no aguardo.
Desde já Agradeço.

Olá Saulo,

Desculpe a demora em responder.

Segue anexa a planilha.

Quando a planilha é desprotegida, coloquei uma mensagem notificando, mas questiono se ela é mesmo necessária, ou se seria melhor apenas exibir mensagem caso a senha esteja errada. Qual sua preferência?

Saliento que a senha dentro da macro é muito frágil. Qualquer pessoa com conhecimentos básicos de Excel poderá entrar na macro e vê-la, logo, esse sistema de senha não deve ser utilizado quando segurança é importante.

As macros estão no módulo “Bloqueio”, e as chamadas à macro ProDesproAutomatico estão nos eventos CHANGE de ambas as planilha.

A macro ProDesproManual é acionada pelos botões das planilhas.

É muito importante que você apague os módulos que não estão sendo usados. Você tem mais de 100 módulos, e aproximadamente 200 macros, a grande maioria remanescentes de gravação.

Faça os testes e avise se for necessário alterar algo.

Jimmy San Juan

Prezado Jimmy 07 Nov 2019 às 21:10
Primeiramente Muito Obrigado em dispor de seu Tempo Para Ajudar-me
A solução ficou excelente, tentarei replicar para demais planilhas que temos por aqui.
Vamos as respostas:
1) Melhor manter solução original, ou seja, a notificação de que a planilha foi desprotegida;
2) Concordo que o sistema de senha interno a macro é frágil, e agradeço sua preocupação, porém no momento essa é a melhor opção. OBS: Todos aqui somos basicão demais em macro.
Então..
Como Dito anteriormente, somos basicão mesmo, e sua solução ficou excelente, desculpe minha falta de conhecimentos por isso gostaria de sua orientação sobre as suas diretrizes: “As macros estão no módulo “Bloqueio”, e as chamadas à macro ProDesproAutomatico estão nos eventos CHANGE de ambas as planilha.”
Dúvidas:
1) Não localizei o módulo “Bloqueio” para ver a macro;
2) “à macro ProDesproAutomatico estão nos eventos CHANGE de ambas as planilha.”
Isso significa que elas (macros) estariam inseridas nos respectivos botões “bloqueio/desbloqueio planilhas”

Você Poderia Me “Dar os Caminhos das Pedras”?
Aguardo Seu FeedBack.
Atenciosamente.

Prezado Jimmy 07 Nov 2019 às 21:10
Primeiramente Muito Obrigado em dispor de seu Tempo Para Ajudar-me
Favor Desconsiderar a Anterior.
"Quebrei a Cabeça" e Consegui Entender Tudinho o Que Você Fez.
Excelente Solução

Saulo,

Na tela do editor do VBA (das macros), tem uma coluna à esquerda com os módulos. O primeiro módulo é o BLOQUEIO. Dando um duplo clique nele você abre p módulo, cujo conteúdo (macros) vão ser mostradas à direita.

As macros existentes nos módulos são de uso geral, são chamadas por botões, ou diretamente pela tela do editor do VBA, ou são chamadas por outras macros. Há macros que são de uso específico, e são executadas apenas quando alguns eventos pré-definidos acontecem. Alguns desses eventos são: Abertura ou fechamento da pasta de trabalho, seleção de uma planilha, alteração de alguma célula, cálculo, etc.

No nosso caso, quando qualquer célula da planilha é alterada, a macro do evento CHAGE é acionada, e ela, por sua vez, executa a macro que está no módulo BLOQUEIO.

Em outras palavras, foi dito ao Excel: “Excel, quando alguma célula for alterada, acione a macro do evento CHANGE, e ela deve acionar a macro do módulo. A macro do módulo deve verificar se a alteração em questão foi a inserção de uma fórmula na célula que foi inserida. Se não foi, pode encerrar. Se foi, marque essa célula para que fique bloqueada quando a planilha for protegida.”

As macros acionadas pelos botões podem estar em qualquer módulo, e não tem nada a ver com as macros de eventos.

Você deve ter feito dezenas de gravações de macros, não sei se propositalmente ou não, mas elas estão todas lá, ocupando espaço. Normalmente, uma planilha como a sua, com a finalidade de bloqueio como o seu, é feito com 2 ou 3 macros em um único módulo. Seu arquivo tem mais de 100 módulos, e 200 macros, provavelmente originárias das gravações que fez. Isso torna a planilha mais pesada e demorada para abrir.

Resumindo. Se você não utiliza macro alguma, exceto essas que fizemos juntos, me avise para que eu apague tudo aquilo, pois o quadro é assustador!! kkkkk

Quanto à senha, por mais basicão que seja, ao abrir o módulo ela irá aparecer. Alterei um pouco a macro para camuflar um pouquinho a senha. A segurança ainda é pequena, mas aumentou.
Coloquei a seguinte linha na macro:
Aux1 = "math or down undo let at row 213 023 199 724"
A senha é formada pela primeira letra (ou número) de cada “palavra” que está entre as aspas. Quando quiser alterar essa senha, coloque, para cada letra da senha pretendida, uma palavra iniciando por aquela letra.

Por exemplo: quero a senha Jimmy, então coloco, entre as aspas, as palavras “Join in my month yet”.
Se você acha que assim ficou complicado, posso voltar a como era, explícito assim:
Senha = “modular2017”

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan

Prezado Jimmy 08 Nov 2019 às 15:40
Aproveitando Vosso Conhecimento, deixa Eu te pedir mais um favor, se possível peço que analise a situação abaixo:
A Planilha “Registro Pendencia NC” recebe dados que incluo na planilha “Inserir Pendencia NC” após clique no botão “INSERIR”, teria possibilidades de todas as vezes que se clicasse no botão “INSERIR” da planilha “Inserir Pendencia NC” as linhas de grade na planilha “Registro Pendencia NC” ficassem com as bordas , porem somente das células A7 Até U7 e a cada inclusão fosse copiando as respectivas formatações?
Desde Já agradeço

Olá Saulo,

Não entendi muito bem o que você necessita. Quer que a macro coloque as bordas da faixa A7:U7? Somente nessa faixa? Quando diz "copiando as respectivas formatações" quer dizer as bordar ou toda a formatação?

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan

Prezado Jimmy 08 Nov 2019 às 19:46
Primeiramente Muito Obrigado Por dispor de seu Tempo Para Me Ajudar.
Então...
As Células “A1” até “U6” são destinadas a títulos e cabeçalho, portanto não entrariam na rotina da macro.
Os 1º dados de controle da planilha iniciam-se na célula “A7” e terminam na célula “U7” e após novas inclusões são exibidos nas células “A8” até “U8” e assim sucessivamente, ou seja, a cada nova inclusão os dados são gravados nas respectivas células posteriores.
Em Sendo essa a rotina de trabalho, a solicitação de ajuda é a seguinte: que após cada inclusão de dados na planilha, a formatação se replique na planilha somente para as células que contenham os dados, obedecendo aos range entre as células “A” até “U” de acordo com a formação inicial das célula “A7” a “U7”.
Desde Já Agradeço

Olá Saulo,

Entendi o que quer e alterei a macro. Peço que teste.

Quanto à senha, por mais basicão que seja, ao abrir o módulo ela irá aparecer. Alterei um pouco a macro para camuflar um pouquinho a senha. A segurança ainda é pequena, mas aumentou.
Coloquei a seguinte linha na macro:
Aux1 = "math or down undo let at row 213 023 199 724"
A senha é formada pela primeira letra (ou número) de cada “palavra” que está entre as aspas. Quando quiser alterar essa senha, coloque, para cada letra da senha pretendida, uma palavra iniciando por aquela letra.

Você não comentou nada a respeito disto. Preciso que me diga como prefere.

Você deve ter feito dezenas de gravações de macros, não sei se propositalmente ou não, mas elas estão todas lá, ocupando espaço. Normalmente, uma planilha como a sua, com a finalidade de bloqueio como o seu, é feito com 2 ou 3 macros em um único módulo. Seu arquivo tem mais de 100 módulos, e 200 macros, provavelmente originárias das gravações que fez. Isso torna a planilha mais pesada e demorada para abrir.

Resumindo. Se você não utiliza macro alguma, exceto essas que fizemos juntos, me avise para que eu apague tudo aquilo, pois o quadro é assustador!! kkkkk

E nem sobre isto. A planilha está demorando quase 30 segundos pra abrir no meu PC, e é uma planilha minúscula!
Se você não estiver disposto a mexer nisso, ok, mas se quiser, te dou apoio e limpamos essa bagunça.

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan