• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to abort source workbook loading after macro finishes

T

TimeWarp

New Member
Details:

I have two workbooks, a source workbook to import ranges of data from

and a destination workbook to import the data into. Both workbooks have

the same worksheets and userforms, except the destination workbook has

improvements. The purpose here is to import log data going back as far

as 2009, into an improved copy of the source workbook by clicking a button

on a userform, to trigger a macro that automates the process.


By design, when the workbook is opened, Excel is minimized and is hidden,

then the user is presented with a userform to simplify date, time log entry,

and display of information. When the user exits the form, the workbook

is saved and closed. Excel is return to its normal state.


Problem:

The details of the process all work great, but with one nagging issue!

When the macro process is completed and the userform of the destination

workbook is un-hidden, suddenly the source workbook attempts to

load and I am presented with the security dialog box requesting that

macros be allow or not allowed!


If I simply click the "X" close button of the dialog box, the

problem goes away and all is well. What is needed and I have

been unable to acomplish, is a way to close the dialog box with

a macro, or force the source workbook from reloading which

would be an even better solution.


I am unable to figure out why it dose not stay closed since I have

chosen to close it without saving changes within the macro code.


Any help would be appreciated. I am including the macro in question

with this post in hopes that someone better at VBA can tell me what

I have overlooked or am missing here, that could solve this problem?


Thanks in advance,

TimeWarp


'

The Code:

Option Explicit

Sub Macro2GetPastData()


Dim Sheet As Object

Dim wSheet As Worksheet

Dim sfilename As String

Dim Sh As Object

Dim sOp As String


On Error GoTo Macro2GetPastData_Error


Application.ScreenUpdating = True


sOp = "Alert user what action is about to occurr."

'/////////////////////////////////////////////

'* 'This code needs the "EnableCaselKey Property and maybe OnKey"

'* 'to block CTRL+Break, CANCEL or ESC key interuptions

'* 'from the user, to complete password authentication upon completion.

'/////////////////////////////////////////////

Dim Msg, Style, Title, Help, Ctxt, Response, MyString

Msg = "Do you want to perform a PTO log transfer for old workbook into this workbook?" ' Define message.

Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons.

Title = "PTO Log Automatic Update" ' Define title.

Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then ' User chose Yes.

MyString = "Yes" ' Perform next action.

GoTo Cont

Else

MyString = "No" ' User chose No.

GoTo Insufficient

Insufficient:

UserForm1.Show

Exit Sub

End If

Cont:

Application.ScreenUpdating = True 'Temp setting for testing

'=================================================

sOp = "Prepare workbook for editing."

For Each wSheet In Worksheets

wSheet.Unprotect Password:="password" ', _UserInterFaceOnly:=True

wSheet.Visible = xlSheetVisible

Next wSheet

Application.WindowState = xlMaximized

ActiveSheet.Range("A1").Activate


sOp = " Maximisz"

' ActiveSheet.Unprotect "password", AllowFiltering:=True, UserInterfaceOnly:=True, AllowInsertingHyperlinks:=False


'Temp setting for testing

Application.Visible = True 'Temp setting for testing

Application.WindowState = xlMaximized 'Temp setting for testing

' Unload UserForm1 'Temp setting for testing

UserForm1.Hide


']]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]

' First unhide target workbook worksheets and select worksheet "PTO_2009"

' then

sOp = " Locate and open the source workbook to import from."

sfilename = Application.GetOpenFilename

Workbooks.Open sfilename


' Note to Self: In the following macro, "crrPTO_Log.xls" is used as the source file and "PTO_Log.xls" _

is the file to import the data into.

' Need to bypass automatic loading userform in source workbook.

sOp = " Still needs work closing the source file correctly when done."


Application.Run ("'crrPTO_Log.xls'!Maximisz")


For Each Sheet In Sheets

Sheet.Visible = xlSheetVisible

Next


'* MsgBox "Copy first range from crrPTO of 2009"

Sheets("PTO_2009").Select

ActiveSheet.Unprotect Password:="password" ', UserInterFaceOnly:=True

sOp = "Need additional sheet change operations added below"

Range("B10:C35").Select

Selection.Copy

ActiveWindow.WindowState = xlNormal


'* MsgBox "Now paste first range from crrPTO of 2009 to PTO_Log of 2009"

Workbooks("PTO_Log.xls").Activate

Sheets("PTO_2009").Select

ActiveSheet.Unprotect Password:="password" ', UserInterFaceOnly:=True

Range("B10:C35").Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _

xlNone, SkipBlanks:=False, Transpose:=False


'* MsgBox "Copy second range from crrPTO of 2009"

Workbooks("crrPTO_Log.xls").Activate

ActiveWindow.WindowState = xlMaximized

Range("E10:G35").Select

Application.CutCopyMode = False

Selection.Copy

Workbooks("PTO_Log.xls").Activate

Range("E10:G35").Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _

xlNone, SkipBlanks:=False, Transpose:=False


'* MsgBox "Now paste second range from crrPTO of 2009 to PTO_Log"

' and repeat the process for each year worksheet.

Sheets("PTO_2010").Select

Workbooks("crrPTO_Log.xls").Activate

Sheets("PTO_2010").Select

Range("B10:C35").Select

Application.CutCopyMode = False

Selection.Copy

Workbooks("PTO_Log.xls").Activate

Range("B10:C35").Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _

xlNone, SkipBlanks:=False, Transpose:=False

Workbooks("crrPTO_Log.xls").Activate

Range("E10:G35").Select

Application.CutCopyMode = False

Selection.Copy

Workbooks("PTO_Log.xls").Activate

Range("E10:G35").Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _

xlNone, SkipBlanks:=False, Transpose:=False

Sheets("PTO_2011").Select

Workbooks("crrPTO_Log.xls").Activate

Sheets("PTO_2011").Select

Range("B10:C35").Select

Application.CutCopyMode = False

Selection.Copy

Workbooks("PTO_Log.xls").Activate

Range("B10:C35").Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _

xlNone, SkipBlanks:=False, Transpose:=False


Workbooks("crrPTO_Log.xls").Activate

Range("E10:G35").Select

Application.CutCopyMode = False

Selection.Copy

Workbooks("PTO_Log.xls").Activate

Range("E10:G35").Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _

xlNone, SkipBlanks:=False, Transpose:=False


Sheets("PTO_2012").Select

Workbooks("crrPTO_Log.xls").Activate

Sheets("PTO_2012").Select

Range("B10:C35").Select

Application.CutCopyMode = False

Selection.Copy

Workbooks("PTO_Log.xls").Activate

Range("B10:C35").Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _

xlNone, SkipBlanks:=False, Transpose:=False


Workbooks("crrPTO_Log.xls").Activate

Range("E10:G35").Select

Application.CutCopyMode = False

Selection.Copy

Workbooks("PTO_Log.xls").Activate

Range("E10:G35").Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _

xlNone, SkipBlanks:=False, Transpose:=False


Range("A1").Select

Workbooks("crrPTO_Log.xls").Activate

Range("A2").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = ""

sOp = "Goto range A1 of worksheets before hiding and closing source workbook"

Range("A1").Select


Application.ScreenUpdating = True 'Temp setting for testing

sOp = "Hide worksheets before closing source workbook"


With Worksheets

ActiveWindow.DisplayWorkbookTabs = True


For Each Sh In Sheets

sOp = "Make Prompt sheet the active worksheet before closing source workbook"

If Sh.Name = "Prompt" Then

Sh.Visible = xlSheetVisible

Sh.Unprotect Password:="password" ', UserInterFaceOnly:=True

Sh.Activate

Else

ActiveWorkbook.Sheets("Prompt").Visible = xlSheetVisible

ActiveWorkbook.Sheets("Prompt").Select

End If

Next Sh

End With


sOp = " Close the source workbook."

'MsgBox "The name of the active workbook is " & ActiveWorkbook.Name

Set Sheet = Nothing

Set Sh = Nothing

Application.DisplayAlerts = False

Workbooks("crrPTO_Log.xls").Close SaveChanges:=False

sOp = " Process completed."

Workbooks("PTO_Log.xls").Activate

'MsgBox "The name of the active workbook is " & ActiveWorkbook.Name

Range("A1").Select

sOp = "Activate the PTO_Log Userform to confirm operation completion."

Application.Run ("'PTO_Log.xls'!startMyForm")


sOp = "Activate the PTO_Log Userform process completed"

Exit Sub


Macro2GetPastData_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Macro2GetPastData of Module Module1" + vbCrLf + sOp

End Sub

'
 
Are there any event macros in the old log (like worksheet change, activate)? Additionally, are there any forms from the old log book being loaded?


If one of those have been triggered, the need to call that macro gets "stored". VB will then try and run the macro by opening up the workbook.


If you allow the workbook to be re-opened, does it try and run/do something?
 
Hi Luke M,


Yes, you are correct on all three remarks.

There is a worksheet change event tied to each worksheet, there is a form that gets activated when the workbook would normally open and if I allow the workbook to open by allowing macros when the security dialog fires at the end of my import process, then by design, the Userform in the of the source workbook would take control of Excel

such that Excel is minimized and is hidden, then the user is presented with a userform to simplify date, time log entry, and display of information. When the user exits the form, the workbook is saved and closed. Excel is return to its normal state with any other workbook that may have been already open in Excel.


Do you know of a work around that would import my data while keeping the workbook in a closed state?

I only chose the "Application.GetOpenFilename" command because it was the only command I came across that allows the user to locate the path/location of the correct workbook to import from.

But at this point I'd open to any alternatives that get the job done!

And thank you Luke for your response thus far. Sounds like you clearly recognize this issue and I would really appreciate any advise you may have.


Best regards,

TimeWarp
 
We might be able to still work with the workbook in an open state, we just need to disable the change macros. Looking through your code, it doesn't appear your needing those change events to happen, correct? The "magic code" is:


Application.EnableEvents = False


This toggles all change events on/off. Note that, unlike Application.ScreenUpdating, this will not "reset" itself after your code ends (or breaks, if you debug), and you need to reset it at the end of the macro to:

Application.EnableEvents = True


Does turning off the events enable your macro to run smoothly?
 
Luke,


Good idea but no joy. My macro still runs through to the end but I also still get the macro security dialog at the end of the process.

Dose the userform that fires upon opening the source workbook also qualify as an event?

If so, is there away to target the "Application.EnableEvents = False" command at the source workbook before its userform gets activated?


Thanks,

TimeWarp
 
Is the userform your using in the destination workbook (that is staying open) or in the source workbook (which should be closed at end of macro)?


The Application.EnableEvents should be affecting all workbooks, as it's an application setting. But, I do admit that userforms are one of my weaker areas. I'm assuming your code looks something like this?

Code: 
Sub Macro2GetPastData()

Application.EnableEvents = False

'everything else

'goes here

Application.EnableEvents = True

End Sub
 
Luke,


Much joy!

I thought about the question I just posted and realized that I added the "Application.EnableEvents = False" command right after the

sfilename = Application.GetOpenFilename

"Workbooks.Open sfilename" protion of my macro.

By moving the "Application.EnableEvents = False" command just before the "Workbooks.Open sfilename" protion of my macro, suddenly no more macro security dialog!

Ausome, you are the man and "Excel Ninja" you are oh great one!


Thank soooooooo much!

Regards,

TimeWarp (Excel Ninja wanna be)
 
You must log in or register to reply here.
Back
Top