Enter a password in the VBA Project via module or SendKeys

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

Here's the step-by-step (done in Word but works for Office):

Step 1

Step2

Issue1:

Accordingtoyourrecentcomment,youwouldliketoblocktheVBAcodefromthespreadsheetsgeneratedbythisinitial.Itturnsoutthatthisisnotpossibleinausualway,becauseVBAProjectdoesnotexposethepasswordtobedefinedviacode.

Howevertherearesomewaystodothis,suchasusingSendKeys(notrecommended):

SubAddNewPlan()DimNewPlanAsWorkbookSetNewPlan=Workbooks.AddWithNewPlan.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: link

However, I could not make it work correctly for me, so I modified it a bit because I realized that there must be at least one default module for VBA blocking to take place, so I included creating a test module in the example. It is necessary to include the Microsoft Visual Basic for Applications Extensibility 5.3

The other ways would be to create a spreadsheet template with VBA code set and fill that spreadsheet with data or via the Windows API (which would be more complex and I do not own any examples now) . >

Anyway, if the user really wants to see the code VBA there are ways to achieve 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 database, I have to type a password, if it helps, 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

Just create a UserForm with a TextBox and a Button.