Macro de evento para alterar o cabeçalho do Excel - Dicas do Excel

Donna do Missouri perguntou:

Tem alguma ideia de como posso obter o caminho onde o documento está arquivado indicado no cabeçalho ou rodapé - ou em qualquer parte do documento. Posso obter o nome do arquivo com & f, mas não consigo descobrir como fazer o caminho.

Em primeiro lugar, entendo que a Microsoft adicionou essa funcionalidade ao Excel XP e agradeço a eles, pois é um problema frequente. Qualquer leitor que já tenha atualizado para o Office XP não precisará das técnicas da dica desta semana para resolver este problema, mas elas ainda serão úteis para resolver outros problemas.

A solução para essa dica é um tipo especial de macro denominado macro manipulador de eventos. Vamos assumir brevemente o controle do Excel sempre que ele estiver prestes a imprimir nossa pasta de trabalho e adicionar o caminho atual ao cabeçalho.

Muitos usuários do Excel se aventuraram a gravar macros simples. As macros são armazenadas em um módulo denominado Módulo1 ou Módulo2 e passam a fazer parte do seu projeto. Hoje, vou discutir macros do manipulador de eventos. Essas macros residem em um módulo de código especial associado a cada planilha ou pasta de trabalho.

Dicas anteriores, como a dica Digite o tempo do Excel sem os dois pontos, lidaram com o evento Worksheet_Change. A dica de hoje requer que adicionemos algum código ao evento BeforePrint da pasta de trabalho.

O código adicionado a um evento será executado sempre que esse evento for "disparado". Nesse caso, sempre que a pasta de trabalho do Excel for impressa, antes do início da impressão, o Excel passa o controle para o código VBA e permite que qualquer coisa que você possa especificar no código VBA aconteça automaticamente antes da impressão.

Vou assumir que você é novo em macros de manipulador de eventos. Vou explicar exatamente como chegar ao lugar certo para inserir essa macro.

Eu tenho uma pasta de trabalho chamada 'Tip055 Sample.xls'. Com a pasta de trabalho carregada no Excel, irei pressionar alt = "" + F11 para iniciar o editor visual básico. A aparência padrão do editor é mostrada à direita. À esquerda, você geralmente vê um painel Projeto empilhado no topo de um painel Propriedades. A maior parte do lado direito da tela inclui um painel de código. Se você não tiver macros na pasta de trabalho, o painel de código ficará cinza, conforme mostrado à direita.

Eu adicionei palavras de script em itálico azul à imagem para identificar os três painéis - você não verá isso em seu exemplo.

É importante que você consiga ver o Painel de Projeto no editor VB. Se a sua visualização do editor VB não incluir o painel do projeto, pressione Ctrl + R para visualizar o Painel do Projeto. Ou clique no ícone da barra de ferramentas mostrado abaixo:

O painel do projeto mostrará um projeto para cada pasta de trabalho do Excel aberta e cada suplemento instalado. Clique no sinal de adição cinza ao lado do nome da pasta de trabalho para expandir o projeto da pasta de trabalho. Em seguida, clique no sinal de mais cinza próximo à pasta Objetos do Microsoft Excel para expandir a pasta do objeto. Agora você deve ver uma entrada para cada planilha e uma entrada chamada ThisWorkbook.

Clique com o botão direito do mouse na entrada para ThisWorkbook e selecione View Code no menu pop-up.

Agora você provavelmente terá um grande painel de código branco em branco ocupando o lado direito da tela. Existem dois menus suspensos na parte superior do painel de código que dirão (Geral) e (Declarações).

• No menu suspenso à esquerda, selecione Pasta de trabalho.
• A lista suspensa direita agora está preenchida com todos os eventos programáveis ​​associados à pasta de trabalho. Existem eventos aqui que irão executar o código sempre que a pasta de trabalho for aberta, ativada, desativada, etc. Hoje, queremos escrever o código no evento BeforePrint, então selecione BeforePrint no menu suspenso à direita.

Observe que toda vez que você seleciona algo no menu suspenso à direita, o editor VBA grava a linha inicial e final do código no módulo de código para você. A primeira vez que você altera o menu suspenso à esquerda para Pasta de trabalho, provavelmente recebeu o início de uma sub-rotina Workbook_Open por padrão. Se não for escrever um procedimento Workbook_Open, você deve considerar a exclusão deste procedimento vazio.

Agora, vamos escrever o código VBA. Existem algumas variáveis ​​úteis que você pode usar.

• ActiveWorkbook.Path retornará o caminho da pasta de trabalho. Pode ser semelhante a "C: Meus Documentos MrExcel".
• ActiveWorkbook.FullName retornará o caminho e o nome do arquivo da pasta de trabalho. Pode ser semelhante a "C: Meus documentos MrExcel Tip055 Sample.xls".

Você pode atribuir esta variável a uma das 6 posições a seguir:

Worksheets("Sheet1").PageSetup.LeftHeader = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.CenterHeader = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.RightHeader = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.LeftFooter = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.CenterFooter = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.RightFooter = ActiveWorkbook.FullName

Aqui estão três macros de amostra possíveis.

Esta macro terá o caminho e o nome do arquivo adicionados como rodapé direito da planilha ativa:

Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.RightFooter = ActiveWorkbook.FullName End Sub

Esta macro terá o caminho adicionado como cabeçalho esquerdo da Folha1 e como rodapé central da Folha2:

Private Sub Workbook_BeforePrint(Cancel As Boolean) Worksheets("Sheet1").PageSetup.LeftHeader = ActiveWorkbook.Path Worksheets("Sheet2").PageSetup.CenterFooter = ActiveWorkbook.Path End Sub

Se você tende a usar a opção "Pasta de trabalho inteira" ao imprimir, esta versão adicionará o nome completo como rodapé central em todas as folhas:

Private Sub Workbook_BeforePrint(Cancel As Boolean) For Each Sh In ActiveWorkbook.Worksheets Sh.PageSetup.CenterFooter = ActiveWorkbook.FullName Next Sh End Sub

Siga um destes exemplos ou crie seu próprio. Quando terminar, feche o editor VBA com Arquivo> Fechar e Retornar ao Microsoft Excel.

Sempre que você imprimir uma planilha, o código será executado e inserirá o caminho atual no cabeçalho ou rodapé apropriado que você indicou no código VBA.

Algumas notas e cuidados:

• Os usuários novatos do Excel não terão idéia de que esse código está na pasta de trabalho. Quando eles abrem a pasta de trabalho, eles podem receber o aviso de segurança de que o arquivo contém macros, mas não haverá nenhum aviso quando o código VBA destruir tudo o que eles tinham como rodapé central e colocar o nome do caminho lá. Isso pode causar azia. Imagine que daqui a 5 anos alguém esteja usando sua pasta de trabalho e o novo gerente queira que o nome do arquivo passe do rodapé central para o rodapé direito. Essa pessoa pode saber como alterar manualmente as configurações em Arquivo> PageSetup, mas se ela não souber que o código está lá, ficará maluca, pois o código continuamente altera seus rodapés.

  It is really unlikely that they will be able to find your code module, but just in case they do, you might want to leave a comment in the code module that directs them back to this website for an explanation.

  You also might want to add a comment to cell A1 reminding yourself that there is an event handler set up to change the print headers.

• There is a setting within VBA which prevents events from running. If your macro suddenly stops working, t is common to find that something within VBA has turned this setting to prevent the events from running. The common scenario is that a programmer starts a macro with:

  Application.EnableEvents = False '… macro here… Application.EnableEvents = True

  Quando ocorre um erro na macro, os eventos nunca são ativados novamente. Quando isso acontece, há poucos avisos. Se você suspeitar que seus manipuladores de eventos não estão sendo executados, vá para o editor visual básico. Pressione Ctrl + g para abrir um painel imediato. No painel imediato, digite:

  Print Application.EnableEvents

  e pressione Enter. Se você achar que está definido como False, digite a seguinte linha no painel imediato:

  Application.EnableEvents = True

  e pressione Enter.

Obrigado a Donna por uma ótima pergunta. No processo de explicação da resposta, foi uma grande oportunidade para expandir o conceito de manipuladores de eventos em VBA.