Password VBA

[JP Montoya]

I have two questions regarding the following VBA:

Private Sub Workbook_Open()
With Sheet4
.Protect Password:="crtc", UserInterfaceOnly:=True
.EnableOutlining = True
End With
With Sheet5
.Protect Password:="crtc", UserInterfaceOnly:=True
.EnableOutlining = True
End With
With Sheet7
.Protect Password:="crtc", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub

1.) I would like to mask the password
2.) I would like for the macros to always be on regardless of the macro settings unless the correct password is entered if possible.

Thanks

 

[Datsmart]

1). You can Password protect your VBA Project to not allow a casual user to see your password in your code.

Here are the steps:
In VBE, click "Tools" | "VBA Project Properties... "
Click the Protection Tab
Check the "Lock Project for Viewing" checkbox
Enter and confirm your password.
Next time you open the saved workbook, the properties will take effect.


2). Here is a good Demo on Macros Settings:
http://www.datapigtechnologies.com/flashfiles/forcemacros.html

 

[JP Montoya]

I noticed that the link you provided (which was very helpful) was in an older version of Excel. I am working off of 2007 is there something I need to do in the Trust Settings?

 

[Smitty]

I noticed that the link you provided (which was very helpful) was in an older version of Excel. I am working off of 2007 is there something I need to do in the Trust Settings?

Nope, just protect the VBA Project and then users can't get in to see anything unless they have the password.

As for the second question:

2.) I would like for the macros to always be on regardless of the macro settings unless the correct password is entered if possible.

You don't. There's no way to programmatically change a user's macro security settings, otherwise the entire concept would be relatively useless. Take a look at Self-Cert on the help file, which is a relatively simple way of signing your code, so that it's trusted on other machines.

HTH,

 

[JP Montoya]

DatsMart (above) provided a great work around but I run into 2 issues:

1.) I love the fact that I can hide supporting tabs and they cannot unhide them. But my supporting tabs will need to be unhidden by myself and the link did not cover that.

2.) In my newer version of excel you do not get (or at least consistantly get) the option to enable or disable macros (the pop up window) In Excel 2007 it gives you this little option above the spreadsheet and in the option there is not something that says "disable".

Any thoughts?

 

[Smitty]

1) You generally don't need to unhide sheets in order to work on them with code.

2) In 2007 goto the Office Button-->Options-->Trust Center-->Trust Center Settings-->Macro settings. You'll find your enable options there.

 

[balki]

One thing you could try is checking to see if macros are enabled. If they're not enabled, throw a warning to the user (then possibly close the workbook). Not 100% sure HOW to do this, but here's code that I use to check to see if a file is opened as READ ONLY. I would guess that the same check could be made on whether macros are on or not?

Dim blnReadonly As Boolean
blnReadonly = ThisWorkbook.ReadOnly
If blnReadonly = True Then
MsgBox ("This file was opened as READ ONLY, and therefore cannot process anything. Please reopen and repeat your request.")
GoTo SKIPtoEND
End If

 

[Datsmart]

balki,
If macros are not enabled, you can not get a macro to close the workbook or do anything else.

Erik has some good suggestions in the below link, but a macro can not check anything if it is not allowed to run.
http://www.mrexcel.com/board2/viewtopic.php?t=265961

 

[Michael M]

John is correct....but you can force users to enable macros
Create a new sheet in your workbook called "INTRO"
Put a text box on this sheet warning users that if they see this warning they will have to restart excel and enable macros.
Use this code to run the OPen and close events.
You will have to change the Open code to suite your needs.

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Worksheets("INTRO").Activate
Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> ("INTRO") Then ws.Visible = xlSheetVisible
    Next ws
 Worksheets("Establishment").Activate
 For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = ("INTRO") Then ws.Visible = xlSheetHidden
    Next ws
Application.ScreenUpdating = True
End Sub

'*******Workbook Close Code******

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = ("INTRO") Then ws.Visible = xlSheetVisible
Next ws
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> ("INTRO") Then ws.Visible = xlSheetHidden
Next ws
End Sub