Enter a password in the VBA Project via module or Sendkeys

Question

Hello, I have a spreadsheet matrix that will enter in other thousands of smaller spreadsheets, will save in another extension, will put a code inside this and wanted it to block the vba project of these smaller spreadsheets, 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.

Answer 1

Hello, I have a system that uses spreadsheets as a database, and when I want to access the database, I have to enter a password, if I help 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 Button.

Answer 2

Just you enter the VBA editor, and in the project tree, set a password for the same.

Follow the step by step (done in Word but serves for all Office):

Step 1

Step 2

---

Issue 1:

According to your recent comment, you would like to block the VBA code of the worksheets generated by this initial. It turns out that this is not possible in a usual way, because the VBAProject does not expose the password to be set via code.

However, there are some ways to do this, such as using Sendkeys (unsaved):

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 could not make it work properly for me, so I modified it a little because I realized that it is necessary that there is at least one standard module for the VBA lock occurs, so I included the creation of a test module in the example. The reference Microsoft Visual Basic for Applications Extensibility 5.3 needs to be included

The other ways would be to create a spreadsheet template with the VBA code set and fill this worksheet with data or via Windows API (that would be more complex and I have no example now).

Anyway, if the user really wants to see the code VBA there are ways to get even with password, for example using Openoffice to open your file.