Exiting file without Excel default interfering

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Hi,
I would like to know if it is possible to insert some kind of code somehow
and somewhere to make the closing of a file via anything other than a VB
macro possible. That is I need to be able to stop a user closing a file via
the X button or via the file menu, etc., and only close/save, etc., via a
VBform that I create.
I realise the Application.DisplayAlerts = False might do this?? but it
doesn't seem to always work, particularly in Office 97.
Rob
 
I don't work with VB, but you might want to play with this idea:

In the workbook that you want to control in the Thisworkbook module:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
MsgBox "uh, uh, uh!"
End Sub


Then in your VB code (I used VBA from a different workbook for testing):

Option Explicit
Sub testme03()
Dim wkbk As Workbook
Set wkbk = Workbooks.Open(Filename:="book99.xls")

'do your stuff

Application.EnableEvents = False
wkbk.Close savechanges:=True 'false???
Application.EnableEvents = True
End Sub

The .enableevents = false means that excel won't even try to run the
_beforeclose event and you can close (and save???).

So this'll get close. If the user opens the workbook with macros disabled or
just disables events, you're, er, in trouble.

If it's important, you may want something in the _beforesave event, too???

====
When you're developing this stuff, you can enter commands in the immediate
window within the VBE.

Ctrl-G to see it.

application.enableevents = false
right before you close
application.enableevents = true
right after you close

It'll make the development a tiny bit easier.
 
Hi Dave,
Thanks heaps for your suggestion. It seems initually that this will do he
trick.
First of all I meant VBA not VB.
Secondly, Although I can input various codes and know my way around
reasonably well within VBA, I do not understand all the procedures and
protocols.
Regarding your code, I cannot get past the line
Set wkbk = Workbooks.Open(Filename:="book99.xls")
I have put the name of my workbook instead of book99.xls, but when I run the
procedure it wants to reopen the same workbook again because of that Set
statement.
Do I need to know something else here?
In case this helps, I have shown how I have it in the particular workbook
I'm working on.

Private Sub CloseDocument_Click()
Dim wkbk As Workbook
Set wkbk = Workbooks.Open(Filename:="Tax Records beta.xlt")
UserForm1.Show
Application.EnableEvents = False
wkbk.Close savechanges:=True 'false???
Application.EnableEvents = True
End Sub

The UserForm1 brings up options to close with or without saving and even an
option to rename the file at the end of the financial year.
The first part stopping the user closing the workbook by any other way works
great.
Thanks very much,
Rob
 
I thought that you were opening your workbook via your program--so I included an
open in my sample. (Toss that line if you don't need it.)

And I thought you were closing the workbook via your routine, too.

So someway you'll want to close that workbook. And it looks like that workbook
is actually a workbook that was created from a template.

Is that part true?

I think that creating a new workbook based on that template (another button???)
might be better:

Option Explicit
Dim wkbk As Workbook
Private Sub CreateDocument_Click()
Set wkbk = Workbooks.Add(template:="tax records beta.xlt")
'Set wkbk = Workbooks.Add(template:="c:\my documents\excel\book8.xlt")
UserForm1.Show
'do your stuff
End Sub

Private Sub CloseDocument_Click()
Application.EnableEvents = False
wkbk.Close savechanges:=True 'false???
Application.EnableEvents = True
End Sub

I think I'd be careful and fully specify the location of the template and then I
think I'd do a File|Save (in code) if you really wanted to save it. Take a look
at .saveAs in the help in VBA.
 
Thanks Dave for your valuable input! I appreciate that it may be better to
have a button to create the workbook from the template which I may
implement after I have the rest working properly.
The Workbook is created initually from a template which is just opened from
File/New option on menu bar.
I still have one problem with the procedure, however.
I have fiddled around with your earlier code (removing all the first part)
and have it running very nicely.
The problem I have is that on first opening of the file, the beforeClose
event works great. BUT... after I close the file with the procedure stated
above, and reopen it again, the BeforeClose event does not occur. (The file
closes without any prompts). It will only work again if I completely close
down Excel and then reopen the file. I presume this is not a problem with
the code but rather a bug in Excel which I hope is not the case. Could you
look at or try this and see what the problem might be.
If its easier, I could attach a semblance of the file with just the
necessary close and BeforeClose codes and buttons there-in (only 27kb) or,
all the code I have in the test file is set out below.

This is what I have on a test workbook.....
On Sheet1, I have a Control Toolbox Command button with the following code:
Private Sub CommandButton1_Click()
CloseSaveOptions.Show
End Sub

I use this code to close the file in a Form called "CloseSaveOptions":

Private Sub CloseYes_Click()
Application.EnableEvents = False
ActiveWorkbook.Close savechanges:=False 'false to close without save
Application.EnableEvents = True
End Sub

AND this is in ThisWorkbook module to make sure the user has to use the
options I give him:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
MsgBox "Must use Close Document button on Tax Invoice Sheet!"
End Sub

Thanks Dave,
Rob
 
I thought that the code (including the userform) would be a different workbook.
From the sound of it, I was wrong.

Anyway:

Private Sub CloseYes_Click()
Application.EnableEvents = False
ActiveWorkbook.Close savechanges:=False 'false to close without save
Application.EnableEvents = True
End Sub

Causes a problem.

You disable events, then you close the workbook that's running the code. So
that last line is never run. So the events are kept disabled.

Try this:

Put this in a general module:

Option Explicit
Public blkEvents As Boolean

(blkEvents will now be "visible" everywhere in your code.)

Then in the workbook_beforeclose:
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = Not blkEvents
If blkEvents = False Then
MsgBox "Must use Close Document button on Tax Invoice Sheet!"
End If
End Sub

and finally in your button click:
Option Explicit
Private Sub CloseYes_Click()
blkEvents = True
ActiveWorkbook.Close savechanges:=False 'false to close without save
End Sub

So instead of using excel's application.enableevents to stop the save, we do it
with our own variables.

And I don't care what happens to them when we close the workbook.





rob said:
Thanks Dave for your valuable input! I appreciate that it may be better to
have a button to create the workbook from the template which I may
implement after I have the rest working properly.
The Workbook is created initually from a template which is just opened from
File/New option on menu bar.
I still have one problem with the procedure, however.
I have fiddled around with your earlier code (removing all the first part)
and have it running very nicely.
The problem I have is that on first opening of the file, the beforeClose
event works great. BUT... after I close the file with the procedure stated
above, and reopen it again, the BeforeClose event does not occur. (The file
closes without any prompts). It will only work again if I completely close
down Excel and then reopen the file. I presume this is not a problem with
the code but rather a bug in Excel which I hope is not the case. Could you
look at or try this and see what the problem might be.
If its easier, I could attach a semblance of the file with just the
necessary close and BeforeClose codes and buttons there-in (only 27kb) or,
all the code I have in the test file is set out below.

This is what I have on a test workbook.....
On Sheet1, I have a Control Toolbox Command button with the following code:
Private Sub CommandButton1_Click()
CloseSaveOptions.Show
End Sub

I use this code to close the file in a Form called "CloseSaveOptions":

Private Sub CloseYes_Click()
Application.EnableEvents = False
ActiveWorkbook.Close savechanges:=False 'false to close without save
Application.EnableEvents = True
End Sub

AND this is in ThisWorkbook module to make sure the user has to use the
options I give him:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
MsgBox "Must use Close Document button on Tax Invoice Sheet!"
End Sub

Thanks Dave,
Rob
Click to expand...
 
Hello again Dave,
Thank you so much for your Excellent advice. The last procedures worked
perfectly the first time without altering anything!
Sorry I was not clear about how all this was set out in the first place.
(It's hard to know what to advise the newsgroup when asking a question when
you're not sure what's relevant at the time.)
It would be so good to have someone, like yourself, sitting next to me
whilst creating a workbook to tell me where I'm going wrong.
Is there a cheap or free program that will help teach me some of these
procedures I've been asking?
Thanks again for your time and great code.
Rob


Dave Peterson said:
I thought that the code (including the userform) would be a different workbook.
From the sound of it, I was wrong.

Anyway:

Private Sub CloseYes_Click()
Application.EnableEvents = False
ActiveWorkbook.Close savechanges:=False 'false to close without save
Application.EnableEvents = True
End Sub

Causes a problem.

You disable events, then you close the workbook that's running the code. So
that last line is never run. So the events are kept disabled.

Try this:

Put this in a general module:

Option Explicit
Public blkEvents As Boolean

(blkEvents will now be "visible" everywhere in your code.)

Then in the workbook_beforeclose:
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = Not blkEvents
If blkEvents = False Then
MsgBox "Must use Close Document button on Tax Invoice Sheet!"
End If
End Sub

and finally in your button click:
Option Explicit
Private Sub CloseYes_Click()
blkEvents = True
ActiveWorkbook.Close savechanges:=False 'false to close without save
End Sub

So instead of using excel's application.enableevents to stop the save, we do it
with our own variables.

And I don't care what happens to them when we close the workbook.





rob said:
Thanks Dave for your valuable input! I appreciate that it may be better to
have a button to create the workbook from the template which I may
implement after I have the rest working properly.
The Workbook is created initually from a template which is just opened from
File/New option on menu bar.
I still have one problem with the procedure, however.
I have fiddled around with your earlier code (removing all the first part)
and have it running very nicely.
The problem I have is that on first opening of the file, the beforeClose
event works great. BUT... after I close the file with the procedure stated
above, and reopen it again, the BeforeClose event does not occur. (The file
closes without any prompts). It will only work again if I completely close
down Excel and then reopen the file. I presume this is not a problem with
the code but rather a bug in Excel which I hope is not the case. Could you
look at or try this and see what the problem might be.
If its easier, I could attach a semblance of the file with just the
necessary close and BeforeClose codes and buttons there-in (only 27kb) or,
all the code I have in the test file is set out below.

This is what I have on a test workbook.....
Click to expand...
Click to expand...
 
I know that I'm pretty cheap, er, that's spending, so that's not what you meant.

You can visit a bunch of sites that have stuff in them:

John Walkenbach's Links page:
http://j-walk.com/ss/excel/links/index.htm

Chip Pearson's version:
http://www.cpearson.com/excel/links.htm

Debra Dalgleish's page for books:
http://www.contextures.com/xlbooks.html
(John Walkenbach's book is very nice)

(and don't forget these sites, too.)

Way too many to mention...

But my favorite source are the newsgroups and google. You can learn a lot by
lurking and trying. And every once in a while, you can post with something that
helps others.




rob said:
Hello again Dave,
Thank you so much for your Excellent advice. The last procedures worked
perfectly the first time without altering anything!
Sorry I was not clear about how all this was set out in the first place.
(It's hard to know what to advise the newsgroup when asking a question when
you're not sure what's relevant at the time.)
It would be so good to have someone, like yourself, sitting next to me
whilst creating a workbook to tell me where I'm going wrong.
Is there a cheap or free program that will help teach me some of these
procedures I've been asking?
Thanks again for your time and great code.
Rob

Dave Peterson said:
I thought that the code (including the userform) would be a different workbook.
From the sound of it, I was wrong.

Anyway:

Private Sub CloseYes_Click()
Application.EnableEvents = False
ActiveWorkbook.Close savechanges:=False 'false to close without save
Application.EnableEvents = True
End Sub

Causes a problem.

You disable events, then you close the workbook that's running the code. So
that last line is never run. So the events are kept disabled.

Try this:

Put this in a general module:

Option Explicit
Public blkEvents As Boolean

(blkEvents will now be "visible" everywhere in your code.)

Then in the workbook_beforeclose:
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = Not blkEvents
If blkEvents = False Then
MsgBox "Must use Close Document button on Tax Invoice Sheet!"
End If
End Sub

and finally in your button click:
Option Explicit
Private Sub CloseYes_Click()
blkEvents = True
ActiveWorkbook.Close savechanges:=False 'false to close without save
End Sub

So instead of using excel's application.enableevents to stop the save, we do it
with our own variables.

And I don't care what happens to them when we close the workbook.





rob said:
Thanks Dave for your valuable input! I appreciate that it may be better to
have a button to create the workbook from the template which I may
implement after I have the rest working properly.
The Workbook is created initually from a template which is just opened from
File/New option on menu bar.
I still have one problem with the procedure, however.
I have fiddled around with your earlier code (removing all the first part)
and have it running very nicely.
The problem I have is that on first opening of the file, the beforeClose
event works great. BUT... after I close the file with the procedure stated
above, and reopen it again, the BeforeClose event does not occur. (The file
closes without any prompts). It will only work again if I completely close
down Excel and then reopen the file. I presume this is not a problem with
the code but rather a bug in Excel which I hope is not the case. Could you
look at or try this and see what the problem might be.
If its easier, I could attach a semblance of the file with just the
necessary close and BeforeClose codes and buttons there-in (only 27kb) or,
all the code I have in the test file is set out below.

This is what I have on a test workbook.....
Click to expand...
Click to expand...
Click to expand...
 
Thanks Dave
Can't imagine you being cheap with all the valuable stuff you've helped me
with.
Thanks for the links!
I frequent and seem to get most of my help via this newsgroup but even
though the answers are great, I can't seem to learn some of the fundamentals
that would help me write more of my own code. The helps in Excel make me
look stupid because it assumes you know a lot of stuff.
Even some of the answers in this group assume the same although I forgive
them because they can't possibly know what standard of expertise the
"requestor" has.
Questions relating to Functions, Private, Public Static subs; why the
Application statement is needed before anothere statement sometimes, what
works with the
With statements and so on are all questions that make the begginer feel
discouraged. Oh for a BASICS on basic!
For instance one that really gets me is that quite a few example are given
with the Function statement but when I copy it to try it it won't even begin
to execute??? Which means I need to come to this group more than I would
need to.

Rob

Dave Peterson said:
I know that I'm pretty cheap, er, that's spending, so that's not what you meant.

You can visit a bunch of sites that have stuff in them:

John Walkenbach's Links page:
http://j-walk.com/ss/excel/links/index.htm

Chip Pearson's version:
http://www.cpearson.com/excel/links.htm

Debra Dalgleish's page for books:
http://www.contextures.com/xlbooks.html
(John Walkenbach's book is very nice)

(and don't forget these sites, too.)

Way too many to mention...

But my favorite source are the newsgroups and google. You can learn a lot by
lurking and trying. And every once in a while, you can post with something that
helps others.
Click to expand...
 
Yeah, there's a learning curve. But that's true with most things. You may want
to visit your local bookstore to see if you can find a book that you like.

And I would guess that most functions are gonna be called by something else--a
different sub/function or even from a worksheet.

If the function returns a simple value, you can always do:

Function test(aStr as string) as String
'do something neat
end function

And just call it from

sub test1()
msgbox test("HI")
end sub

But the only real good way to learn is to keep trying/lurking. I know that I
"monitor" other regular posters so that I can see how they approach things.



rob said:
Thanks Dave
Can't imagine you being cheap with all the valuable stuff you've helped me
with.
Thanks for the links!
I frequent and seem to get most of my help via this newsgroup but even
though the answers are great, I can't seem to learn some of the fundamentals
that would help me write more of my own code. The helps in Excel make me
look stupid because it assumes you know a lot of stuff.
Even some of the answers in this group assume the same although I forgive
them because they can't possibly know what standard of expertise the
"requestor" has.
Questions relating to Functions, Private, Public Static subs; why the
Application statement is needed before anothere statement sometimes, what
works with the
With statements and so on are all questions that make the begginer feel
discouraged. Oh for a BASICS on basic!
For instance one that really gets me is that quite a few example are given
with the Function statement but when I copy it to try it it won't even begin
to execute??? Which means I need to come to this group more than I would
need to.

Rob
Click to expand...
<<snipped>>
 
All good suggestions! Thanks Dave
Just BTW, I love how most of you guys must live on the other side of the
world as it's great waking up and logging on in the mornings and having your
replies waiting for me.
Rob
 
No, no, no. You live on the other side! <vbg>

You ever talk to someone on the phone and sent them an email so that they can
see a file. That 30 seconds it takes seems like forever. Amazing where
technology has taken us so far.

rob said:
All good suggestions! Thanks Dave
Just BTW, I love how most of you guys must live on the other side of the
world as it's great waking up and logging on in the mornings and having your
replies waiting for me.
Rob
Click to expand...
<<snipped>>
 
Back
Top