Software

General discussion

Gravatar
0 Votes
Locked

Excel - Cust. View ,Passwd Protect&Macro

By Topic ·
I am currently developing a worksheet where a number of cells have to be password protected. I have also desigend some Custom Views for viewing printing etc. adn I used >Group< to speed up the process within Custom View. The whole oepration for various Group are linked to a Macro which is operated via a button on the worksheet.

The Problem arises when I password protect the worksheet that Grouping and Custom View does not work.
To overcoem this I included two more Macros
a)Unprotect Worksheet,
b) protect worksheet
and incorporated this into the main macro which is operated via a button on the page. The function does solve the problem for the Grouping, and Custom views to function, (due to Protect and Unprotect Macros). At this point the Worksheet cells are protected.

However, I have noted that when I activate the Macro for a particular view, the Spreadsheet is duly protected, but if the user should go to >Tools>Protection>unprotect sheet
Excel automatically unprotects the worksheet without the need to input the password. This is not what I intended.
My QUESTION:
1. How can I write a Custom View that will work with Password Protection
2. How can I use Group with password protection
3. How can i write the macro to pull all of the above elements together without the problem I mention above occuring.
4) Can I password protect Macros so that they can not be changed

The best workable suggestions will get the total 1250 points. If there is a split then the award points will be shared equally

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

by mikex In reply to Excel - Cust. View ,Passw ...

with activesheet
.unprotect password:="your password"
' your code
.protect password:="your password"
end with

gravatar
Collapse -

by Topic In reply to

The Code is Good and Helpfull, would have appreciated a little more on how to input, as in the Answer 2

gravatar
Collapse -

by a.barnes In reply to Excel - Cust. View ,Passw ...

Topic,

The code above is good for the active worksheet. If you want to protect/unprotect all worksheets in the workbook, you could try the following:

Sub mcrProtect(bolProtect As Boolean, strPass As String)
For Each x In Worksheets
If bolProtect Then
x.Protect Password:=strPass
Else
x.Unprotect Password:=strPass
End If
Next x
End Sub

Call this function by using True to protect or False to unprotect, and supply a password.

In the Visual Basic Editor, you can right-click on your module and select VBA Project Properties. Click on the Protection tab and you can apply security so that your code cannot be viewed.

Good luck!

gravatar
Collapse -

by Topic In reply to

Great Reply, Thank very helpful advise.
Would you mind if I e-mailed you for furtehr advice in teh event I should still ahve problems trying to get the code into VBA ( as I am a novice as far as VBA programming is concerned) ???

Thank you very much

gravatar
Collapse -

by Topic In reply to Excel - Cust. View ,Passw ...

This question was closed by the author

Back to Software Forum

Start or search

Related Discussions

Related Forums