Conheça o Júlio Battisti

Julio Battisti - Autor de mais de 25 livros de informática Júlio Battisti tem mais de 50 livros publicados, centenas de e-books e é certificado Microsoft.

SAIBA MAIS...

Loja Virtual do Site

Curta Nossa Fanpage

« Anterior

Δ Página principal

¤ Índice

Próxima »

Criando menus, barras de comando e botões personalizados no Excel usando VBA
Autor: Robert Friedrick Martim

Lição 013 - Criando um Menu Camaleão

Vários clientes que adquiriram a primeira edição deste módulo me escreveram perguntando como fazer para criar um menu “camaleão”, aquele que muda conforme a necessidade. Supondo que você se encontra na planilha clientes, então, um menu fica disponível. Se você se encontra na planilha contas, outro menu aparece e assim sucessivamente.

A principio, estava um pouco relutante de escrever esta parte, afinal, o código para fazer isso já havia sido apresentado de forma indireta bastando apenas uma adaptação para o desejado. Contudo, após vez a real dificuldade de visualização da solução do problema, eu acredito que a decisão seja a mais acertada para que o leitor tenha em mãos um curso que seja realmente completo.

Primeiramente, precisamos visualizar o problema. O que realmente desejamos fazer? A idéia é ter um menu que seja modificado quando certa planilha é ativada. E o que é isso exatamente? Quando ativamos uma planilha nós disparamos um evento no aplicativo Excel, quando o evento é disparado podemos capturar o evento é utilizá-lo fazer uma outra coisa, como modificar a estrutura do menu ativo.

É exatamente esta idéia que precisamos colocar em prática. Uma forma simples de fazer isso é utilizar os eventos da própria planilha, por exemplo:

Private Sub Worksheet_Activate()

Call criarMenu

End Sub

Quando a planilha que contém o código acima é ativada, a sub-rotina criarMenu é chamada e o menu construído. Podemos fazer a mesma coisa na planilha seguinte, e na seguinte, e na seguinte, etc. Porém, teríamos que fazer isso para cada planilha o que pode se tornar uma bola de neve. Além do que ela não resolve o problema da seleção de uma planilha em uma outra pasta ativa.

Vamos supor que o código acima se encontra na Plan1 da Pasta1. O que ocorre se você seleciona a Plan1 da Pasta2? Sem dúvida que não acontecerá o que você espera, ou melhor, não acontecerá nada.

O formato do menu será o seguinte:

Figura 0‑27

Neste caso, temos um combobox que lista as planilhas disponíveis. O popup que irá mudar é o popup contas. Quando a planilha de fornecedores for ativada, o menu será modificado para o menu fornecedores e assim por diante:

Figura 0‑28

A complexidade de seu menu deve ser ditada pela sua real necessidade.

Algumas considerações antes de continuarmos:

O que ocorre quando mudamos de planilha?
Resp: Como dito, o menu deve mudar
O que ocorre quando mudamos de pasta?
Resp: O menu atual é removido e o combobox desativado
Qual o nível de complexidade?
Resp: Dependerá das suas necessidades, porém, eventos não existentes precisam ser criados através de classes e instanciados

Para o momento, estes são os três pontos que precisamos ter em mente. O primeiro, já havia discutido, o segundo é um extra e o terceiro é apenas para lembrá-lo que o código pode se tornar muito mais complexo do que o atual.

Então, vamos iniciar pela parte mais simples. Abra a janela de código da pasta de trabalho. Nela você deverá inserir as seguintes linhas:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call wsMenuDel

End Sub

Private Sub Workbook_Open()

Call wsMenus

End Sub

As rotinas funcionam nos eventos Open e BeforeClose da pasta de trabalho.

Iniciaremos pela declaração de algumas variáveis comuns a todas as sub-rotinas. Adicione um módulo e no topo do módulo adicione as seguintes variáveis e constantes:

'Constante públicas utilizadas no código

Public Const CMDBARNOME As String = "MENU CAMALEAO"

Public Const MENUFORNECEDORES As String = "Fornecedores"

Public Const MENUCLIENTES As String = "Clientes"

Public Const MENUCONTA As String = "Contas"

'Dimensiona os objetos contendo os eventos como sendo a classe onde

'onde eventos foram definidos

'appExcel refere-se aos eventos do aplicativo (Excel)

Dim appExcel As New Classe1

' cboBox refere-se aos eventos da combobox

Dim cboBox As New Classe1

Os objetos referentes à Classe1 serão discutidos nos comentários da classe.

O próximo passo é escrever todas as sub-rotinas para criação, remoção e reconstrução do menu. No mesmo módulo, você deverá adicionar as seguintes sub-rotinas:

	Sub wsMenus() ' Dimensiona os objetos Dim cmdBar As CommandBar Dim btn As CommandBarButton Dim btnDropDown As Object ' Remove a barra de comando caso ela já exista Call wsMenuDel ' Cria a nova barra de comando Set cmdBar = CommandBars.Add(CMDBARNOME, msoBarFloating) cmdBar.Width = 150 ' Adiciona o menu dropdown Set btnDropDown = cmdBar.Controls.Add(Type:=msoControlDropdown) With btnDropDown ' A Tag (etiqueta) é utilizada na classe .Tag = "Lista" ' Ação a ser executada .OnAction = "selPlanilha" .Width = 150 End With ' Adiciona um botão de ajuda a barra de comando Set btn = cmdBar.Controls.Add(Type:=msoControlButton) With btn .Caption = "Ajuda" .OnAction = "ajuda" .Style = msoButtonIconAndCaption .FaceId = 984 End With 'Define os eventos do aplicativo que serão monitorados Set appExcel.appXL = Application 'Define a combobox que deve ser monitorada pela Classe1 cboBox.setDrop btnDropDown With cmdBar .Visible = True .Protection = msoBarNoChangeDock + msoBarNoResize End With End Sub
	Sub wsMenuDel() On Error Resume Next Application.CommandBars(CMDBARNOME).Delete End Sub
	Sub selPlanilha() Dim btnDropDown As Object On Error Resume Next Set btnDropDown = CommandBars.FindControl( _ Type:=msoControlDropdown, Tag:="Lista") ActiveWorkbook.Sheets(btnDropDown.Text).Activate End Sub
	Sub mnuContas() Dim cmdBar As CommandBar Dim menu As CommandBarPopup Dim btn As CommandBarButton Set cmdBar = CommandBars(CMDBARNOME) ' Remove os botões antes de adicionar o novo On Error Resume Next cmdBar.Controls(MENUCONTA).Delete cmdBar.Controls(MENUCLIENTES).Delete cmdBar.Controls(MENUFORNECEDORES).Delete ' Adiciona o botão antes do botão "Ajuda" Set menu = cmdBar.Controls.Add(Type:=msoControlPopup, BEFORE:=2) menu.Caption = MENUCONTA Set btn = menu.Controls.Add(Type:=msoControlButton) btn.Caption = "Adicionar conta" Set btn = menu.Controls.Add(Type:=msoControlButton) btn.Caption = "Remover conta" End Sub

Sub mnuFornecedores()

Dim cmdBar As CommandBar

Dim menu As CommandBarPopup

Dim btn As CommandBarButton

Set cmdBar = CommandBars(CMDBARNOME)

On Error Resume Next

cmdBar.Controls(MENUCONTA).Delete

cmdBar.Controls(MENUCLIENTES).Delete

cmdBar.Controls(MENUFORNECEDORES).Delete

Set menu = cmdBar.Controls.Add(Type:=msoControlPopup, BEFORE:=2)

menu.Caption = MENUFORNECEDORES

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

btn.Caption = "Registrar Fornecedor"

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

btn.Caption = "Situação cadastral"

End Sub

Sub mnuClientes()

Dim cmdBar As CommandBar

Dim menu As CommandBarPopup

Dim btn As CommandBarButton

Set cmdBar = CommandBars(CMDBARNOME)

On Error Resume Next

cmdBar.Controls(MENUCONTA).Delete

cmdBar.Controls(MENUCLIENTES).Delete

cmdBar.Controls(MENUFORNECEDORES).Delete

Set menu = cmdBar.Controls.Add(Type:=msoControlPopup, BEFORE:=2)

menu.Caption = MENUCLIENTES

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

btn.Caption = "Registrar Cliente"

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

btn.Caption = "Situação pgtos"

End Sub

Sub mnuRemover()

Dim cmdBar As CommandBar

Set cmdBar = CommandBars(CMDBARNOME)

' Remove todos os botões

On Error Resume Next

cmdBar.Controls(MENUCONTA).Delete

cmdBar.Controls(MENUCLIENTES).Delete

cmdBar.Controls(MENUFORNECEDORES).Delete

End Sub

Sub ajuda()

ActiveWorkbook.FollowHyperlink _

"http://www.msofficegurus.com", _

NewWindow:=True, AddHistory:=True

End Sub

As três sub-rotinas para reestruturação do menu podem ser colocadas em uma única rotina, porém, deixarei esta parte como exercício para o leitor. Tudo que o leitor precisa fazer é observar os pontos em comuns nas sub-rotinas e utilizá-los na construção de uma única sub.

Com as sub-rotinas resolvidas, adicione uma classe ao seu projeto. Lembre-se que nos referimos à classe como Classe1 quando declaramos os objetos Application e Combobox. Portanto, este deve ser o nome da classe. Caso queira, utilize outro nome qualquer, mas não esqueça de modificar no módulo.

Abaixo se encontra a seqüência na classe com os comentários para melhor compreensão do desenvolvimento do código:

	'Declara os objetos que serão monitorados Public WithEvents appXL As Application Public WithEvents drop As Office.CommandBarComboBox 'Rotina para monitorar o ativamento de planilha
	Private Sub appXL_SheetActivate(ByVal Sh As Object) Dim btnDropDown As Object Dim ws As Worksheet Dim wb As Workbook Dim i As Long ' Define o objeto wb como sendo a pasta ativa Set wb = ActiveWorkbook ' Se a pasta ativa nao for a "Tópico 13.xls", entao ' sair da rotina If Not wb.Name = "Tópico 13.xls" Then Exit Sub ' Define o objeto btnDropDown como sendo a combobox na ' barra de comando CMDBARNOME. O controle é encontrado ' através da etiqueta (Tag) Set btnDropDown = CommandBars(CMDBARNOME).FindControl _ (Type:=msoControlDropdown, Tag:="Lista") ' Limpa a combobox btnDropDown.Clear ' Para cada planilha na pasta de trabalho For Each ws In Sh.Parent.Sheets ' Adicionar o nome da planilha a combobox btnDropDown.AddItem ws.Name Next ' Define o item ativa na combobox For i = 1 To btnDropDown.ListCount If btnDropDown.List(i) = Sh.Name Then _ btnDropDown.ListIndex = i: Exit For Next ' Chama a rotina de mudança na combobox ' Observe que nao somente estamos monitorando o aplicativo ' como também a combobox Call drop_Change(btnDropDown) End Sub
	Private Sub appXL_WorkbookActivate(ByVal wb As Workbook) Dim btnDropDown As Object Set btnDropDown = CommandBars(CMDBARNOME).FindControl _ (Type:=msoControlDropdown, Tag:="Lista") ' Se a pasta de trabalho atual for a "Tópico 13.xls" If wb.Name = "Tópico 13.xls" Then ' Entao, ativar a combobox e btnDropDown.Enabled = True ' chamar a rotina de ativacao de planilha appXL_SheetActivate wb.ActiveSheet Else: ' Se nao for, entao remover o menu Call mnuRemover ' E desabilitar a combobox btnDropDown.Enabled = False End If End Sub
	Public Sub setDrop(box As Office.CommandBarComboBox) ' Define a combobox como sendo a "box" ' Esta ativacao é feita quando a rotina de criacao ' do menu é rodada. A linha no módulo que faz isso é ' "cboBox.setDrop btnDropDown" Set drop = box End Sub