VBA & AddInExpress AddIn

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

Only registered users are allowed to subscribe.
VBA & AddInExpress AddIn
VBA Password request on closing Excel File with VBA Makro 
Subscribe
Michael Kaden




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

thank you very much, this is very kind of you.

I send you an email to support with the files attached.

Thank you again and have a nice weekend.

Michael
Posted 15 Jun, 2019 01:32:33 Top
Andrei Smolin


Add-in Express team


Posts: 16568
Joined: 2006-05-11
Thank you, Michael.

I've rebuilt the add-in and setup projects. I suppose I'll test it tomorrow.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 17 Jun, 2019 08:59:52 Top
Michael Kaden




Posts: 226
Joined: 2017-11-15
Thank you Andrei,

I am keen to see what your results are.

kind regards 6 thank you

Michael
Posted 18 Jun, 2019 07:43:19 Top
Andrei Smolin


Add-in Express team


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

Sorry for the delay; yesterday I was out of office because of a health issue.

Whenever I start Excel, I get an almost endless number of errors; the first one is this:

---------------------------
aleraSoft error
---------------------------
Error in DLL _mscorlib

Program _NewWorkbook

Error number _ 57

A required privilege is not held by the client.



Show errors? *+*
---------------------------
Yes No
---------------------------

Then I need to close some 10 or more error messages. One of them says: couldn't find c:\aleralog.txt.

When I open the file you sent me recently - its file name ends with "New 500 kW", Excel reports that it contains a circular reference. Then I get a warning that I have no license (four times). After that I get the Welcome form where I click Start, then cancel the Unprotect Sheet request (many times), get another error similar to the one above and some 10+ error messages.

Well, I can reproduce the issue in this form: after I cancel opening the VBA module, I close the workbook (do not save changes) and then close Excel. Here I get numerous AleraFBC password requests.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 19 Jun, 2019 07:55:12 Top
Andrei Smolin


Add-in Express team


Posts: 16568
Joined: 2006-05-11
Hmm, the add-in installed and the add-in started from the project work differently. If I start the add-in from the project, I don't see the numerous exceptions above.

Still I get four warnings (captioned "Input Error") about the license missing. The first one shows a different assistance contact email address.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 19 Jun, 2019 09:59:56 Top
Michael Kaden




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

Thank you very much for your reply.

I hope that your health is restored fully and that you are feeling well.

The file “500 kW” is a very old, started 10 years ago, VBA project, and at that time I used MsgBox to track errors. However it was the application when the password prompt issue came up first, when my users have also my new Vb.Net AddIn installed. But we should not use this workbook to get to the bottom of the password prompt issue

To reproduce the issue easier, I have made the Excel Workbook “Password test.xlsm”, see my email 3.5.2019 13:15. This workbook is also included in the Folder “XExcelSamples” in the files I send you with the complete project. Just in case, the Password, to look at the VBA code is: PTX

The password prompt issue also comes up with this workbook.

The VB.net ADX AddIn should not use any MsgBox usage for Error messages. Do you get MsgBox errors before you open the “500 kw” workbook?

The “aleraSoft error” you quoted, should not be seen as a message box, but come up in the aleraLog.txt file?

The Errors in my Vb.net AddIn are written into the aleralog.txt file.

I had a MsgBox Error routine in my Error handler
Service.vb (356) Public Sub ECall
Lines (361 – 394) but this should not be used due to
(361) GoTo NoMessage
(394) NoMesssage:
(391)This also includes a MsgBox where you could supress repeat Errors with the same numbers.
So why do you get MsgBox Errors ????

This is how aleralog.txt is created

Define a global variable for the location:

C:\aleraSoft\111alera adx\Support 01\GlobalVariables.vb(21):
Public MyAppFile As String = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & "\alera\aleralog.txt"

So the file location is therefore USER\AppData\Local\alera\ aleralog.txt

Why your program tries to open c:\aleralog.txt is not clear to me?

But if your “C Directory” is protected and for some reason it is trying to write to c:\aleralog.txt then you will get many errors. The Err. Description “A required privilege is not held by the client” seems to point to that?

To limit the size of aleraLog.txt this file is erased and newly created whenever an Excel Workbook is opened.

'My.Computer.FileSystem.DeleteFile(MyAppFile)

In our test project this is commented out so the messages stay even when a new Workbook is opened, but it needs to be cleared manually before it gets too big.

“Input Error” is a text used in the VBA 500 kW file Error not in the Vb.net AddIn project.

I believe the many errors experienced are because you cannot access aleralog.txt when you run the AddIn installed. When you run it from the project, you seem not to have these restrictions?

So

1.) Lets use “Password test.xlsm”

2.) Please look why you get MsgBox Errors as this is skipped in Service.vb (356) Public Sub ECall

3.) Please look why there is a restriction on access to aleralog.txt and why it is trying to put it on C:\

I believe when we clarified these, then we can look at the issue “password prompt” in more detail.

Thank you very much for your support & kind regards

Michael
Posted 20 Jun, 2019 01:23:31 Top
Andrei Smolin


Add-in Express team


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

I can't reproduce the issue if I use Password test.xlsm. That is I start Excel, open the file, click End to close the form, modify the workbook, open the VBA IDE, try to open the VBA module, cancel the request, close the VBA IDE, close the workbook (choosing No when asked whether to save the workbook), close Excel. If I use “500 kW”, I get the issue 100%.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 24 Jun, 2019 05:32:38 Top
Michael Kaden




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

Strange, I get the issue with password test.xlsm every time. Just to make sure:

I have used the files, which I have transmitted to you and installed the project by

alera - right mouse
select AddIn Express
select register
The project registers and the Com and Excel AddIns are shown in Excel Options

Before that I have unregistered the project and checked that it is not loaded.

I open password test.xlsm
Clear the form with the END button
Click Developer button
Click Visual Basic - which opens the "Microsoft Visual Basic for Applications" IDE
Double click VBA Project (Password test.xlsm)
Cancel button on Password request
Close the file with the control box button
Confirm Don't save on closure

BTW the password prompt only comes up when the EXCEL instance is closed, i.e. all the workbooks are closed.

What is your suggestion how to find the reason for the password prompt if the Password test.xslm does no give you the issue?

I would also appreciate if you could comment on my comments 2.) and 3.)above


thank you very much & kind regards

Michael
Posted 24 Jun, 2019 08:00:28 Top
Andrei Smolin


Add-in Express team


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

I strongly suggest commenting out blocks of your code. Start with commenting out complete VBA code.

I can comment on these items: I don't reproduce them any longer.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 26 Jun, 2019 09:31:15 Top
Michael Kaden




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

thank you for your answer.

I removed all VBA code from my Password test.xlsm but left the VBA part password protected. The password prompt still comes up on closing the Excel instance.

Perhaps the following gives a clue to where the problem comes from??

I open a new workbook (Book1). Then I open Password Test .xlsm in the same Excel Instance. I open the VBA editor when Book1 is in focus. I double click the VBA Folder of Password Test .xlsm and cancel the password request. I close all files and nothing happens.

Same procedure as above, but I open the VBA editor when Password Test.xlsm is in focus. I double click the VBA Folder of Password Test .xlsm and cancel the password request. The password prompt comes up after completely closing the Excel instance (all workbooks closing)

Same as above, but I open the VBA editor when Password Test.xlsm is in focus. I double click the VBA Folder of Password Test .xlsm and cancel the password request. Then I open the VBA editor when Book1 is in focus. I double click the VBA Folder of Password Test .xlsm and cancel the password request. I close all files and nothing happens.

Any suggestion what to test next when you look at the above behaviour?

The password prompt only comes up when my AddIn is installed. It does not make any difference if I install it with the setup from the internet or if I register it from the VS IDE.

To me it looks like that when Password test.xlsm has focus, Windows does not realise the password cancel and this is so because something in the code of my AddIn.

As far as I can see from my google research, it seems to be a Windows / office / Excel bug which is connected to specific software Versions.

I have the following
Windows 7 Professional / Service Pack1 64 bit
Microsoft Office 2010 EXCEL 14.0.7232.5000 (32 bit)
Visual Studio 15.8.5
AddIn Express 8.9.4453

Is there anything else where there could be a difference between my and your machine setup?

Any suggestion is highly appreciated.

Thank you for your input and kind regards
Posted 27 Jun, 2019 01:57:34 Top