Enter a password in the VBA project via module or SendKeys
Hello, I have a matrix sheet that will enter thousands of other smaller sheets, will save in another extension, put a code inside this and wanted it to block the VBA project of these smaller sheets, I'm trying to use SendKeys for this, but this inefficient.
Sub entrando_no_padrão()
Application.DisplayAlerts = False
Dim fld As Object
Dim fld2 As Object
Dim fld3 As Object
Dim fld4 As Object
Dim fld5 As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set fso2 = CreateObject("Scripting.FileSystemObject")
Set fso3 = CreateObject("Scripting.FileSystemObject")
Set fso4 = CreateObject("Scripting.FileSystemObject")
Set fso5 = CreateObject("Scripting.FileSystemObject")
Dim ver_num As Integer
ver_num = 0
Set fld = fso.GetFolder("C:\1")
n = 1
y = 1
For Each fld In fld.SubFolders
If fld <> "ESSE_NOME_NÃO_ENTRA" Then
Set fld2 = fso2.GetFolder("C:\1\" & fld.Name)
For Each fld2 In fld2.SubFolders
If Len(Dir("C:\1\" & fld.Name & "\" & fld2.Name & "\PCP- Planos de controle", vbDirectory) & "") > 0 Then
Set fld3 = fso3.GetFolder("C:\1\" & fld.Name & "\" & fld2.Name & "\PCP- Planos de controle")
For Each fld3 In fld3.Files
Call PADRONIZAR(fld.Name, fld2.Name, fld3.Name)
SendKeys "%f" & "p" & "^{TAB}" & "{+}" & "{TAB}" & "34670920" & "{TAB}" & "34670920" & "{TAB}" & "~" & "%{F4}"
Next fld3
End If
If Len(Dir("C:\1\" & fld.Name & "\" & fld2.Name & "\PEP - Plano de embalagem", vbDirectory) & "") > 0 Then
Set fld4 = fso4.GetFolder("C:\1\" & fld.Name & "\" & fld2.Name & "\PEP - Plano de embalagem")
For Each fld4 In fld4.Files
Call PADRONIZAR(fld.Name, fld2.Name, fld4.Name)
SendKeys "%f" & "p" & "^{TAB}" & "{+}" & "{TAB}" & "34670920" & "{TAB}" & "34670920" & "{TAB}" & "~" & "%{F4}"
Next fld4
End If
If Len(Dir("C:\1\" & fld.Name & "\" & fld2.Name & "\FIT - Ficha de Instrução de Trabalho", vbDirectory) & "") > 0 Then
Set fld5 = fso5.GetFolder("C:\1\" & fld.Name & "\" & fld2.Name & "\FIT - Ficha de Instrução de Trabalho")
For Each fld5 In fld5.Files
Call PADRONIZAR(fld.Name, fld2.Name, fld5.Name)
SendKeys "%f" & "p" & "^{TAB}" & "{+}" & "{TAB}" & "34670920" & "{TAB}" & "34670920" & "{TAB}" & "~" & "%{F4}"
Next fld5
End If
Next fld2
End If
Next fld
If x <> x Then
final:
Open "\\caminha\para\abrir\um\txt" For Append As #2
Print #2, fld2.Path
Close #2
End If
Application.DisplayAlerts = True
End Sub
That's it, for now.
2 answers
Just enter the VBA editor, and in the project tree, set a password for it.
Follows the step by step (done in Word but it is suitable for the whole Office):
Step 1
Step 2
Edition 1:
According to your recent comment, you would like to block VBA code from spreadsheets generated by that initial.
It turns out that this is not possible from a usual way, since VBAProject does not expose the password to be set via code.
However, there are some ways to do this, such as using SendKeys (not recommended):
Sub AddNewPlan()
Dim NewPlan As Workbook
Set NewPlan = Workbooks.Add
With NewPlan
.Title = "New Plan"
Call AddSampleCode(NewPlan)
Call ProtectVBProject(NewPlan, "abc123")
.SaveAs "C:\NewPlan.xlsm", xlOpenXMLWorkbookMacroEnabled
.Close
End With
End Sub
Private Sub ProtectVBProject(WB As Workbook, ByVal Password As String)
' Ativa a planilha a ser bloqueada
WB.Activate
' Envia o comando para abrir o VBA
SendKeys "%{F11}", True
' Abre a janela de proteção do projeto VBA
WB.VBProject.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
' Envia os comandos para digitar a senha e confirmar
SendKeys "+{TAB}{RIGHT}%V{+}{TAB} {TAB}" & Password & "{TAB}" & Password & "~", True
End Sub
Private Sub AddSampleCode(WB As Workbook)
Dim xPro As VBIDE.VBProject
Dim xCom As VBIDE.VBComponent
Dim xMod As VBIDE.CodeModule
Dim xLine As Long
With WB
Set xPro = .VBProject
Set xCom = xPro.VBComponents.Add(vbext_ct_StdModule)
xCom.Name = "ModTeste" ' Adiciona um novo módulo padrão a planilha
Set xMod = xCom.CodeModule
'Adiciona o código do novo modulo
With xMod
.InsertLines 2, "Sub Teste()"
.InsertLines 3, "Msgbox ""teste"""
.InsertLines 4, "End Sub"
End With
End With
End Sub
This method was discussed in this topic: http://www.ozgrid.com/forum/showthread.php?t=13006&p=65776#post65776
However I couldn't get it to work properly for me, so I modified it a bit because I realized it's required that there be at least one standard module for VBA blocking to occur, so I included creating a test module in the example. the Microsoft Visual Basic for Applications Extensibility 5.3 reference is required
The other ways would be to create a spreadsheet template with the VBA code defined and populate that spreadsheet with data or via Windows API (which would be more complex and I don't have any examples now).
Anyway, if the user really wants to see the Code VBA there are ways to get it, even with a password, for example using OpenOffice to open your file.
Hello, I have a system that uses spreadsheets as a database, and when I want to access the base, I have to enter a password, if it helps you the code is this:
Private Sub btnOk_Click()
If txtSenha.Text = "" Then
MsgBox "Digite a senha para acessar o Arquivo", vbCritical
ElseIf txtSenha <> "vba" Then 'senha
MsgBox "Digite a senha corretamente para acessar o Arquivo", vbCritical
Else
Application.Visible = True
Unload Me
UserForm6.Hide 'aqui é a tela principal do meu sistema fica invisivel
Exit Sub
End If
End Sub
Only create a userform with a TextBox and a button.