closing the active workbook

  • Thread starter Thread starter jmoffat
  • Start date Start date
J

jmoffat

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 ?
 
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 ope
it via my browser, the workbook remains unhidden. Seems to be a proble
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
 
Back
Top