Freeze screen when running code

[zoog25]

Hello all,

Here is the situation, i have the following code that I'm running. The code moves sheets from one workbook to another and then adds VBA coding to a few of the sheets. My question is the following, is there any code that prevents a person from visually seeing the code run its parts. What i mean is that when i run the code it shows opening up the other workbook, moving the sheets over, opening the editor window, adding code to the sheets, and then closing the editor window. All might make the person running feel like there is an issues. Is there any way of say having the screen freeze and then have all the items run int he background and then when the screen unfreezes, all the changes have been made. Here is the code i'm running in case there is something i can add to it to make this happen.

Sub WBOpenMod()

Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim fName
Dim wbATL As Workbook

fName = ActiveWorkbook.Path & "\Agreement Tracking Log.xlsx"

Select Case fName = Empty
    Case False
        Set wbATL = Workbooks.Open(fName)
        wbATL.Sheets("Final Map").Move Before:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        wbATL.Sheets("Tract Parcels").Move Before:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        wbATL.Sheets("Permits").Move Before:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        wbATL.Sheets("NOC").Move Before:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        wbATL.Sheets("Security").Move Before:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        wbATL.Sheets("Contact").Move Before:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        wbATL.Sheets("Contact (ST)").Move Before:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        wbATL.Sheets("DATA").Move Before:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Call AddFMCode
        Call AddTPCode
        Call AddSTCode
        Call AddNOCCode
        wbATL.Save
        wbATL.Close
        WbOpen = True
End Select

ThisWorkbook.VBProject.VBE.MainWindow.Visible = False

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

As you can see i tried using the ScreenUpdating to prevent this but it doesn't quite do what i want it to do.

 

[diddi]

i dont know the answer, but a work around might be to display a full screen userform with a progress bar or something?

 

[ajm]

have you set screenupdating to false in any of the macros that you call from this routine?

 

[zoog25]

Hello ajm. No i haven't, i might try that and see if that will work.

 

[Jaafar Tribak]

Turning off ScreenUpdating will only work partially for freezing the excel window, won't work on the vbe.

You can use some API calls to freeze the entire screen but if an unhandled error occurs, it could cause the entire screen to stay frozen adding more confusion to the user.

Adding to diddi's suggestion, you could also make the userform topmost to prevent any other window from coming to the foreground.

Another idea is to open the "Agreement Tracking Log.xlsx" workbook in a hidden seperate excel instance and run the macro there.

BTW, You can add code to a vbaproject without making the vbe visible.

 

[zoog25]

Hello Jaafar,

Thank for the suggestions. I did have a question or follow up to what you said.

How do i open workbook in a hidden separate excel instance?

Seconding with you last comments, how do i add code to the project without making the vbe visible.

Thank you.

 

[Dan_W]

Hi Zoog25. There are plenty of websites (and posts in this forum!) which that deal with both of your questions.

As a general guide, Chip Pearson's site is always an excellent resource , and he provides a useful guide on adding VBA code programatically (i.e., without making the VBE visible): http://www.cpearson.com/Excel/vbe.aspx. As for using separate instance, the code provided by Excel Off The Grid would help you here:

Sub CreateNewSession()
Dim xlApp As Excel.Application
Set xlApp = New Excel.Application

xlApp.Workbooks.Add
xlApp.Visible = True

Set xlApp = Nothing
End Sub

To make it 'hidden', you just need to change the .Visible property to False.

 

[zoog25]

Thank you Dan_W for the advice. I believe your method works in more or less how I can do a few things. Also, the links you provided are great resources to help with other aspects I can incorporate.

 

[Dan_W]

Glad to hear it - I always learn something new whenever I visit Chip's site.