VBA Password Prompt when exiting Excel

[baitmaster]

I've seen this question asked before, but have not yet found a useful answer.</SPAN>
</SPAN>
When closing a workbook containing a password-protected VBA module, and then exiting Excel, I'm receiving a pop-up message asking for the VBA Project password. This appears minimum 4 times, the most I’ve seen it so far is 29</SPAN></SPAN>

Microsoft's own help on the subject seems to think the issue is caused by something far more complex what I've encountered:</SPAN>
https://support.microsoft.com/en-us/kb/280454</SPAN>

I’ve managed to narrow my issue down, and can recreate it as follows:</SPAN></SPAN>

• create and save a .xlsm file containing this code: </SPAN></SPAN>

Function test() As String
test = Application.Caller.Address
End Function

and a single formula in a cell =test()</SPAN></SPAN>

• Password protect the VBA module</SPAN></SPAN>
• Close the file and quit Excel</SPAN></SPAN>
• Reopen the file</SPAN></SPAN>
• In the immediate window type: application.calculatefull</SPAN></SPAN>
• Close again and quit Excel</SPAN></SPAN>

Message appears 8 times when Excel quits. The problem is therefore being caused by Application.caller, which I need in my code as my UDF bases its calculation on the cell which called it.</SPAN>
</SPAN>
Is there another way I can refer to the cell that contains the UDF, other than to use this code?
</SPAN></SPAN>
I don’t have Google Desktop, and no other files are open including PMW which has been moved out of XLStart for this test. I'm currently on Excel 2010

Cheers
Matt</SPAN></SPAN>

 

[RoryA]

That code has no issues for me following your steps. Are you sure you don't have any COM add-ins loaded?

 

[baitmaster]

What, just like what you wrote on this thread in 2012?
http://www.mrexcel.com/forum/excel-...xiting-visual-basic-applications-project.html

Embarrassingly, it turns out that yes I do have that. Its also something that I can't uninstall or switch off, because Admin have configured it to reopen when Excel is opened. And I'm working in a major Bank so Admin won't listen to me... Oh well, the bug stays

Thanks once again Rory

 

[blueskyz]

I cant exactly figure out the exact reason. According to what i read so far from forums, users mentioned that the problem stems from issues like plugins, dropbox installed, dll etc. But I realise that the prompt may not appear if you have another excel file that is opened. Not sure whether the following will help but it works for me. What happens is that I ensure that the Excel application does not close entirely when i close the workbook. Not the best solution, but one that works for me for now.

Paste the following under Workbook:


Private Sub Workbook_beforeClose(Cancel As Boolean)

ThisWorkbook.Close

End Sub

 

[Jon Peltier]

This is a problem that has intermittently plagued my own Excel VBA add-ins for a small number of customers. I've documented the problem in my online documentation: VB Password Dialog - Peltier Tech Charts for Excel - Documentation.

While working on a specific situation for a client, I came up with a solution. I don't know if it only works for his situation (on just my machine) or if it is more widely applicable.

I actually tried a few avariations on "ThisWorkbook.Close" as blueskyz suggested above, to no avail. But what I came up with was just as simple, and seems just as unnecessary, but it seems to work.

Insert the line "ThisWorkbook.Saved = True" at the end of the Workbook_BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' blah blah before close code

    ThisWorkbook.Saved = True
End Sub

If anyone has a chance to try this, could you let me know if it helps for you and/or your clients.

 

[cnatac2000]

I cant exactly figure out the exact reason. According to what i read so far from forums, users mentioned that the problem stems from issues like plugins, dropbox installed, dll etc. But I realise that the prompt may not appear if you have another excel file that is opened. Not sure whether the following will help but it works for me. What happens is that I ensure that the Excel application does not close entirely when i close the workbook. Not the best solution, but one that works for me for now.

Paste the following under Workbook:


Private Sub Workbook_beforeClose(Cancel As Boolean)

ThisWorkbook.Close

End Sub

I tried this one, and it looks like working. Thanks