Excel VBA Suddenly Goes To Design Mode during Macro Runtime

Question

Hope someone help me with this problem.

I have a subroutine that will copy my data from this workbook to another existing workbook(Report Workbook).

When I try to run the code, during run-time once it opens the Report Workbook it suddenly stops and I've notice that it goes to design mode. Thus, hindering the execution of the routine.

But when I try to put a break point on the subroutine and continue it again. It executes without problem. This seems a bit odd.

I'm a bit out of the corner trying to figure this out.

So far this are the troubleshooting steps that I've tried.

Tried to run the Workbook in another machine.
I have tried deleting all the Workbook_Open on the Report Workbook.
Tried to delete all the Macro Codes on the Report Workbook.
Insert an error handler. Unfortunately, no error is presented.
Tried to delete the DoEvents code on the subroutine.

But still no luck. It stops the code from executing.

Private Sub TransferRawData()
Dim wsPTRawData As Worksheet, wbPTWorkBook As Workbook, wsOutputRaw As Worksheet
Dim filePath As String, FileName As String, ptTargetRow As Long

Application.EnableEvents = False
Application.StatusBar = "Exporting All Raw Data... Please wait a moment..."
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

filePath = ThisWorkbook.Path & "\"
FileName = filePath & pt_FileName

Set wbPTWorkBook = Workbooks.Open(FileName:=FileName, UpdateLinks:=0, IgnoreReadOnlyRecommended:=True)
DoEvents
Set wsPTRawData = wbPTWorkBook.Worksheets(pt_ProdRawSheet)
Set wsOutputRaw = ThisWorkbook.Sheets(merger_prodOutputSheet)

ptTargetRow = wsPTRawData.Range("A" & Rows.Count).End(xlUp).Row + 1

If lastRow(wsOutputRaw, "A") > 1 Then wsOutputRaw.Range("A2:F" &    lastRow(wsOutputRaw, "A")).Copy wsPTRawData.Range("A" & ptTargetRow)

wbPTWorkBook.Close True

Set wsOutputRaw = Nothing
Set wsPTRawData = Nothing
Set wbPTWorkBook = Nothing

End Sub

BTW, I have 3 set of workbook that uses the above code. All of them will copy the data on the Report Workbook. But all of them are having trouble in executing the transfer routine.

For additional Information. Once I run the routine and it executes the Workbooks.Open Event. Here is the screenshot of the VBA Project Window and Excel Window.

is there an error showing? ... please post a screenshot if you can — jsotola
hi @jsotola, It didn't appear any error messages. But I added a screenshot of my screen to give you a better overview on my problem. — BLitE.exe
Can you add error catches in there and output the error message? Sometimes there are hidden error messages — Maldred
Don't know if this is all of the code, but the Calculation and EnableEvents settings are persistent - you should be setting them back before your code exits. — Tim Williams
Hi @TimWilliams yeah, actually i can omit all those performance enhancer settings in this routine. Cause before I execute all the routines in my projects I have set it all up and set them back again. I just added all those settings on this routine for additional reference. — BLitE.exe

Maldred Maldred · Accepted Answer · 2017-11-02T18:54:44

Add an error handler in here and see if there are any errors that aren't being caught properly

Private Sub TransferRawData()
Dim wsPTRawData As Worksheet, wbPTWorkBook As Workbook, wsOutputRaw As Worksheet
Dim filePath As String, FileName As String, ptTargetRow As Long

Application.EnableEvents = False
Application.StatusBar = "Exporting All Raw Data... Please wait a moment..."
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

filePath = ThisWorkbook.Path & "\"
FileName = filePath & pt_FileName

On Error GoTo ErrHandler
Set wbPTWorkBook = Workbooks.Open(FileName:=FileName, UpdateLinks:=0, IgnoreReadOnlyRecommended:=True)
DoEvents
Set wsPTRawData = wbPTWorkBook.Worksheets(pt_ProdRawSheet)
Set wsOutputRaw = ThisWorkbook.Sheets(merger_prodOutputSheet)

ptTargetRow = wsPTRawData.Range("A" & Rows.Count).End(xlUp).Row + 1

If lastRow(wsOutputRaw, "A") > 1 Then wsOutputRaw.Range("A2:F" &    lastRow(wsOutputRaw, "A")).Copy wsPTRawData.Range("A" & ptTargetRow)

wbPTWorkBook.Close True

Set wsOutputRaw = Nothing
Set wsPTRawData = Nothing
Set wbPTWorkBook = Nothing


ErrHandlerSave:
    Debug.Print Chr(13) & "Error # " & Str(Err.Number) & " was generated on Saving" _
        & Err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) & Err.Description
End Sub

You'll see an error message come up in the Immediate window at the bottom of the VBA code. If you don't have it up or unsure how to show it LOOK HERE

Excel VBA Suddenly Goes To Design Mode during Macro Runtime

2 Answers