Discussion Locked

VBA - protect Code in excel vba code.

0 helpful
9 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am creating a new excel workbook in code(vba). I use code & template from an existing worksheet and use save as to save this info into another workbook which is generated on day-to-day basis.
I have to pass the worksheet created to the end users.I need to protect the code modules from the users viewing it.
this is to be done using vba code as I save the new workbook.
thanks
ananth

9 replies

by peyison

You can lock the VBA code:

From the VBA editor select Tools, VBAProject Properties, and choose the Protection tab. This will allow you to lock the code, and enter a password.

I don't think there's a way to do this in code. You can retreive thelock status using:

Application.VBE.ActiveVBProject.Protection

But it is a read only property.

Once you lock the code, however, it will remain locked even if you save the worksheet under a new name. So you could lock the code in the templatefirst and the workbooks created from it will be locked as well.

Hope this helps.

by venu_51

to eliminate the process of setting the protection manually(from IDE) daily I am trying to automate it.

by phpenet

I suggest two methods :1/ If you don't need the code to be in the end user WB, it is better to put it in a dummy WB that will pilot the operations from behind.
2/ If you need the code to be there, then make all your sub Private and cancel or protect the Alt-F11 combination with Application.OnKey, and trap all errors. OTH Patrick.

by phoenix

This code may help you out;Option Explicit

Const BreakIt As String = "%{F11}%TE+{TAB}{RIGHT}%V{+}{TAB}"

Sub Change_VBA_PW()
Dim WB As Workbook
Dim Password As String

Set WB = ActiveWorkbook 'Or your workbook name
Password = "test"
Call SetVBProjectPassword(WB, Password)

End Sub

Sub SetVBProjectPassword(WB As Workbook, ByVal Password As String)
'Needs reference to Visual Basic for Applications Extensibility Library
Dim VBP As VBProject
Dim OpenWin As VBIDE.Window
Dim i As Integer

Set VBP = WB.VBProject

Application.ScreenUpdating = False

' close any code windows to ensure we hit the right project
For Each OpenWin In VBP.VBE.Windows
If InStr(OpenWin.Caption, "(") > 0 Then OpenWin.Close
Next OpenWin

WB.Activate

'Application.OnKey "%{F11}"
SendKeys BreakIt & Password & "{tab}" & Password & "~" & "%{F11}", True
Application.ScreenUpdating = True
WB.Activate

End Sub

HTH

Ivan

by phoenix

This code may help you out;Option Explicit

Const BreakIt As String = "%{F11}%TE+{TAB}{RIGHT}%V{+}{TAB}"

Sub Change_VBA_PW()
Dim WB As Workbook
Dim Password As String

Set WB = ActiveWorkbook 'Or your workbook name
Password = "test"
Call SetVBProjectPassword(WB, Password)

End Sub

Sub SetVBProjectPassword(WB As Workbook, ByVal Password As String)
'Needs reference to Visual Basic for Applications Extensibility Library
Dim VBP As VBProject
Dim OpenWin As VBIDE.Window
Dim i As Integer

Set VBP = WB.VBProject

Application.ScreenUpdating = False

' close any code windows to ensure we hit the right project
For Each OpenWin In VBP.VBE.Windows
If InStr(OpenWin.Caption, "(") > 0 Then OpenWin.Close
Next OpenWin

WB.Activate

'Application.OnKey "%{F11}"
SendKeys BreakIt & Password & "{tab}" & Password & "~" & "%{F11}", True
Application.ScreenUpdating = True
WB.Activate

End Sub

HTH

Ivan

This discussion is closed to new replies.