Solved: Unlock all worksheets

[JohnnyBravo]

OK, quick question. What is the command for unlocking ALL worksheets in a workbook? I recorded a macro to unlock the active work but I need to modify the coding. Here's what I have thus far.[VBA] Sub Unlock_Workbook()
'
' Unlock_Workbook Macro
' Macro recorded 10/7/2005 by John
'
' Keyboard Shortcut: Ctrl+Shift+U
'
ActiveWorkbook.Unprotect Password:="unlockme"
End Sub[/VBA]

Rather than manually selecting each worksheet, I'd like VBA to unlock all of them.

[Bob Phillips]

All worksheets are password protected?

[VBA]
Sub Unlock_Workbook()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect Password:="unlockme"
Next sh
End Sub
[/VBA]

[JohnnyBravo]

Thank you XLD - that works fine. Now another quick follow up question. How do you tell Excel to run that code for whatever workbook is opened? For instance, I've got a folder for "Letters Sent" and a subfolder under that for all the hospitals we deal with. So using Windows Explorer, I open up each hospital's folder and double-click on the Excel file found.

How do you get VBA to automatically execute that for whatever file is opened?

BTW, what does the "Dim" mean?

[Bob Phillips]

Thank you XLD - that works fine. Now another quick follow up question. How do you tell Excel to run that code for whatever workbook is opened? For instance, I've got a folder for "Letters Sent" and a subfolder under that for all the hospitals we deal with. So using Windows Explorer, I open up each hospital's folder and double-click on the Excel file found.

How do you get VBA to automatically execute that for whatever file is opened?

BTW, what does the "Dim" mean?

Not tested, I quickly knocked this code up from some code snippets I have, but it should be fine - just be warned. It automatically opens all those files and processes them.

[VBA]
Option Explicit

Dim oFSO

Sub LoopFolders()
Dim i As Integer

Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "C:\Letters Sent"

Set oFSO = Nothing

End Sub

'--------------------------------------------------------------------------*-
Sub selectFiles(sPath)
'--------------------------------------------------------------------------*-
Dim oFolder As Object
Dim oFiles As Object
Dim oFile As Object
Dim oFldr
Dim oWB As Workbook

Set oFolder = oFSO.GetFolder(sPath)
Set oFiles = oFolder.Files
For Each oFile In oFiles
If oFile.Type = "Microsoft Excel Worksheet" Then
Set oWB = Workbooks.Open(Filename:=oFile.Path)
Unlock_Workbook oWB
oWB.Save
oWB.Close
End If
Next oFile

For Each oFldr In oFolder.Subfolder
selectFiles oFldr.Path
Next

Set oWB = Nothing
Set oFiles = Nothing
Set oFolder = Nothing

End Sub

'--------------------------------------------------------------------------*-
Sub Unlock_Workbook(wb As Workbook)
'--------------------------------------------------------------------------*-
Dim sh As Worksheet
For Each sh In wb.Worksheets
sh.Unprotect Password:="unlockme"
Next sh
End Sub

[/VBA]

Dim means Dimension a variable, that is dedclare it and type it.

.

[johnske]

Hi JohnnyBravo,

If you want to have the same code available for every workbook you may have opened, the simplest way's probably to place the code in 'Personal.xls' and you can then call on it whenever it's needed.

i.e. place xlds' original code (below) in Personal.xls ....

All worksheets are password protected?

[VBA]
Sub Unlock_Workbook()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect Password:="unlockme"
Next sh
End Sub
[/VBA]

HTH,
John

[JohnnyBravo]

Thanks for all your guys - I really appreciate it.

[Zack Barresse]

Hello, I'm going to mark this as Solved.