Macro needed to protect and unprotect a particular sheet in excel

[vignesh_thegame]

Hi,
I need to create a Macro to protect a particular worksheet with a password in a workbook.

For example:-

my workbook has a filename "Vignesh.xlxs". i have many sheets in that such as "Jan", "Feb", "Mar", Apr", "May"..... "Dec".

Here i need to protect only "Jan" to "Dec" sheets with same password, and "Jul" sheets must be Editable.

This workbook is a employee details for an year. Like this i have more than 1500 workbooks...

I tried this below macro, but it was protecting all sheets in a workbook. but i need to lock all sheets except one sheet..

Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+q


For i = 1 To Sheets.Count
Sheets(i).Protect Password:="vicky", DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets(i).EnableSelection = xlUnlockedCells
Next i
End Sub

Can anyone really look into this and help me....

 

[sastoka]

Hi try this.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim w As Worksheet

     For Each w In Worksheets          
         If w.Name <> ("Jul") Then w.Protect ("password")      
     Next w

End Sub

This will trigger before closing the workbook, and will save every sheets that has a different name than Jul

 

[vignesh_thegame]

Hi Sastoka,
Thanks for your reply. i have slightly modified your code and its works fine. Now i need take this a step further. This code is working for a single workbook. i have a set of 500 excel workbook with same format sheet. For all these i need a macro to open the excel file and protect only "Jul" sheet and then all workbook needs to be save and close...

 

[sastoka]

Are all the workbooks located in the same folder ?

 

[vignesh_thegame]

Yes. All the workbooks are in same folder. i need a macro to open all excel files and do protect all sheets in that workbook except "Jul" sheet and then it must be save and close...

 

[sastoka]

First if you could give us the path to the folder that contain every workbook.

then tell us if they have the same format as for the name

and lastly unfortunately, calling workbook, open, run the macro then, I think it's an application level (absolutely not sure) and I have never used application level, you will have to wait for someone more qualified than me.

sorry

 

[vignesh_thegame]

Hi,
i have used the below coding to open all workbook and a could call a macro to run in that, this code is opening all workbook, but i m not able to run my macro on it..

Sub AllFiles()
Dim folderPath As String
Dim filename As String
Dim wb As Workbook

folderPath = "C:\SAP Imports\Sales Orders\" 'change to suit

If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"

filename = Dir(folderPath & "*.xls")
Do While filename <> ""
Application.ScreenUpdating = False
Set wb = Workbooks.Open(folderPath & filename)

'Call a subroutine here to operate on the just-opened workbook
Call Mymacro


filename = Dir
Loop
Application.ScreenUpdating = True
End Sub

My macro is here,
Private Sub Workbook_Activate()
Dim w As Worksheet


For Each w In Worksheets
If w.Name <> ("Jul") Then w.Protect ("password")
Next w


End Sub

i need this to run on all workbook