Hi
I have been trying for locking the VBA project by sending keys but to no avail. Can you please help where I am going wrong.
following sub as I found in Stakeoverflow is being used where I call below Sub by Sending following parameters:
Sub CopySht()
Application.ScreenUpdating = False
Dim Desdb1 As Workbook
Dim SourceWB As Workbook
Set SourceWB = ThisWorkbook
RunDate = Format(Now, "ddmmyy_hhmmss")
Pathtosave = ThisWorkbook.Path & "\" & "ExecMaster" & RunDate & ".xlsm"
'sourceWB.Sheets("PyScripts").Range("L6").Value = SchFileName
Debug.Print Pathtosave
Set Desdb1 = Workbooks.Add
Desdb1.SaveAs (Pathtosave), FileFormat:=52
'// Copy Sheets in new workbook
With SourceWB.Sheets("My Report")
.Visible = xlSheetVisible
.Copy Before:=Desdb1.Sheets(1)
End With
With SourceWB.Sheets("Welcome")
.Visible = xlSheetVisible
.Copy Before:=Desdb1.Sheets(1)
End With
Desdb1.Activate
Desdb1.Sheets("Welcome").Activate
Call LockVBAProject(Desdb1.Name, "A4321")
Desdb1.Save
Desdb1.Close , True
End Sub
Sub LockVBAProject(nameWorkbookForMarket As String, pw As String)
With Workbooks(nameWorkbookForMarket).Application
If Workbooks(nameWorkbookForMarket).VBProject.Protection = 1 Then Exit Sub
'//execute the controls to lock the project\\
.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
'//activate 'protection'\\
Set vbProj = Workbooks(nameWorkbookForMarket).VBProject
Set Application.VBE.ActiveVBProject = vbProj
If vbProj.Protection = 1 Then Exit Sub
.SendKeys "^{TAB}"
'//CAUTION: this either checks OR UNchecks the\\
'//"Lock Project for Viewing" checkbox, if it's already\\
'//been locked for viewing, then this will UNlock it\\
'//enter password (password is 123 in this example)\\
.SendKeys "{ }"
.SendKeys "{TAB}" & pw
'//confirm password\\
.SendKeys "{TAB}" & pw
'//scroll down to OK key\\
.SendKeys "{TAB}"
'//click OK key\\
.SendKeys "{ENTER}"
'the project is now locked - this takes effect
'the very next time the book's opened...
End With
End Sub
I am expecting that VBA project of my new Macro Workbook created by workkbook.add with few sheets Veryhidden and copying module is locked by sending Keys of my password.
Bookmarks