Results 1 to 2 of 2

How to Lock VBA Project of a Workbook created by Macro through VBA

Threaded View

  1. #1
    Registered User
    Join Date
    04-14-2019
    Location
    Singapore
    MS-Off Ver
    Office 365
    Posts
    20

    How to Lock VBA Project of a Workbook created by Macro through VBA

    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.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] I opened another workbook from this site and it created a phantom workbook in my project
    By carlmon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-14-2022, 09:39 AM
  2. [SOLVED] Run macro from newly created workbook
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-15-2018, 08:54 AM
  3. Run a macro with a command button in a new workbook that is created from another workbook.
    By seattletimebandit in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-08-2018, 11:58 PM
  4. Lock a project using a macro
    By mother earth in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-29-2007, 07:18 AM
  5. accessing file created in different macro within the same project
    By Mbrolass in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-25-2007, 07:34 AM
  6. Lock single Module ONLY in workbook; without locking VBA project?
    By Kootenay in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-21-2006, 12:05 AM
  7. Macro run each time a new workbook created
    By cmorton89 in forum Excel General
    Replies: 0
    Last Post: 03-08-2006, 03:58 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1