Até este ponto criamos vários menus e o leitor deve ter percebido que se o volume de menus e botões for elevado teremos um grande problema, pois o código ficará muito longo.

Esta parte é dedicada à automação deste processo. Embora os métodos anteriores sejam considerados “automações”, aqui automação quer dizer separar o código dos itens que compõem a barra de comando e menus.

Vamos supor que desejamos inserir um menu antes do menu Arquivo no Excel. Este novo menu conterá 8 diferentes botões e dois popups. O primeiro popup conterá 5 botões e o segundo popup conterá 6 botões. Coloque agora no contexto dos códigos anteriores.

É desnecessário dizer que o código para a construção de tal menu será enorme. Sem contar que se houver necessidade de acrescentar ou retirar menus e botões temos que editar diretamente no código. Você esqueceu um acento e tem que correr para o código. Um menu foi inserido na ordem errada e lá vamos nós para o código novamente.

Com certeza deve haver uma forma mais fácil para fazer isso, certo? Correto. Contudo, formas mais fáceis de fazer algo estão sempre evoluindo. Antes viajar de cavalo era eficiente, depois foi carro e hoje é avião. Em breve será foguetes e quem sabe um dia viajaremos por tele transportação como no Jornada nas Estrelas.

Os métodos apresentados funcionam assim. Hoje, eles são eficientes. Amanhã alguém inventa uma idéia melhor. Portanto, ao desenvolver os métodos apresentados pense em como você pode melhorará-los.

Deixando o papo furado de lado, vamos ao que interessa: como melhorar o procedimento de criação de menus.

Primeiramente, precisamos estar claros do que desejamos fazer. Por exemplo, queremos acrescentar um FaceID? Queremos acrescentar um atalho? Qual a ação a ser executada? O que está sendo adicionado é uma barra de comando, menu ou botão? Em que ordem eles entram? Onde eles estarão posicionados? Flutuando ou anexado ao menu principal do Excel?

Estas são perguntas que precisamos fazer antes de construirmos qualquer coisa. Observe que quando perguntamos algo, efetivamente já temos as respostas do que procuramos. Pense nisso. Você sabe as respostas, porém, não sabe como implementá-las. Esta é a diferença. Portanto, se há uma pergunta tem que haver uma resposta, caso contrário a pergunta é inválida.

Como isto em mente, nós estamos prontos para iniciar a construção de nossa folha de solução. Aqui, passarei o meu raciocínio por trás da solução por mim apresentada.

O Excel é uma grande matriz e utilizaremos isso em nosso favor. Por exemplo, imagine a Plan1 como sendo a nossa barra de comando. Tudo que está dentro da Plan1 é, portanto, um item de nossa barra de comando, por analogia.

Agora imagine a primeira linha com diversos cabeçalhos e embaixo de cada cabeçalho um série de itens. Se cada cabeçalho é um menu (popup), então, cada item abaixo é um botão deste popup.

Outra forma de visualizar isso é dizer que cada planilha é um menu (popup) e os itens contidos dentro de cada planilha representam os botões destes popups. O nome da barra de comando seria, digamos, o nome da pasta.

Como podemos ver, com um pouco de imaginação estamos contemplando formas diferentes de solucionar o mesmo problema. Como cada objeto tem suas propriedades, precisamos definir quais propriedades nos interessam. Para o primeiro exemplo estaremos utilizando as seguintes propriedades:

• Name/Caption
• FaceId (para botões somente)
• OnAction (para botões somente)
• ShortcutText (para botões somente)
• BeginGroup

Como a barra de comando é composta por três objetos, estaremos manipulando estes três casos:

• CMDBAR (a barra de comando em si)
• MMENU (o menus tipo popup)
• BOTAO (os botões que executarão os comandos)

Para evitar erro na digitação (o que nos causaria dores de cabeça no código) os três itens acima serão selecionados a partir de uma lista de validação.

Uma vez construída a sua tabela em uma planilha do Excel, o formato geral deve ter a seguinte cara:

Figura 0‑29

Como cada propriedade está escrita em inglês no VBA, os cabeçalhos também foram inseridos em inglês para facilitar a referência, leitura e compreensão.

Como de praxe, iniciamos o nosso código com a declaração das variáveis:

Sub MenusAutomatizados()

   Dim ws      As Worksheet

   Dim linha   As Long

   Dim cBar    As CommandBar

   Dim mnu     As CommandBarPopup

   Dim btn     As CommandBarButton

   Set ws = ThisWorkbook.Sheets("ItensMenus")

   linha = 3 

End Sub

A variável ws é importante pois precisamos definir onde estão os itens que fazem parte de nossa barra de comando. A variável linha é importante, pois efetuaremos um loop na primeira coluna para varrer todos os itens entrados. Como o primeiro item está na linha número 3 o valor inicial de nossa linha é definido como sendo 3.

O nosso próximo passo é definir os casos:

Sub MenusAutomatizados ()

   'Aqui entram as linhas apresentadas acima 

   With ws

    Do Until IsEmpty(.Cells(linha, 1))

     TIPO = .Cells(linha, 1)

     Select Case TIPO

       Case "CMDBAR"

        'Código de construção da barra entrará aqui

       Case "MMENU"

        'Código de construção da menu entrará aqui

       Case "BOTAO"

        'Código de construção do botão entrará aqui

     End Select

     linha = linha + 1

    Loop

   End With  

End Sub

O que o código acima está fazendo é definir o TIPO conforme o loop ocorre, seleciona o caso do tipo (ie “CMDBAR”, “MMENU” ou “BOTAO”). As casos estão entre aspas porque são textos (string). Uma vez decidido qual é o caso contido na ws.cells(linha,1) o caso é executado.

Como os objetos são inseridos em ordem, precisamos definir isso claramente. Como foi dito anteriormente, se em um planilha temos um coluna com um cabeçalho que representa o menu, então, tudo abaixo deste cabeçalho representa os botões deste menu.

A estrutura apresentada acima segue esta mesma lógica. Se esta ordem não for seguida, os botões serão adicionados aos menus errados.

   Case "CMDBAR"

    Set cBar = CommandBars.Add _

     (Name:=.Cells(linha, 2), Position:=msoBarFloating)

Se este foi o caso encontrado durante o loop ele é executado. O nosso próximo caso é o menu popup (Case "MMENU"). A construção deste caso é:

   Case "MMENU"

    Set mnu = cBar.Controls.Add(Type:=msoControlPopup)

     mnu.Caption = ws.Cells(linha, 2)

Como o rótulo (Caption) encontra-se na segunda coluna, basta definir a linha atual do loop como sendo o local onde o texto se encontra. O mesmo vale para o BeginGroup.

Finalmente, temos o caso do botão (Case "BOTAO"):

   Case "BOTAO"

    Set btn = mnu.Controls.Add(Type:=msoControlButton)

     With btn

       .Caption = ws.Cells(linha, 2)

       .FaceId = ws.Cells(linha, 3)

       .OnAction = ws.Cells(linha, 4)

       .ShortcutText = ws.Cells(linha, 5)

       .BeginGroup = ws.Cells(linha, 6)

    End With

Aqui, as propriedades do botão são inseridas conforme a sua respectiva posição na tabela apresentada acima.

Montando o quebra-cabeça do código acima temos:

Sub MenusAutomatizados ()

   Dim ws      As Worksheet

   Dim linha   As Long

   Dim cBar    As CommandBar

   Dim mnu     As CommandBarPopup

   Dim btn     As CommandBarButton

   Set ws = ThisWorkbook.Sheets("ItensMenus")

   linha = 3

   delBarra 'Deleta a barra anterior caso ela exista

   With ws

    Do Until IsEmpty(.Cells(linha, 1))

    TIPO = .Cells(linha, 1)

    Select Case TIPO

     Case "CMDBAR"

       Set cBar = CommandBars.Add _

        (Name:=.Cells(linha, 2), Position:=msoBarFloating)

     Case "MMENU"

       Set mnu = cBar.Controls.Add(Type:=msoControlPopup)

        mnu.Caption = ws.Cells(linha, 2)

     Case "BOTAO"

       Set btn = mnu.Controls.Add(Type:=msoControlButton)

        With btn

          .Caption = ws.Cells(linha, 2)

          .FaceId = ws.Cells(linha, 3)

          .OnAction = ws.Cells(linha, 4)

          .ShortcutText = ws.Cells(linha, 5)

          .BeginGroup = ws.Cells(linha, 6)

        End With

     End Select

     linha = linha + 1

    Loop

   End With

   'Utiliza o mesmo argumento que o BeginGroup

   cBar.Visible = ws.Cells(3, 6)  

End Sub

Como podemos ver, o volume de código é drasticamente reduzido e uma vez que ele esteja funcionando, precisamos somente nos concentrar na digitação dos itens que compõem nossa barra de comando.

Após a execução de nosso código, obtemos o seguinte resultado baseado nas informações digitadas na planilha contendo os itens do menu:

Figura 0‑30

Para provar a versatilidade deste método, iremos acrescentar mais um caso: “ATALHOS”. Aqui, estamos interessados em inserir um botão de atalho no menu de atalho para as células (barra de comando número 29). À nossa lista anterior vamos acrescentar a palavra “ATALHO” conforme a figura abaixo:

Figura 0‑31

Assim sendo, temos mais um caso (Case) a ser avaliado no código anterior. Conforme o loop ocorre o novo caso sendo avaliado é:

  Case "ATALHO"

   Set btn = CommandBars(.Cells(linha, 7)).Controls.Add

     With btn

      .Caption = ws.Cells(linha, 2)

      .FaceId = ws.Cells(linha, 3)

      .OnAction = ws.Cells(linha, 4)

      .BeginGroup = ws.Cells(linha, 6)

     End With

Basta inserir o código acima no código final anterior e temos mais caso que é devidamente tratado pelo VBA.

Após a execução tudo é construído e temos um belo resultado:

Figura 0‑32

O método utilizado acima é o mesmo método utilizado pelo Excel para listar todos os menus e comandos disponíveis. Se você pretende criar menus mais complexos, estude a maneira como o Excel lista as barras de comandos. Utilize a pasta de trabalho Listar Menus para fazer isso.

Terminamos aqui a nossa jornada no mundo dos menus em Excel. Qualquer dúvida ou sugestão, não hesite em contatar o autor.