Macro hangs up after opening a second workbook

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
149
I am having a strange problem come up that I've never seen before. Hoping someone can tell me where I've glitched my code, so I can fix it and quit feeling like an idiot.

I am running a macro that pulls a list of new projects and enters the data into the Audit Log. The problem I'm having is that the Audit Log opens, and then the macro freezes on that page. No going further, no messages, no anything.

If for some reason I already have the Audit Log open, then the macro runs full-out, without a hiccup to be had.

Is there something that I'm doing wrong on the opening of that second file that keeps it from going on from there?

While this isn't an emergency (I can simply let it stop, then go back to wb1 and hit the macro again, and since the Audit Log is open, it works then), it's frustrating as heck. Hoping someone can help me.

Thank you so much for taking the time to look at this!


Code:
Sub ProjectTasks()

' Run Audit Log entry & Create New MDR
' Written by Melody October May
' Created Jul 6, 2020

Application.ScreenUpdating = False
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
On Error GoTo ErrorHandler

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim pwd As String
Dim pwdX As String

pwd = Range("Z1")
ActiveWorkbook.Unprotect Password:=pwd
Set wb1 = ActiveWorkbook

Workbooks.Open Filename:="X:\QASystems\Audit Projects Status Log.xlsm"

'THIS IS WHERE MY PROBLEM OCCURS - THE MACRO HANGS UP HERE UNLESS THE LOG IS ALREADY OPEN

     Sheets("All Projects").Select
     Set wb2 = ActiveWorkbook
     pwdX = Range("AB1")
     Set wb2 = ActiveWorkbook

wb1.Activate

'Enter into Audit Projects Log
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

ChrisFoster

Board Regular
Joined
Jun 21, 2019
Messages
127
Office Version
  1. 365
Platform
  1. Windows
Try this, I am not sure it will fix it but it's worth a shot.

VBA Code:
        Workbooks.Open Filename:= _
        "X:\QASystems\Audit Projects Status Log.xlsm"
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,813
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
How do you actually trigger the macro, and is there any code in the Workbook_Open event of the Audit log workbook?
 

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
149
OK, I did the smart thing (d'uh!) and turned on display alerts. Turns out the hang-up was actually a few lines further down (which I didn't include), when I went to do the entry - I used the wrong password (pwdX instead of pwd) to unprotect the sheet.

I apologize for throwing out a "help me" that was nothing more than user sloppiness. I thank you both for pitching in on this :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,656
Messages
5,573,438
Members
412,529
Latest member
cTatch
Top