• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to protect the sheets using vba macro [SOLVED]

W

webmax

Member
Hi


I have e multiple workbooks for example 25 workbook files. In the each workbook contain 3 worksheets for that i am now manually opening each workbooks and each sheets and protect the sheet and giving password can you tell me this can be done using macro using selection of the folder (ie contains this 25 workbooks)
 
Good day webmax


This must be in the most asked question list (top ten) if you do a search in the search box top right you will be inundated with answers and examples.
 
Hi Webmax


The following will open and protect all of the sheets in a particular folder. You will need to change the folder name to suit. The password has been originally titled Password so change this too if you like.

[pre]
Code: 
Option Explicit
Sub OpenName()
Const sPath = "D:Data" ' < Change this path
Dim sFil As String
Dim owb As Workbook
Dim sh As Worksheet
sFil = Dir(sPath & "*.xls*")

Do While sFil <> ""
Set owb = Workbooks.Open(sPath & sFil)
For Each sh In ActiveWorkbook.Worksheets
sh.Protect "Password"
Next sh
owb.Close True 'Save on Close
sFil = Dir
Loop

End Sub
[/pre]

Take care


Smallman
 
Hi Webmax


You are most welcome.


If you want to unprotect the sheets just use the following;


Code: 
sh.unprotect "Password"


Take care


Smallman
 
Hi, webmax!

Just fyi you may want to give a look at this link:

http://chandoo.org/forums/topic/macro-to-hideunhide-in-protected-sheet-produces-error-code#post-37901

It's about Excel security, which it's not bullet proof, instead it looks like more to a Gruyere cheese.

Regards!
 
Good day webmax


As SirJB7 has pointed out Excel security is nonexistent, you can do all you like with trying to make a workbook secure but any one with a bit of know how can very quickly undo all you have done to make your workbook secure.


There a quite a few posts on this forum telling you how to undo/remove security.


The best security I have found is that if users try to edit or alter that which they are not allowed to then remove them from the users group, if this puts them in jeopardy of losing their job...tough their fault.
 
You must log in or register to reply here.
Back
Top