Neste artigo iremos trabalhar um controle activeX do MSExcel, aplicado ao desenvolvimento de controle interno. Este recurso potencializará a efetividade do controle, seu manuseio, forma de apresentação e redução do trabalho manual. Iremos inserir um seletor (combobox) para mudar parâmetros de um relatório já pronto, a cada vez que mudarmos os valor do seletor o relatório irá carregar novos valores, baseados no banco de dados. Será necessário a utilização do MSExcel 2007 ou superior. Mãos a obra!
1º Passo - Habilitar a guia Desenvolvedor
Abra uma planilha em branco (menu iniciar > Todos os Programas>Microsoft Office> Microsoft Excel).
Clique no Botão Office (canto superior esquerdo - simbolo do Office), clique em "opções do Excel", na opção "Mais Usados", dê um clique em "Mostrar guia Desenvolvedor na Faixa de Opções", dê Ok e volte a planilha. Note que uma guia foi inserida na faixa de opções no Excel.
2º Passo - Banco de Dados
Na guia "Plan1", iremos atribuir o banco de dados. Sera o lugar onde iremos alocar o histórico de informações. Seguiremos um exemplo fictício, iremos controlar as despesas da Futuro S.A. Esta etapa será importante pois irá conter toda a base de informação de nosso trabalho, por isso tome cuidado ao montar o layout do banco.
O layout de nosso banco esta disposto abaixo:
Data | Centro de Custo | Tipo de Despesa | Valor |
5/1/2011 | Sede | Despesa de Pessoal | 15.250,00 |
4/2/2011 | Sede | Despesa de Pessoal | 14.180,00 |
5/1/2011 | Depto Qualidade | Despesa de Pessoal | 23.720,00 |
4/2/2011 | Depto Qualidade | Despesa de Pessoal | 22.370,00 |
5/1/2011 | Manutenção | Despesa de Pessoal | 8.950,00 |
4/2/2011 | Manutenção | Despesa de Pessoal | 9.350,00 |
5/1/2011 | Montagem | Despesa de Pessoal | 12.300,00 |
4/2/2011 | Montagem | Despesa de Pessoal | 9.750,00 |
5/1/2011 | RH | Despesa de Pessoal | 7.500,00 |
4/2/2011 | RH | Despesa de Pessoal | 7.650,00 |
3º Passo - Máscara do Relatório
Na guia "Plan2", iremos alocar a máscara do relatório, será sua forma de apresentação. Seu relatório pode conter layouts sofisticados e bem elaborados, iremos seguir um exemplo simplificado:
Relatório de Despesas - Futuro S.A. | ||
Departamento | R$ | A.V.% |
Depto Qualidade | 0% | |
Manutenção | 0% | |
Montagem | 0% | |
RH | 0% | |
Sede | 0% | |
Total | - | 0% |
4º Passo - Parâmetros
Na guia "Plan3" iremos introduzir os parâmetos do relatório. Um relatório pode ter quantos parâmetros se desejar, contudo será necessário mais controles e sofisticação na montagem da planilha. Para o nosso exemplo utilizaremos apenas um parâmetro, que será a Mês. Em Plan3, na célula A1, iremos inserir o texto "Parâmetro", em A2 iremos atribuir o valor 2, que fará referência ao mês 02 (fevereiro), veja como ficou:
5º Passo - Fórmulas
Voltemos a guia "Plan1", iremos trabalhar a formula SomaSes, não iremos nos aprofundar nos conceitos desta fórmula, em outro post faremos isto. Veja abaixo estrutura da fórmula.
=SOMASES(Valor;Centro_Custo;Plan2!$A4;Data;Plan3!$A2)
Parâmetro da data está no trecho "Plan3!$A2", desta forma toda vez que mudarmos o valor do paâmetro em na guia "Plan3" na célula A2, o valor da fórmula também irá mudar, sendo atualizado pelo novo parâmetro.
6º Passos - Inserir o controle Combobox
Na guia "Plan2", selecione a aba "Desenvolvedor", clique em Inserir, em controles ActiveX, clique em caixa de combinação.
Adicione o controle na planilha,
Vamos a guia "Plan3", na célula C1, digite "Lista de Valores", para C2 e C3 faça conforme abaixo:
Estes serão os valores que alimentaram o nosso combobox, para isto clique com o botão direito sobre o combo, e selecione propriedades. Irá aparecer a janela de propriedades do combobox, no campo "ListFillRange" digite o seguinte valor: "Plan3!C2:C3". Desta forma estama parametrizando o combo para mostrar os valores contidos na figura acima.
7º Passo - Inserir código para o evento Change do combobox
Clique duas vez sobre o combo, o Excel irá abrir o editor do VBA, digite o código abaixo:
Private Sub ComboBox1_Change()
Plan3.Cells(2, 1).Value = ComboBox1
ComboBox1 = CDate(ComboBox1)
End Sub
Feche o editor do VBA e volte para a guia "Plan2". Na aba "Desenvolvedor" clique em "Modo Desing", para desfazer sua seleção. Volte para o combo e clique sobre ele, veja que irão abrir as seguintes opções:
Ao mudar os valores do combo o relatório irá buscar as informações correspondentes ao novo parâmetro. Assim o relatório se torna dinâmico, basta alimentar o banco com as novas informações e adicionar os novos parâmetros. Isto por certo ira facilitar o dia-a-dia de quem precisa apresentar relatórios onde as informações mudão em apenas alguns aspectos como datas.
Espero que o artigo seja útil, por favor, envie sua sugestão, crítica ou comentário. Solicite um modelo pelo e-mail agabriel.rocha@gmail.com . Em breve, sairá outro artigo relacionado a Excel. Até mais.
Realmente sensacional, até eu que não entendo nada de Excel consegui fazer. Parabéns, posta mais ai porque agora me entusiasmei.
ResponderExcluir