CODEWITHSUNDEEP
vbaexcel
sheds141
sheds141

Reputation: 23

Vba code stops after workbook open. No errors

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

Upvotes: 2

Views: 6496

Answers (2)

Boomer67
Boomer67

Reputation: 75

But even Darren Bartrup-Cook's otherwise fine answer won't suffice if the Shift key is involved ! Vestiges of a bug in XL from 2005! Using the Shift key in a keystroke combination to run a macro will cause execution to halt after the the target workbook opens. For example, CTRL + SHIFT + q to run the macro won't work; CTRL + q will.

Upvotes: 3

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

I can see a couple of things wrong with your code aside from the code stopping.

The code may be stopping due to code in the other workbook firing when it opens, so that needs to be stopped.

The other problems I see are that you're not referencing the newly opened workbook with a variable, instead using ActiveWorkbook which may not always be correct.

The line where you're copy the range is using Users as the range reference, but the cell references are using the currently active sheet.

After your do loop I'd add this code: 

Dim wrkBk As Workbook

Application.EnableEvents = False
Set wrkBk = Workbooks.Open(strLocation & MainProgramName)
Call UserPassword_Unlock
With wrkBk.Worksheets("Users")
    .Range(.Cells(4, 1), .Cells(100, 11)).Copy _
        Destination:=ThisWorkbook.Worksheets("Users").Range("A4")
End With
ThisWorkbook.Save
wrkBk.Close SaveChanges:=False
Call UserPassword_Lock
Application.EnableEvents = True

Note I use wrkBk to reference the newly opened workbook. The Copy and Paste are shortened to a single line with each cell and range reference fully qualified using With wrkbk.Worksheets("Users").
Application.EnableEvents = False should stop any code firing when the workbook is opened.

Upvotes: 5

Related Questions