Locking VBA Project.

[newuser08]

Hi Guys,

The workbook I’ve created has individual pages locked to the user (same password for each sheet), also I have locked the VBA Project.

When I open the book I have made the sheets visible and unlocked them to save me time, however I also want the VBA Project to be unlocked.

I have tried to record a macro however to record I need to unlock the VBA then I end up with a blank macro.

I have limited knowledge to vba so I tried Application.Unprotect (“pword”), which as you can imagine didn’t work. Any suggestions would be a great help.

Below is the On Open I have been using:

Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
Sheet2.LblUser.Caption = Application.UserName
Sheet2.Activate

If Application.UserName = "user.1" Then
a = MsgBox("Do You Need To Change Things?", vbYesNo)
If a = 6 Then

Sheet1.Visible = xlSheetVisible
Sheet1.Unprotect ("pword")
Sheet4.Visible = xlSheetVisible
Sheet4.Unprotect ("pword")
Sheet2.Activate
ActiveSheet.Unprotect ("pword")
Application.Unprotect (“pword”)

End If
Else

Application.Calculation = xlCalculationAutomatic
Sheet2.LblUser.Caption = Application.UserName
Sheet2.Activate
Sheet2.ScrollArea = "A1:M37"

End If
End Sub

 

[macropod]

Hi newuser08,

The following code, which is based on work by fellow MS MVP Bill Manville, can be used to Protect/Unprotect a vba project, provided you know the password. The example is for Excel, but could be modified to work with other Office applications. Change "Password" to suit

Sub TestProtect()
ProtectVBProj "Password"
End Sub

Sub TestUnprotect()
UnprotectVBProj "Password"
End Sub
Sub UnprotectVBProj(ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection <> 1 Then Exit Sub ' already unprotected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys Pwd & "~~"
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
End Sub

Sub ProtectVBProj(ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection = 1 Then Exit Sub ' already protected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~"
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
End Sub

Cheers

[MS MVP - Word]

 

[newuser08]

Thanks for that macropod, I have seen this referenced on numerous sites, BUT I cant get the code to work.

Has anyone got a working model that can be provided so that I can manipulate… I just cant get the above to work!

Many Thanks

 

[SkipVought]

"Has anyone got a working model that can be provided so that I can manipulate."

You just got one.

"BUT I cant get the code to work."

Please provide specifics; modifications you made, error messages and corresponding statement.


Skip,

_{Just got a nuance...
to replace the old subtlety!}

 

[newuser08]

I changed

Sub TestProtect() to Sub ProtectVBA()

Sub TestUnprotect() to Sub UnprotectVBA()

I placed UnprotectVBA in the worksheet on open this returned 'Programmatic access to Visual Basic Project is no trusted' error message.

I need to reiterate I am a newbe and am a little unsure as to where to put the code, so please go easy on me

Thanks

 

[AnotherHiggins]

- Go to Tools > Macro > Security.

- Go to the Trusted Publishers tab.

- Check the box beside Trust access to Visual Basic Project.

- In the future, tell us the specific problem you're running into

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[newuser08]

thanks guys worked just fine.

In future I will try to be more concise.