I have some code that is meant to open an xlsm workbook, copy some data from it and paste it in the workbook with the code. Both workbooks are password protected and the code is password protected. I have some code that is setup to run before save, open and close which locks the workbook.
So the problem is that the code stops with no errors after the workbook is opened by vba as seen below. I thought it has something to do with the shift key, a problem I have seen all over the net with the open method but I altered the code to fix that and the problem was still there. I then tried removing the code for the on open in the workbook being opened and it worked. Why is this? I have run code just like this before with workbooks that have code on open and it worked just fine.
I am using Excel 2013.
Sub User_Update()
Application.ScreenUpdating = False
Dim strCurrentProgram As String
Dim MainProgramName As String
Dim strLocation As String
strLocation = "X:\Produktionsmesstechnik\Gehaeuse_Freigabe\"
strCurrentProgram = Dir(strLocation & "*.xlsm")
Do While strCurrentProgram <> ""
If InStr(strCurrentProgram, "Gehäuse Freigabe Program Ver") = 1 Then
If MainProgramName = "" Then
MainProgramName = strCurrentProgram
ElseIf CInt(Mid(MainProgramName, 29, 3)) < CInt(Mid(strCurrentProgram, 29, 3)) Then
MainProgramName = strCurrentProgram
End If
End If
strCurrentProgram = Dir
Loop
Workbooks.Open Filename:=strLocation & MainProgramName <<<< CODE STOPS HERE
ActiveWorkbook.Sheets("Users").Range(Cells(4, 1), Cells(100, 11)).Copy
Call UserPassword_Unlock
ThisWorkbook.Sheets("Users").Range("A4").Paste
ThisWorkbook.Save
Workbooks(MainProgramName).Close
Call UserPassword_Lock
End Sub