VBA and password prompts

[excelheaven]

I have a read only workbook that multiple users use to send data via code to a master database. The master is password protected and the code enters the password upon opening. Each time the master is open for a split second, but very occasionally, two users may try and access it at the same time.

When this happens, the password prompt for the master sheet appears. I have written an error handling routine to respond to the "cannot open workbook error" to tell the user to try again and which then safely exits the routine, but I want to know how to code in to cancel the password prompt when it appears.

Anyone got any ideas?

Thanks

 

[jim may]

Post your existing code, please.

 

[excelheaven]

To open the file I just use:

Workbooks.Open Filename:= _
"Z:\Spread\Master Database.xlsb", Password:="something"

If the workbook is already open on another machine then the password prompt appears.

Thanks

 

[jim may]

Post your existing code referred to below, please.

I have written an error handling routine to respond to the "cannot open workbook error"

 

[excelheaven]

This is the Error HAndler - but it only kicks in after the user has responded to the password prompt to abort the routine and inform the user. An error is not generated when the password prompt first appears.

EH:
If Err = 1004 Then
MsgBox "There is a problem processing your" + vbCrLf + "request at this time." + vbCrLf + "Please try again", vbOKOnly + vbCritical, "Cannot proceed"
Sheets("Home").Select
End
End If

Thanks

 

[jim may]

Try modifying your WB Open code to

On Error GoTo EH
Workbooks.Open Filename:= _
"Z:\Spread\Master Database.xlsb", Password:="something"
EH:
MsgBox "There is a problem processing your" + vbCrLf + "request at this time." + vbCrLf + "Please try again", vbOKOnly + vbCritical, "Cannot proceed"
Sheets("Home")

Maybe?
Not sure - unable to test here...

 

[jim may]

Correction...

On Error GoTo EH
Workbooks.Open Filename:= _
"Z:\Spread\Master Database.xlsb", Password:="something"
Exit Sub 'originally omitted
EH:
MsgBox "There is a problem processing your" + vbCrLf + "request at this time." + vbCrLf + "Please try again", vbOKOnly + vbCritical, "Cannot proceed"
Sheets("Home").Select