closing the active workbook

Hi

I am trying to write some code that when the workbook is opened, it prompts the user for a password and if an invalid password is entered it closes the workbook.

This seems pretty simple. Putting the below snippet into the open event ...

pass_dpanel.Show
If (pass_dpanel.TextBox1.Value <> "correct") Then
' not valid password
MsgBox ("invalid password")
ActiveWorkbook.Close
End If

Which works ok until I copy the workbook onto our web server. This time when an incrrect password is entered a duplicate file is opened myworkbook[1].xls and if an incorrecte passwrod is entered for a seccond time I get an "Invalid operation" error.

Any ideas ?

Try

MsgBox ("invalid password")
ThisWorkbook.Close <<<<< Note change..
End If

HTH,

"jmoffat" wrote:

>
> Hi
>
> I am trying to write some code that when the workbook is opened, it
> prompts the user for a password and if an invalid password is entered
> it closes the workbook.
>
> This seems pretty simple. Putting the below snippet into the open
> event ...
>
> pass_dpanel.Show
> If (pass_dpanel.TextBox1.Value <> "correct") Then
> ' not valid password
> MsgBox ("invalid password")
> ActiveWorkbook.Close
> End If
>
> Which works ok until I copy the workbook onto our web server. This
> time when an incrrect password is entered a duplicate file is opened
> myworkbook[1].xls and if an incorrecte passwrod is entered for a
> seccond time I get an "Invalid operation" error.
>
> Any ideas ?
>
>
> --
> jmoffat
> ------------------------------------------------------------------------
> jmoffat's Profile: http://www.excelforum.com/member.php...fo&userid=1151
> View this thread: http://www.excelforum.com/showthread...hreadid=535477
>
>

no it didn't work

I get the same error

I tried it on a web server and got an error created by a reference to
another project (Rob Bovey's code cleaner infact). Not a problem on my
own machine. Maybe you could try removing any references that arn't
really required?? Bit of a long shot.

Paul

there are no such references the code is pretty much as written

I have tried alternateive fix such as:

Private Sub Workbook_Open()
'
Call get_pass
If (Not pass_ok) Then
bname = ThisWorkbook.Name
Windows(bname).Visible = False
End If
'

But this didnt work either - once I put it onto the web server and open it via my browser, the workbook remains unhidden. Seems to be a problem specific to the web.

Hi
I would guess so. It's an interesting issue and it would be nice to see
a solution! There must be a lot of excel files accessed from web
servers.

Paul

after much experimentation and in case anyone wants to know ...

It would appear that if you download excel from a web server via a simple URL link then, when using the _open event or auto_open subroutine, at least one workbook MUST remain open after the download. Otherwise the borowser [MS internet explorer] opens up a copy called something like: workbook[1].xls

So to close the active workbook (for example if the an entered password is incorrect) you must ...

sub auto_open
pass_dpanel.Show
bname=activeworkbook.name
If (pass_dpanel.TextBox1.Value <> "correct") Then
' not valid password
MsgBox ("invalid password")
workbooks.add <-- must do this to keep a workbook open !
Workbooks(bname).Close
End If