VBA & AddInExpress AddIn

Add-in Express™ Support Service
That's what is more important than anything else

VBA & AddInExpress AddIn
VBA Password request on closing Excel File with VBA Makro 
Subscribe
Michael Kaden




Posts: 226
Joined: 2017-11-15
Hello Andrei,

I have an Excel File with VBA Makro, the Makro is Password protected.

When I install my VB.net AddInExpress AddIn and I want to Close this file, I am prompted to enter the VBA Password. If I cancel the request, the file closes normally.

Also if I open the VBA Editor and enter teh Password, it will Close normally.

It Looks to me, that the Excel AddInExpress AddIn wants to Access the VBA Makro Editor before closing the file?

In my Excel AddInExpress AddIn I unlock all cells and protect all cells on AdxExcelAppEvents1_WorkbookOpen I do not use (AdxExcelAppEvents1_WorkbookClose or AdxExcelAppEvents1_WorkbookBeforeClose)

Any Idea how to prevent the prompt for the VBA Makro Password?

Kind regards

Michael
Posted 30 Apr, 2019 11:00:04 Top
Andrei Smolin


Add-in Express team


Posts: 16558
Joined: 2006-05-11
Hello Michael,

I don't know why this occurs. I'd like to reproduce this issue. Does it occur if you do not unlock and then lock all cells in WorkbookOpen? Does the macro protected handle any events?

I can't reproduce the issue in Excel 2016 (Office 365) if I have a password-protected macro (it is never called) and my add-in calls Workbook.Unprotect() and then Workbook.Protect(); no parameters are passed to these calls. Also, I've chosen "Enable all macros" in Trust Center | Macro Settings.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 01 May, 2019 04:05:04 Top
Michael Kaden




Posts: 226
Joined: 2017-11-15
Dear Andrei,

thank you for your prompt Response even on First May.

I send an email to

support@add-in-express.com

thank you and Kind regards

Michael
Posted 03 May, 2019 06:13:22 Top
Andrei Smolin


Add-in Express team


Posts: 16558
Joined: 2006-05-11
Hello Michael,

Sorry for the delayed response; we had a mixture of week end and holidays.

I can't reproduce the issue. I've installed the add-in and followed the steps. The only modification: after I cancel the password request, I close the VBA IDE. I don't think this matters.

I suggest that we work together offline: I'd start with checking Trust Center settings on both sides.

A wild thought: is the issue reproducible if you turn your antivirus off?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 08 May, 2019 07:23:17 Top
Michael Kaden




Posts: 226
Joined: 2017-11-15
Dear Andrei,

thank you very much for your message, I was also away for some time.
I tried without Kaspersky virus scanner, no change.

Please note that I am using Windows 7 and Office 2010 to ensure compatibility with all (newer) MS Versions

I also use VS 2017

and Add-In Express Version 8.9.4453.0
(never change a winning horse)

My Excel Trust centre looks as follows

User added an image
User added an image
User added an image
User added an image
User added an image
User added an image
User added an image
User added an image
User added an image
User added an image
User added an image

Thank you & Kindest regards

Michael
Posted 16 May, 2019 10:09:38 Top
Andrei Smolin


Add-in Express team


Posts: 16558
Joined: 2006-05-11
Hello Michael,

Thank you. I've reproduced the issue when using your add-in in Excel 2010 with my workbook. I can't reproduce the issue with my add-in (it prints the workbook name in the WorkbookOpen event) and my workbook. It looks like the issue depends on your code; also, it may relate to Excel 2010 only. I've tried changing Trust Center settings with no effect.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 17 May, 2019 05:05:12 Top
Michael Kaden




Posts: 226
Joined: 2017-11-15
Hello Andrei,

thank you for your post, it actually pointed me to the right direction.

As I was not using any Features of the AddIn in the VBA code workbook, it must have something to do with the
AdxExcelAppEvents1_WorkbookOpen.

So I checked trough the AdxExcelAppEvents1_WorkbookOpen in the AddIn

In this procedure I go through all Worksheets and check protection etc.

If xlBook IsNot Nothing Then
			Dim asheet As Object = ExcelApp.ActiveSheet
			sheettype = asheet.name
			Dim wSheets As Excel.Sheets = xlBook.Worksheets

			For iSheet As Integer = 1 To wSheets.Count


I store the Name of the starting worksheet so i can go back to it after I checked all the worksheets:


	Next
			''###############
			xlBook.Sheets.Item(asheet.name).Activate


If I copy out

xlBook.Sheets.Item(asheet.name).Activate

The error goes away.

Now I know what causes the error, but I have no clue why an how to rectify the problem.

Do you have any Suggestion?

Thank you & Kind regards

Michael
Posted 21 May, 2019 05:49:23 Top
Andrei Smolin


Add-in Express team


Posts: 16558
Joined: 2006-05-11
Hello Michael,

Michael Kaden writes:
xlBook.Sheets.Item(asheet.name).Activate


You do this in the Workbook.Open event, right? If so, I suggest that you use the machinery we describe in section Wait a Little (https://www.add-in-express.com/docs/net-office-tips.php#wait-a-little) to perform the Activate after the WorkbookOpen is processed. Also, pay attention to releasing COM objects; the issue may also be caused by non-released COM objects returned by xlBook.Sheets and by Worksheets.Item(asheet.name).

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 21 May, 2019 09:44:31 Top
Michael Kaden




Posts: 226
Joined: 2017-11-15
Dear Andrei,

thank you very much for your reply. I checked all the releasing of Com Objects, I had one which I rectified, however i assume that this should not give such error. Anyway I changed it but this did not help.

I was also thinking, that a non released object might be the cause as if I have several Workbooks open in one Excel Instance the request for the VBA password only comes when the last Workbook is closed i.e. this instance of Excel is closed, even if teh Workbook with the VBA code is closed Long before.

I then find out, that the issue is not always there. Then finally I reduced the WorkBookOpen Event to:

	
Private Sub AdxExcelAppEvents1_WorkbookOpen(sender As Object, hostObj As Object) 'Handles AdxExcelAppEvents1.WorkbookOpen
On Error GoTo Errorhandler
Exit Sub
Errorhandler:
Call serv.ECall(Err.Number, Err.Description, Err.Source, "WorkbookOpen")
Resume Next
End Sub


And it still happend (asking for the VBA password when closing the file)
It definately does not happen, when the AddIn is not installed.

So my question is:

What other components of the AddIn are adressed when a workbook is opened, closed or used, without using any components (Ribbon) of the AddIn?

Thank you & Kind regards

Michael
Posted 22 May, 2019 02:03:07 Top
Andrei Smolin


Add-in Express team


Posts: 16558
Joined: 2006-05-11
Hello Michael,

Why is the Handles clause commented out? Is that method called at all?
If I assume that it is called, are you saying that the issue goes away if you comment out the serv.ECall call?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 22 May, 2019 06:13:05 Top