Excel 2016 com VBAAula 5

Prof. Guilherme Nonino Rosa

Apresentação:

Prof. Guilherme Nonino Rosa- Graduado em Ciências da Computação pela Unifran – Universidade

de Franca no ano de 2000.

- Pós-Graduado em Tecnologia da Informação aplicada aos Negócios pela Unip-Universidade Paulista no ano de 2011.

- Licenciado em Informática pela Fatec – Faculdade de Tecnologia de Franca no ano de 2011.

- Docente do Senac – Ribeirão Preto desde fevereiro/2012

- Docente do Centro de Educação Tecnológica Paula Souza, nas Etecsde Ituverava e Orlândia de fevereiro/2010

- Docente na Faculdade Anhanguera – Ribeirão Preto desde de fevereiro/2013

Contatos:

Prof. Guilherme Nonino Rosa

guilherme.nrosa@sp.senac.br

OBJETIVOS DO CURSO:

Capacitar o aluno a desenvolver macros, formulários e funções personalizadas utilizando recursos do Excel e do VBA, a fim de otimizar o

processamento, armazenamento e apresentação de informações nas planilhas do Excel.

NORMAS DO LABORATÓRIO

• Proibido ingerir bebidas e alimentos dentro do laboratório

• Evitar atender o celular durantes as aulas.• Evite faltar, pois não há como voltar na aula anterior.

CRITÉRIOS DE AVALIAÇÃO

A avaliação da aprendizagem será contínua e cumulativa, priorizando aspectos qualitativos

relacionados com o processo de aprendizagem e o desenvolvimento do aluno observado durante

a realização das atividades propostas, individualmente e/ou em grupo.

APROVAÇÃO/FREQÜÊNCIA

FREQÜÊNCIA = 75%(7,5 horas)

ENTREGA DOS CERTIFICADOS :

NO ÚLTIMO DIA DO CURSO OU RETIRAR NA SECRETARIA (1º ANDAR)

Usuário:Senha:

1. Cria macro de gravação; 2. Diferencia referência relativa e absoluta; 3. Salva arquivo de Excel com macros; 4. Configura recursos de segurança de macros; 5. Identifica os elementos da interface do VBE; 6. Cria macro de programação; 7. Consulta documentação da Microsoft ou equivalente, quando necessário; 8. Utiliza recursos de tratamento de erro, quando necessário; 9. Utiliza caixa de entrada e caixa de mensagem; 10. Utiliza variáveis e constantes; 11. Utiliza estrutura de desvio condicional; 12. Utiliza estruturas de repetição; 13. Cria funções personalizadas; 14. Cria formulários personalizados; 15. Integra diferentes recursos de programação do VBA em um único projeto.

Macros e VBA - ExcelAula 5 – Parte 01

- Rotina para alterar registros - Função IF- Busca Registro- Gravar Registro Alterado

Private Sub btnbuscar_Click()Application.ScreenUpdating = False

Dim planilha_atual As WorksheetDim vNome As StringDim planilha_principal As Worksheet

Set planilha_atual = Sheets("Clientes")Set planilha_principal = Sheets("Cadastro")

vNome = Sheets("Cadastro").cmbalterar.Valueplanilha_atual.Selectplanilha_atual.Range("A2").Select

Do While ActiveCell.Value <> ""'testa para ver se a célula ativa que possui o valor procuradoIf ActiveCell.Value = vNome Then

planilha_principal.Range("B27").Value = ActiveCell.Valueplanilha_principal.Range("C27").Value = ActiveCell.Offset(0, 1).Valueplanilha_principal.Range("D27").Value = ActiveCell.Offset(0, 2).Valueplanilha_principal.Range("E27").Value = ActiveCell.Offset(0, 3).Valueplanilha_principal.Range("F27").Value = ActiveCell.Offset(0, 4).Valueplanilha_principal.Range("G27").Value = ActiveCell.Offset(0, 5).Value

End IfActiveCell.Offset(1, 0).Select

Loopplanilha_principal.Selectplanilha_principal.Range("B27").Select

Application.ScreenUpdating = TrueEnd Sub

Private Sub btngravaraltera_Click()

Application.ScreenUpdating = False

Dim planilha_atual As WorksheetDim vNome As StringDim planilha_principal As Worksheet

Set planilha_atual = Sheets("Clientes")Set planilha_principal = Sheets("Cadastro")vNome = Sheets("Cadastro").cmbalterar.Value

planilha_atual.Selectplanilha_atual.Range("A2").Select

Do While ActiveCell.Value <> ""

'testa para ver se a célula ativa que possui o valor procuradoIf ActiveCell.Value = vNome Then

ActiveCell.Value = planilha_principal.Range("B27").ValueActiveCell.Offset(0, 1).Value = planilha_principal.Range("C27").ValueActiveCell.Offset(0, 2).Value = planilha_principal.Range("D27").ValueActiveCell.Offset(0, 3).Value = planilha_principal.Range("E27").ValueActiveCell.Offset(0, 4).Value = planilha_principal.Range("F27").ValueActiveCell.Offset(0, 5).Value = planilha_principal.Range("G27").Value

MsgBox "Dados alterados com sucesso", vbOKOnlyAtualizar_Comboplanilha_principal.Selectplanilha_principal.Range("B27").Select

Exit Sub

End IfActiveCell.Offset(1, 0).Select

LoopApplication.ScreenUpdating = True

End Sub

Macros e VBA - ExcelAula 5 – Parte 02

- Apagar dados em todas as planilhas- UsedRange

- Ocultar todas as planilhas- Visible = True

- Reexibir todas as planilhas ocultadas- Visible = False

- Atualizar todos os Combobox.

Código de Atualização(Inserção de registro) do Combobox.

Sub AtualizarCombo()Application.ScreenUpdating = FalseDim planilha_atual As WorksheetSet planilha_atual = Sheets("Controle")planilha_atual.Selectplanilha_atual.Range("A1").Select

'limpa conteúdo da caixa de seleção.Sheets("Controle").cmbapagardados.ClearSheets("Controle").cmbbloquear.ClearSheets("controle").cmbexibir.Clear

For Each vplan In WorksheetsIf vplan.Name <> "Controle" Then

Sheets("Controle").cmbapagardados.AddItem vplan.NameSheets("Controle").cmbbloquear.AddItem vplan.NameSheets("controle").cmbexibir.AddItem vplan.Name

End IfNextSheets("Controle").cmbapagardados.AddItem "Todas"Sheets("Controle").cmbbloquear.AddItem "Todas"Sheets("Controle").cmbexibir.AddItem "Todas"

Application.ScreenUpdating = True

End Sub

Código de Atualização(exclusão de registro) do Combobox.

Sub atualiza_excluir()Application.ScreenUpdating = False

Dim planilha_atual As WorksheetDim planilha_excluir As Stringplanilha_excluir = ActiveSheet.NameSet planilha_atual = Sheets("Controle")planilha_atual.Selectplanilha_atual.Range("A1").Select

'limpa conteúdo da caixa de seleção.Sheets("Controle").cmbapagardados.ClearSheets("Controle").cmbbloquear.ClearSheets("controle").cmbexibir.Clear

For Each vplan In WorksheetsIf vplan.Name <> "Controle" And vplan.Name <> planilha_excluir Then

Sheets("Controle").cmbapagardados.AddItem vplan.NameSheets("Controle").cmbbloquear.AddItem vplan.NameSheets("controle").cmbexibir.AddItem vplan.Name

End IfNextSheets("Controle").cmbapagardados.AddItem "Todas"Sheets("Controle").cmbbloquear.AddItem "Todas"Sheets("Controle").cmbexibir.AddItem "Todas"

Application.ScreenUpdating = TrueEnd Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean)Dim vPlanAtual As StringvPlanAtual = ActiveSheet.NameAtualizarComboSheets(vPlanAtual).Select

End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)Dim vPlanAtual As StringvPlanAtual = ActiveSheet.NameAtualizarComboSheets(vPlanAtual).Select

End Sub

Private Sub Workbook_Open()AtualizarCombo

End Sub

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)atualiza_excluir

End Sub

Eventos para o combobox.

Private Sub btnApagardados_Click()Dim planilha_atual As WorksheetDim planilha_excluir As WorksheetDim planilha_selecionada As String

Set planilha_atual = Sheets("Controle")planilha_selecionada = cmbapagardados.Value

If planilha_selecionada = "Todas" ThenFor Each vplan In WorksheetsIf vplan.Name <> "Controle" Thenvplan.UsedRange.ClearContentsEnd If

NextMsgBox "Dados apagados....", vbInformationcmbapagardados.ListIndex = 0Else

Set planilha_excluir = Sheets(cmbapagardados.Value)If planilha_excluir.Name <> "Controle" Then

planilha_excluir.UsedRange.ClearContentsMsgBox "Dados apagados....", vbInformationcmbapagardados.ListIndex = 0

End IfEnd If

End Sub

Botão apagar dados.

Private Sub btnbloquear_Click()Application.ScreenUpdating = False

Dim vCont As IntegerDim vSenha As StringDim planilhabloquear As Worksheet

vCont = Sheets.Count - 1vSenha = InputBox("Crie a senha", "Senha")

Dim planilha_selecionada As Stringplanilha_selecionada = cmbbloquear.Value

If planilha_selecionada = "Todas" Then

'each trata de objetos - no caso sheets(planilhas)For Each Planilha In WorksheetsIf Planilha.Name <> "Controle" Then

Planilha.Protect Password:=vSenhacmbbloquear.ListIndex = 0

End IfNextMsgBox "Todas as " & vCont & " planilhas foram bloqueadas", vbOKOnly, "Bloqueio"

ElseSet planilhabloquear = Sheets(cmbbloquear.Value)If planilhabloquear.Name <> "Controle" Then

planilhabloquear.Protect Password:=vSenhaMsgBox "A planilha " & cmbbloquear.Value & "foi bloqueada com sucesso!!! "cmbbloquear.ListIndex = 0End If

End IfApplication.ScreenUpdating = False

End Sub

Botão proteger planilha

Private Sub btndesbloquear_Click()Application.ScreenUpdating = False

Dim vCont As IntegerDim vSenha As StringDim planilhadesbloquear As WorksheetvCont = Sheets.Count - 1If cmbbloquear.Value <> "" Then

vSenha = InputBox("Digite sua senha", "Senha")planilha_selecionada = cmbbloquear.Value

If planilha_selecionada = "Todas" Then'each trata de objetos - no caso sheets(planilhas)

For Each Planilha In SheetsIf Planilha.Name <> "Controle" Then

Planilha.Unprotect Password:=vSenhaEnd If

NextMsgBox "Todas as " & vCont & " planilhas foram desbloqueadas", vbOKOnly, "Desbloqueio"

ElseSet planilhadesbloquear = Sheets(cmbbloquear.Value)

planilhadesbloquear.Unprotect Password:=vSenhaMsgBox "A planilha " & cmbbloquear.Value & " foi desbloqueada com sucesso!!! "

End IfElse

MsgBox "Selecione a planilha a ser desbloqueada"btndesbloquear.TakeFocusOnClick = "True"

End IfApplication.ScreenUpdating = True

End Sub

Botão desproteger planilha

Private Sub btnExibir_Click()Application.ScreenUpdating = False

Dim vCont As IntegerDim planilhaexibir As WorksheetDim planilha_selecionada As StringvCont = Sheets.Count - 1If cmbexibir.Value <> "" Then

planilha_selecionada = cmbexibir.Value

If planilha_selecionada = "Todas" Then

'each trata de objetos - no caso sheets(planilhas)For Each Planilha In Sheets

If Planilha.Name <> "Controle" ThenPlanilha.Visible = xlSheetVisible

End If

NextMsgBox "Todas as " & vCont & " planilhas estão visiveis", vbOKOnly, "Desbloqueio"

ElseSet planilhaexibir = Sheets(cmbexibir.Value)

planilhaexibir.Visible = xlSheetVisibleMsgBox "A planilha " & cmbexibir.Value & " está em exibição!!! "

End IfElse

MsgBox "Selecione a planilha a ser exibida"

End IfApplication.ScreenUpdating = True

End Sub

Botão exibir planilha

Private Sub btnOcultar_Click()Application.ScreenUpdating = False

Dim vCont As IntegerDim planilhaocultar As WorksheetDim planilha_selecionada As StringvCont = Sheets.Count - 1If cmbexibir.Value <> "" Then

planilha_selecionada = cmbexibir.Value

If planilha_selecionada = "Todas" Then

'each trata de objetos - no caso sheets(planilhas)For Each Planilha In Sheets

If Planilha.Name <> "Controle" ThenPlanilha.Visible = xlSheetVeryHidden

End If

NextMsgBox "Todas as " & vCont & " planilhas estão visiveis", vbOKOnly, "Desbloqueio"

ElseSet planilhaexibir = Sheets(cmbexibir.Value)

planilhaexibir.Visible = xlSheetVeryHiddenMsgBox "A planilha " & cmbexibir.Value & " está em oculta!!! "

End IfElse

MsgBox "Selecione a planilha a ser oculta"

End IfApplication.ScreenUpdating = True

End Sub

Botão ocultar planilha

Perguntas?