VBA to protect new generated workbook

[BombSheels]

Hello Experts,

Just wanted to ask if it's possible to create a VBA that auto-lock the new generated workbook? Here's the scenario, I have a VBA excel (XLSM file) that generated a new workbook, but when I'm trying to protect/lock the new generated workbook, the excel that was locked is the (XLSM file). the new generated workbook is an XLS or XLSX file only.

 

[BombSheels]

Btw, i found a way.

NB.Protect Password:=""
NB.Worksheets("Sheet1").Cells.Locked = True
NB.Worksheets("Sheet1").Range("A1:AC300").Locked = True
NB.Worksheets("Sheet1").Protect Password:="", UserInterfaceOnly:=True

 

[breynolds0431]

Hello. Try this in a normal module. The idea is that you would declare the new workbook. Whether you are doing that by workbooks.add or otherwise can be modified in the Dim newWB: Set newWB = part.

Sub createWB()

Dim pw As String: pw = "Password" 'Change Password to desired password wrapped in quotes

Dim sPath As String: sPath = ThisWorkbook.Path 'Change ThisWorkbook.Path to desired path"

Dim newWB As Workbook: Set newWB = Workbooks.Add 'Adds and declares a new workbook

With newWB
    'Use .Protect to protect workbook structure
        .Protect
    'Open password is the first "pw". Modify password is the second "pw"
        .SaveAs sPath & "\test.xlsx", , pw, pw
    'Close the newWB
        .Close
End With

End Sub

 

[BombSheels]

Hello. Try this in a normal module. The idea is that you would declare the new workbook. Whether you are doing that by workbooks.add or otherwise can be modified in the Dim newWB: Set newWB = part.

Sub createWB()

Dim pw As String: pw = "Password" 'Change Password to desired password wrapped in quotes

Dim sPath As String: sPath = ThisWorkbook.Path 'Change ThisWorkbook.Path to desired path"

Dim newWB As Workbook: Set newWB = Workbooks.Add 'Adds and declares a new workbook

With newWB
    'Use .Protect to protect workbook structure
        .Protect
    'Open password is the first "pw". Modify password is the second "pw"
        .SaveAs sPath & "\test.xlsx", , pw, pw
    'Close the newWB
        .Close
End With

End Sub

Thank you breynolds! this also works for me