Stefany Tortato
18-10-2016 19:31:24
Ao executar o workbook abaixo, todas as planilhas que estiverem abertas sofrem as mesmas mudanças... existe alguma forma de bloqueio para que execute somente nessa planilha ???
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Módulo1.AdministradorLiberar
travado = True
Módulo1.EsconderCabecalho
Módulo1.ObterNome
Módulo1.FiltrarContrato
Módulo1.ObterDadosContrato
Módulo1.RemoverToolbars
Módulo1.AdministradorBloquear
Application.ScreenUpdating = True
End Sub
o módulo 1 é composto por:
Sub ObterNome()
Sheets("Identificação").Select
Range("B8").Select
ActiveCell.FormulaR1C1 = Replace(ThisWorkbook.Name, ".xlsm", "")
End Sub
Sub FiltrarContrato()
Sheets("Identificação").Select
Range("B8").Select
Selection.Copy
Worksheets("Gerenciamento Contratos").Visible = True
Sheets("Gerenciamento Contratos").Select
ActiveSheet.ListObjects("Tabela_Gerenciamento_Contratos_1").Range.AutoFilter _
Field:=6, Criteria1:=Replace(ThisWorkbook.Name, ".xlsm", "")
Worksheets("Gerenciamento Contratos").Visible = False
End Sub
Sub ObterDadosContrato()
Worksheets("Gerenciamento Contratos").Visible = True
Worksheets("Contrato").Visible = True
Sheets("Gerenciamento Contratos").Select
Range("A1:V999").Select
Selection.Copy
Worksheets("Contrato").Visible = True
Sheets("Contrato").Select
Range("A1:V999").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("Gerenciamento Contratos").Visible = False
Worksheets("Contrato").Visible = False
Sheets("Identificação").Select
End Sub
Sub LimparDadosContrato()
Worksheets("Contrato").Visible = True
Sheets("Contrato").Select
Rows("2:2").Select
Selection.ClearContents
Sheets("Contrato").Select
Worksheets("Contrato").Visible = False
Sheets("Identificação").Select
Range("B8").Select
ActiveCell.FormulaR1C1 = "-"
End Sub
Sub RemoverToolbars()
On Error Resume Next
With Application
.DisplayFullScreen = True
.CommandBars("Full Screen").Visible = False
.CommandBars("MyToolbar").Enabled = False
.CommandBars("MyToolbar").Visible = False
.CommandBars("Worksheet Menu Bar").Enabled = False
End With
On Error GoTo 0
End Sub
Sub AdicionarToolbars()
On Error Resume Next
With Application
.DisplayFullScreen = False
.CommandBars("MyToolbar").Enabled = False
.CommandBars("Worksheet Menu Bar").Enabled = True
End With
On Error GoTo 0
End Sub
Sub Salvar()
Application.ScreenUpdating = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub
Sub Sair()
Módulo1.AdicionarToolbars
ActiveWorkbook.Close SaveChanges:=False
End Sub
Sub EsconderCabecalho()
Dim wrkbk As Workbook
Dim wrksh As Worksheet
Dim prev As Window
'VERIFICAR ESTA PARTE DO CÓDIGO
Set prev = ActiveWindow
For Each wrkbk In Workbooks
For Each wrksh In wrkbk.Worksheets
wrksh.Activate
ActiveWindow.DisplayHeadings = False
Next wrksh
Next wrkbk
prev.Activate
End Sub
Sub ExibirCabecalho()
Dim wrkbk As Workbook
Dim wrksh As Worksheet
Dim prev As Window
'VERIFICAR ESTA PARTE DO CÓDIGO
Set prev = ActiveWindow
For Each wrkbk In Workbooks
For Each wrksh In wrkbk.Worksheets
wrksh.Activate
ActiveWindow.DisplayHeadings = True
Next wrksh
Next wrkbk
prev.Activate
End Sub
Sub VerificarAdministrador()
UserForm1.Show
End Sub
Sub AdministradorLiberar()
Módulo1.AdicionarToolbars
Módulo1.Liberar
Módulo1.ExibirCabecalho
Sheets("Controle").Select
Application.OnKey "{ESC}"
End Sub
Sub AdministradorBloquear()
Módulo1.RemoverToolbars
Módulo1.Bloquear
Módulo1.EsconderCabecalho
Sheets("Controle").Select
Application.OnKey "{ESC}", "NoChange"
End Sub
Sub Bloquear()
Dim wrkbk As Workbook
Dim wrksh As Worksheet
Dim prev As Window
Set prev = ActiveWindow
'VERIFICAR ESTA PARTE DO CÓDIGO
For Each wrkbk In Workbooks
For Each wrksh In wrkbk.Worksheets
wrksh.Activate
wrksh.Protect
Next wrksh
Next wrkbk
prev.Activate
Sheets("Controle").Select
EstaPasta_de_trabalho.travado = True
End Sub
Sub Liberar()
Dim wrkbk As Workbook
Dim wrksh As Worksheet
Dim prev As Window
Set prev = ActiveWindow
For Each wrkbk In Workbooks
For Each wrksh In wrkbk.Worksheets
wrksh.Activate
wrksh.Unprotect
Next wrksh
Next wrkbk
prev.Activate
Sheets("Controle").Select
EstaPasta_de_trabalho.travado = False
End Sub
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Módulo1.AdministradorLiberar
travado = True
Módulo1.EsconderCabecalho
Módulo1.ObterNome
Módulo1.FiltrarContrato
Módulo1.ObterDadosContrato
Módulo1.RemoverToolbars
Módulo1.AdministradorBloquear
Application.ScreenUpdating = True
End Sub
o módulo 1 é composto por:
Sub ObterNome()
Sheets("Identificação").Select
Range("B8").Select
ActiveCell.FormulaR1C1 = Replace(ThisWorkbook.Name, ".xlsm", "")
End Sub
Sub FiltrarContrato()
Sheets("Identificação").Select
Range("B8").Select
Selection.Copy
Worksheets("Gerenciamento Contratos").Visible = True
Sheets("Gerenciamento Contratos").Select
ActiveSheet.ListObjects("Tabela_Gerenciamento_Contratos_1").Range.AutoFilter _
Field:=6, Criteria1:=Replace(ThisWorkbook.Name, ".xlsm", "")
Worksheets("Gerenciamento Contratos").Visible = False
End Sub
Sub ObterDadosContrato()
Worksheets("Gerenciamento Contratos").Visible = True
Worksheets("Contrato").Visible = True
Sheets("Gerenciamento Contratos").Select
Range("A1:V999").Select
Selection.Copy
Worksheets("Contrato").Visible = True
Sheets("Contrato").Select
Range("A1:V999").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("Gerenciamento Contratos").Visible = False
Worksheets("Contrato").Visible = False
Sheets("Identificação").Select
End Sub
Sub LimparDadosContrato()
Worksheets("Contrato").Visible = True
Sheets("Contrato").Select
Rows("2:2").Select
Selection.ClearContents
Sheets("Contrato").Select
Worksheets("Contrato").Visible = False
Sheets("Identificação").Select
Range("B8").Select
ActiveCell.FormulaR1C1 = "-"
End Sub
Sub RemoverToolbars()
On Error Resume Next
With Application
.DisplayFullScreen = True
.CommandBars("Full Screen").Visible = False
.CommandBars("MyToolbar").Enabled = False
.CommandBars("MyToolbar").Visible = False
.CommandBars("Worksheet Menu Bar").Enabled = False
End With
On Error GoTo 0
End Sub
Sub AdicionarToolbars()
On Error Resume Next
With Application
.DisplayFullScreen = False
.CommandBars("MyToolbar").Enabled = False
.CommandBars("Worksheet Menu Bar").Enabled = True
End With
On Error GoTo 0
End Sub
Sub Salvar()
Application.ScreenUpdating = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub
Sub Sair()
Módulo1.AdicionarToolbars
ActiveWorkbook.Close SaveChanges:=False
End Sub
Sub EsconderCabecalho()
Dim wrkbk As Workbook
Dim wrksh As Worksheet
Dim prev As Window
'VERIFICAR ESTA PARTE DO CÓDIGO
Set prev = ActiveWindow
For Each wrkbk In Workbooks
For Each wrksh In wrkbk.Worksheets
wrksh.Activate
ActiveWindow.DisplayHeadings = False
Next wrksh
Next wrkbk
prev.Activate
End Sub
Sub ExibirCabecalho()
Dim wrkbk As Workbook
Dim wrksh As Worksheet
Dim prev As Window
'VERIFICAR ESTA PARTE DO CÓDIGO
Set prev = ActiveWindow
For Each wrkbk In Workbooks
For Each wrksh In wrkbk.Worksheets
wrksh.Activate
ActiveWindow.DisplayHeadings = True
Next wrksh
Next wrkbk
prev.Activate
End Sub
Sub VerificarAdministrador()
UserForm1.Show
End Sub
Sub AdministradorLiberar()
Módulo1.AdicionarToolbars
Módulo1.Liberar
Módulo1.ExibirCabecalho
Sheets("Controle").Select
Application.OnKey "{ESC}"
End Sub
Sub AdministradorBloquear()
Módulo1.RemoverToolbars
Módulo1.Bloquear
Módulo1.EsconderCabecalho
Sheets("Controle").Select
Application.OnKey "{ESC}", "NoChange"
End Sub
Sub Bloquear()
Dim wrkbk As Workbook
Dim wrksh As Worksheet
Dim prev As Window
Set prev = ActiveWindow
'VERIFICAR ESTA PARTE DO CÓDIGO
For Each wrkbk In Workbooks
For Each wrksh In wrkbk.Worksheets
wrksh.Activate
wrksh.Protect
Next wrksh
Next wrkbk
prev.Activate
Sheets("Controle").Select
EstaPasta_de_trabalho.travado = True
End Sub
Sub Liberar()
Dim wrkbk As Workbook
Dim wrksh As Worksheet
Dim prev As Window
Set prev = ActiveWindow
For Each wrkbk In Workbooks
For Each wrksh In wrkbk.Worksheets
wrksh.Activate
wrksh.Unprotect
Next wrksh
Next wrkbk
prev.Activate
Sheets("Controle").Select
EstaPasta_de_trabalho.travado = False
End Sub