close file disable toolbar

[Alberto Ast]

Is there a way to force user to close file with a buttom macro and disable
the regualr closing opcions like close, exit and X at top right corner?

 

[Jacob Skaria]

From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the
left treeview search for the workbook name and click on + to expand it.
Within that you should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code pane.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
End Sub


Insert a module and copy the below macro..which is to be assigned to a button

Sub CloseBook()
ThisWorkbook.Close True
End Sub


If this post helps click Yes

 

[Alberto Ast]

This is great... I thought it was not going to be posible... but still I
asked and I glad....

First part worked great... but could not get the file to close with the macro.

Do I need a special module or can be in one of the currently used modules...?
Any other idea why the close book is not working?

 

[Jacob Skaria]

I am sure there is something wrong in the way you are trying.

--From a saved workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook. Save workbook
--Run macro from Tools|Macro|Run <selected macro()>

Sub Macrotest()
ThisWorkbook.Close True
End Sub


If this post helps click Yes

 

[Alberto Ast]

I have made a lot of macros... I know what you mean to insert codes on This
Workbook and inserting a module and pasting the macro commands and assigning
it to a buttom... but it does not close... would you review the commands
inserted on "This Workbook".

Thanks.

 

[Jacob Skaria]

OK..I have tried with 2003 and 2007 versions..Since the button is on the
active workbook you cab try the below

Sub Macrotest()
Activeworkbook.Close True
End Sub

If this post helps click Yes

 

[Jacob Skaria]

If you dont want to save changes you can try

Activeworkbook.Close False

If this post helps click Yes

 

[Alberto Ast]

It only works if I remove the commands pasted on "This Worksheet"
Looks like those lines are blocking it to close completele.

 

[Jacob Skaria]

Oops...I totally missed that..

Insert this in 'This Workbook'

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = Not blnClose
End Sub

and Insert the below in a module and assign this to a button

Public blnClose As Boolean
Sub CloseBook()
blnClose = True
ActiveWorkbook.Close True
End Sub


If this post helps click Yes

 

[Alberto Ast]

YES.... BIG YES.... it works now..... THANKS

Oops...I totally missed that..

Insert this in 'This Workbook'

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = Not blnClose
End Sub

and Insert the below in a module and assign this to a button

Public blnClose As Boolean
Sub CloseBook()
blnClose = True
ActiveWorkbook.Close True
End Sub


If this post helps click Yes

 

[Alberto Ast]

Jacob,

I am trying to make a text blink... welll I have it already by using from
cpearson the following document

http://www.cpearson.com/excel/BlinkingText.aspx

but since it uses

Private Sub Workbook_Open()
StartBlink
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopBlink
End Sub

looks like it is interfeering with your comand

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = Not blnClose
End Sub

Both use (Cancel as Boolean)

Is it something I can change?

 

[Jacob Skaria]

Alberto hi again; and thanks for your comments on the other post..Happy that
I could help.

On your query merge those codes togerther like the below

Private Sub Workbook_Open()
StartBlink
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = Not blnClose
If Cancel = False Then StopBlink
End Sub


If this post helps click Yes

 

[Alberto Ast]

Hi Jacob,

Hope you get to read this post.... the closing macro is working great but
now I am facing a new problem with this....

I have three files....

"file1" where I use the closing macro.
"file2" same as "file1", same file structure with other name and data from
other site.
"master" file where I need to pull data from "file1" and "file2".

I open "master" and with a macro open "fiel1" copy some data and paste into
"master"... when I close "file1" my macro stops so it does not get to open
"file2" to copy paste the data...
In advance thanks.