Open File Password Prompt While Closing-CodePudding

I have two separated workbooks with macroces: Book1 with Macro1 and Book2 with Macro2. Macro1 is simple code to:

hidely open Book2 (has an open password: 1111);
write "test" in A1 Active Sheet of Book2;
run Macro2 from Book2, which is simple MsgBox "Hello! I am an msgbox from Book2";
close Book2 with saving.

Sub Macro1()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Dim myWb As Workbook
Set myWb = Workbooks.Open(ThisWorkbook.Path & "/" & "Book2.xlsb", True, False, , "1111")
myWb.Unprotect ("1111")


Cells(1, 1) = "test"

myWb.IsAddin = True
Application.Run "'Book2.xlsb'!Macro2"
myWb.Close savechanges:=True


Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Sub Macro2()
MsgBox "Hello! I am an msgbox from Book2"
End Sub

I emphasize that everything should be done secretly and the user should not see neither Book2 nor any flashes, etc. But when code comes to the saving step a window pops up on the screen, which, by the way, can be ignored and the code will continue to run/execute but anyway it's a PROBLEM I want get rid of: password pop-up window

What I noticed:

If I remove the opening password from book2, the saving will be successfull and without pop-up dialogs. But I need Book2 opening protected.
I tried to modify code, but results are not unsatisfactory due to screen blinking, screenshots below: OK V1-NOK V2-NOK

V1: myWb.IsAddin = True is deleted/commented

Application.Run "'Book2.xlsb'!Macro2"
myWb.Close savechanges:=True

V2: ON/OFF IsAddin = True before/after Macro2 executing

myWb.IsAddin = True
Application.Run "'Book2.xlsb'!Macro2"
myWb.IsAddin = False
myWb.Close savechanges:=True

CodePudding user response：

Please, try the next way. It will open the workbook in a new session, will write in its first sheet and run the macro. But a macro sending a message from a non visible session/window is not the best idea. In order to see the message, please move the cursor over the Excel workbooks icons on taskbar, select the MsgBox window and press OK. A better idea would be do avoid sending messages. The code should run as you need:

Sub HiddenWBOpenRunMacro()
  Dim newEx As Excel.Application, myWb As Workbook
  
  Set newEx = CreateObject("Excel.Application")
  'Open the workbook in the newly created session:
  Set myWb = newEx.Workbooks.Open(ThisWorkbook.Path & "/" & "Book2.xlsb", , , , "1111")

  myWb.Sheets(1).cells(1, 1).Value = "Test"  'write in its first sheet
  myWb.Application.Run "'Book2.xlsb'!Macro2" 'run the macro

  myWb.Close True                            'close the workbook, saving it
  newEx.Quit                                 'quit the session
End Sub

If ThisWorkbook not in Excel Trusted Locations it must be added.