PDA

View Full Version : Solved: VBA Project Password Prompt on Close??



Jacob Hilderbrand
10-24-2005, 01:00 PM
I am having some intermittent anamolies with an Excel program that I am working on. It uses a data base / Access as the backend if that may matter.

Sometimes, not all times, but sometimes when I close the workbook will close, Excel will be closed, and I will get a password prompt popup for the VBA project.

If I enter the password nothing happens. If I cancel on the popup, it pops up again. It will popup again about 5 or six times before not coming back.

Then the next time I close, nothing happens.

Any ideas on what could be happening?

malik641
10-25-2005, 07:28 AM
Weird, this guy's (http://www.xtremevbtalk.com/showthread.php?t=241736) having the same problem. I can't really think of anything that would do this....Do you have any workbook_Close events that may trigger this?

The only scenario that I could think of would be an Application.OnTime command that ...I guess... would check for a password, and maybe if this is triggered and the application was closed before the command is executed, then there would be no password to verify??? Therefore prompting you for a password??? :dunno It's a bad guess I know, but it's a guess.


Hopefully that will give anybody who reads this some insight on this situation.

Jacob Hilderbrand
10-25-2005, 10:48 AM
This is the Close Event code.


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call DeleteMenu
'ThisWorkbook.Saved = True

End Sub


This is the DeleteMenu Code.

Sub DeleteMenu(Optional Dummy As Long)

Dim MenuSheet As Worksheet
Dim Row As Long
Dim Caption As String
Dim HotKey As String

On Error Resume Next
Set MenuSheet = ThisWorkbook.Sheets("MENU_SHEET")
Row = 2
Do Until IsEmpty(MenuSheet.Cells(Row, 1))
If MenuSheet.Cells(Row, 1) = 1 Then
Caption = MenuSheet.Cells(Row, 2)
Application.CommandBars(1).Controls(Caption).Delete
Else
End If
Row = Row + 1
Loop
On Error GoTo 0
Application.StatusBar = False

Set MenuSheet = Nothing

End Sub

No other events, and no On Time code running. And it is intermittent so I can't get it to happen all the time, just every other or every third time I close.

Thanks

Jacob Hilderbrand
05-23-2006, 03:34 PM
Bump. Anyone have any suggestions on this issue?

Thanks

Ivan F Moala
05-24-2006, 12:07 AM
Do you have any Addins? or Loaded Dlls from the Addin manager?
This can sometimes do it.

Jacob Hilderbrand
05-24-2006, 04:00 PM
No add-ins are installed. I found some information that says it can be caused by class instance that references the workbook with a locked project. The workbook closes before the variable is released.

But none of my variables referenced a workbook, and there were no class modules and no add-ins.

It is intermittent, or at least I can't find the right trigger to make it happen. :(

Thanks

malik641
05-31-2006, 03:23 PM
Just a few things I thought of....I'm not sure if all of them are related, but I figured I'd ask.

-Have you tried taking out the VBA password?
-Is this digitally signed?
-Do you have any thing else open when you close the workbook?
-Do you have Google Desktop Search?
-Do you have duplications of project names in the VBE?

MountainVogu
05-31-2006, 06:02 PM
Just a thought

You said you have a db backend connection so I presume you're retrieving a recordset or have an active returned query on a worksheet.

could there be something triggering a refresh of the data that in turn triggers an access request to your code.

..total clutch a straws...

Jacob Hilderbrand
06-01-2006, 04:41 PM
Just a few things I thought of....I'm not sure if all of them are related, but I figured I'd ask.

-Have you tried taking out the VBA password?
-Is this digitally signed?
-Do you have any thing else open when you close the workbook?
-Do you have Google Desktop Search?
-Do you have duplications of project names in the VBE?

The prompt only occurs on locked projects.

It is not signed.

Nothing else is really open, maybe Outlook or Word or something like that. Even Excel is not open at the time of the prompt (or not visible at least).

No Google Desktop.

No duplicate names, in fact, it is the only open file in Excel (besides personal.xls)


Just a thought

You said you have a db backend connection so I presume you're retrieving a recordset or have an active returned query on a worksheet.

could there be something triggering a refresh of the data that in turn triggers an access request to your code.

..total clutch a straws...

It has to be something with the db, cause it happens on a few files, once in a while, but all the files use Access. But there are no triggered events that retrieve data. So there are macros to run and they open the db, add / edit / retrieve data, then close the db.

Thanks :)

MountainVogu
06-08-2006, 03:14 PM
Have a look at this:

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B280454

Jacob Hilderbrand
06-08-2006, 06:53 PM
Thanks, it doesn't seem to be the cause in my case though.

Thanks

Jake

malik641
06-08-2006, 08:46 PM
Any chance of us seeing that db backend coding?

And have you tried not using that Call DeleteMenu line? Don't think that'll be it, but you could try it.

Justinlabenne
06-09-2006, 04:22 AM
I have seen similiar things happen when a recordset is opened and not closed properly or set to nothing.

asingh
06-09-2006, 06:21 AM
What I would suggest is that..remove all the code [actually comment out] that is connecting to your Access data base..then try it a couple of times..It is probably the recordset instance, which is still active when the workbook tries to close, and the connection is still active with the Access data base. If this fixes the problem, then you should go through your code and where ever a recorset instance is created...make sure you are killing that instance using SET RST = Nothing, once you have used the recordset..!

Hope this helps...

regards,
asingh

Jacob Hilderbrand
06-10-2006, 11:19 AM
Here is the code that I am using to interact with the database. If you notice anything that should be done differently let me know.



Option Explicit

Sub AddData(Tbl As String)

Dim Db As Database
Dim StrSQL As String
Dim LastRow As Long

Set Db = OpenDatabase(mdbFile)
'Insert data into the database
StrSQL = "Insert into [" & Tbl & "] Select * FROM [tbl] IN '" & xlsFile & "' 'Excel 5.0;'"

'Execute the SQL
Db.Execute StrSQL

'Close the database
Db.Close

Set Db = Nothing

End Sub

Sub DeleteData(StrSQL As String)

Dim Db As Database

Set Db = OpenDatabase(mdbFile)

'Execute the SQL
Db.Execute StrSQL

Set Db = Nothing

End Sub

Sub RetrieveData(StrSQL As String, ws As Worksheet, ClearRange As String, CopyToRange As String)

Dim Db As Database
Dim rs As Recordset
Dim i As Long

'Open the database
Set Db = OpenDatabase(mdbFile)

'Store the records in the database to a variable
Set rs = Db.OpenRecordset(StrSQL)

'Clear old data from the worksheet
ws.Range(ClearRange).ClearContents

'Copy data from the record set to the worksheet
ws.Range(CopyToRange).CopyFromRecordset rs

rs.Close
Db.Close

Set rs = Nothing
Set Db = Nothing

End Sub

malik641
06-10-2006, 01:56 PM
Not sure if this will matter (or be the culprit of your issue). But in the RetrieveData procedure, try setting the ws = Nothing. Even though it was not set as something in the procedure, it's still an object that has a reference....and maybe it's still held after the workbook closes.


Sub RetrieveData(StrSQL As String, ws As Worksheet, ClearRange As String, CopyToRange As String)

Dim Db As Database
Dim rs As Recordset
Dim i As Long

'Open the database
Set Db = OpenDatabase(mdbFile)

'Store the records in the database to a variable
Set rs = Db.OpenRecordset(StrSQL)

'Clear old data from the worksheet
ws.Range(ClearRange).ClearContents

'Copy data from the record set to the worksheet
ws.Range(CopyToRange).CopyFromRecordset rs

rs.Close
Db.Close

Set rs = Nothing
Set Db = Nothing
Set ws = Nothing
End Sub
Just a thought.

Jacob Hilderbrand
06-10-2006, 04:09 PM
Ok, I'll give it a shot, run some tests and let you know.

Thanks

Justinlabenne
06-11-2006, 12:12 AM
I added the line Db.Close right before it is set to nothing. You have this line in the other two procedures, but this one didn't have it. Heres hoping that will do it.


Sub DeleteData(StrSQL As String)

Dim Db As DAO.Database

Set Db = OpenDatabase(mdbFile)

'Execute the SQL
Db.Execute StrSQL

Db.Close 'Added
Set Db = Nothing

End Sub

Jacob Hilderbrand
06-12-2006, 10:16 AM
Thanks Justin, that seems to have taken care of it. :)

jumpjack
05-30-2016, 12:47 AM
I know this is an ancient thread, but I have same issue, and no solution is available around... after 10 years!
I have no connections to external db.
I use Excel 2010 (14.0.7128.5000 - 32 bit) on Windows 10.
I even tried to set up a minimal file with empty macros.... but it keep asking for password on exit. BUT not on my PC! Only on the PC of my customer, which uses almost same version (14.0.7166.5000), but I think he uses Windows 7 (I have to ask).

markddelorme
06-23-2016, 01:18 PM
I have the same issue as described everywhere on this thread, i.e. the problem only happens on my work remote desktop account, but doesn't occur anywhere else... I can't find a solution online, although it seems to be a common occurrence... removing the VBA project password is not an option.