I have a sheet with names of files, and next to it numbers for passwords, like this:
Anneke-Coetzer.xls 5913 5340
Now if I step through this with F8 while in debug mode this code works. But when I run it (using my shortcut key), it doesn't... I believe it's because the sheet that opens gains focus, and it breaks the current macro.
Sub PassWordSaveAll()
drive = "c:\blah\test\temp\"
'Note you have to select three cells for this. FileName, Password, Protect Password
If Selection.Rows.Count = 1 And Selection.Columns.Count = 3 Then
FileToSave = Selection.Cells(1, 1).Value
BookPass = Selection.Cells(1, 2).Value
SheetPass = Selection.Cells(1, 3).Value
'vvvv--- New sheet is opened here, and gains focus, and the rest is not executed
If TryOpen(drive & FileToSave) Then
' NewFile is opened
Call ProtectAllSheetsAuto(SheetPass)
' Save The file with Password
T = Application.DisplayAlerts
'Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=drive & FileToSave, _
FileFormat:=xlNormal, Password:=BookPass, WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = T
ActiveWorkbook.Close
End If
End If
End Sub
Can anybody help me as to how I can get it to work when I press my shortcut key for this macro?
Bookmarks