Hi, I need to get rid of the save option. I only want the sheet able save
when the document is closed and it askes if you want to save. I do not want
the document to be saved any other time.

Here is my document (Office 2007 Only).

http://www.savefile.com/files/1848919

It has been made to book out computer and laptop suites.
Trouble is, some people delete others booked sessions and put their own in.

So I have a macro that locks a cell after data is entered into the cell. As
the sheet is protected anybody trying to delete it afterwards cannot.

Trouble was they would have to enable macros to use the macro. That's like
asking a thief to leave his address when he robs a bank.
So the important sheets are veryhidden & I have another macro that unhides
them when you enable macros. Therefore they cannot access the other sheets
until macros are enabled.

My last problem is that if they enable macros then save when they on the
unhidden sheets & save, then quit without saving they document stays on the
veryhidden sheets. Which is not good as the macros are not enabled.

Gord has tried but been unable to assist and could some other kind soul have
a look.Thanks

Try this. In the VBA editor add a new Class Module called Class1. Insert
the following code into it:-

Dim WithEvents MyExcel As Application
Dim booAllowSave As Boolean
Private Sub Class_Initialize()
Set MyExcel = Application
booAllowSave = False
End Sub
Private Sub Class_Terminate()
Set MyExcel = Nothing
End Sub
Private Sub MyExcel_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
booAllowSave = False
End Sub
Private Sub MyExcel_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI
As Boolean, Cancel As Boolean)
If booAllowSave = False Then
Cancel = True
End If
End Sub

Now insert a new module and add the following code into it:-

Dim MyClass As Class1
Public Sub Auto_Open()
Set MyClass = New Class1
End Sub
Public Sub Auto_Close()
Set MyClass = Nothing
End Sub

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk


"Mr. Burton" wrote:

Hi, I need to get rid of the save option. I only want the sheet able save
when the document is closed and it askes if you want to save. I do not want
the document to be saved any other time.

Here is my document (Office 2007 Only).

http://www.savefile.com/files/1848919

It has been made to book out computer and laptop suites.
Trouble is, some people delete others booked sessions and put their own in.

So I have a macro that locks a cell after data is entered into the cell. As
the sheet is protected anybody trying to delete it afterwards cannot.

Trouble was they would have to enable macros to use the macro. That's like
asking a thief to leave his address when he robs a bank.
So the important sheets are veryhidden & I have another macro that unhides
them when you enable macros. Therefore they cannot access the other sheets
until macros are enabled.

My last problem is that if they enable macros then save when they on the
unhidden sheets & save, then quit without saving they document stays on the
veryhidden sheets. Which is not good as the macros are not enabled.

Gord has tried but been unable to assist and could some other kind soul have
a look.Thanks

Hi, I've entered what you wrote and I get a compile error on 2 lines:

Private Sub MyExcel_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)

&

Private Sub MyExcel_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI
As Boolean, Cancel As Boolean)

Thanks for helping

"Alan Moseley" wrote:

Try this. In the VBA editor add a new Class Module called Class1. Insert
the following code into it:-

Dim WithEvents MyExcel As Application
Dim booAllowSave As Boolean
Private Sub Class_Initialize()
Set MyExcel = Application
booAllowSave = False
End Sub
Private Sub Class_Terminate()
Set MyExcel = Nothing
End Sub
Private Sub MyExcel_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
booAllowSave = False
End Sub
Private Sub MyExcel_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI
As Boolean, Cancel As Boolean)
If booAllowSave = False Then
Cancel = True
End If
End Sub

Now insert a new module and add the following code into it:-

Dim MyClass As Class1
Public Sub Auto_Open()
Set MyClass = New Class1
End Sub
Public Sub Auto_Close()
Set MyClass = Nothing
End Sub

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk


"Mr. Burton" wrote:

Hi, I need to get rid of the save option. I only want the sheet able save
when the document is closed and it askes if you want to save. I do not want
the document to be saved any other time.

Here is my document (Office 2007 Only).

http://www.savefile.com/files/1848919

It has been made to book out computer and laptop suites.
Trouble is, some people delete others booked sessions and put their own in.

So I have a macro that locks a cell after data is entered into the cell. As
the sheet is protected anybody trying to delete it afterwards cannot.

Trouble was they would have to enable macros to use the macro. That's like
asking a thief to leave his address when he robs a bank.
So the important sheets are veryhidden & I have another macro that unhides
them when you enable macros. Therefore they cannot access the other sheets
until macros are enabled.

My last problem is that if they enable macros then save when they on the
unhidden sheets & save, then quit without saving they document stays on the
veryhidden sheets. Which is not good as the macros are not enabled.

Gord has tried but been unable to assist and could some other kind soul have
a look.Thanks

Sorry, I am using Outlook 2003 so we may not have the declaration correct.
Remove everything from within the class module except for the following:-

Dim WithEvents MyExcel As Application
Dim booAllowSave As Boolean
Private Sub Class_Initialize()
Set MyExcel = Application
booAllowSave = False
End Sub
Private Sub Class_Terminate()
Set MyExcel = Nothing
End Sub

Now go to the top of the class code screen and select MyExcel from the
drop-down list of objects, and then WorkbookBeforeClose from the list of
available events. Now include this code in the newly created sub:-

booAllowSave = False

Now choose MyExcel again from the object list and WorkbookBeforeSave from
the list of events. Include the following code within the newly created sub:-

If booAllowSave = False Then
Cancel = True
End If

Recompile the code and save the workbook. Close the workbook and see if it
works. In the mean-time I will see if I can find out the correct declaration
just in case you can't get the above to work.

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk


"Mr. Burton" wrote:

Hi, I've entered what you wrote and I get a compile error on 2 lines:

Private Sub MyExcel_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)

&

Private Sub MyExcel_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI
As Boolean, Cancel As Boolean)

Thanks for helping

"Alan Moseley" wrote:

Try this. In the VBA editor add a new Class Module called Class1. Insert
the following code into it:-

Dim WithEvents MyExcel As Application
Dim booAllowSave As Boolean
Private Sub Class_Initialize()
Set MyExcel = Application
booAllowSave = False
End Sub
Private Sub Class_Terminate()
Set MyExcel = Nothing
End Sub
Private Sub MyExcel_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
booAllowSave = False
End Sub
Private Sub MyExcel_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI
As Boolean, Cancel As Boolean)
If booAllowSave = False Then
Cancel = True
End If
End Sub

Now insert a new module and add the following code into it:-

Dim MyClass As Class1
Public Sub Auto_Open()
Set MyClass = New Class1
End Sub
Public Sub Auto_Close()
Set MyClass = Nothing
End Sub

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk


"Mr. Burton" wrote:

Hi, I need to get rid of the save option. I only want the sheet able save
when the document is closed and it askes if you want to save. I do not want
the document to be saved any other time.

Here is my document (Office 2007 Only).

http://www.savefile.com/files/1848919

It has been made to book out computer and laptop suites.
Trouble is, some people delete others booked sessions and put their own in.

So I have a macro that locks a cell after data is entered into the cell. As
the sheet is protected anybody trying to delete it afterwards cannot.

Trouble was they would have to enable macros to use the macro. That's like
asking a thief to leave his address when he robs a bank.
So the important sheets are veryhidden & I have another macro that unhides
them when you enable macros. Therefore they cannot access the other sheets
until macros are enabled.

My last problem is that if they enable macros then save when they on the
unhidden sheets & save, then quit without saving they document stays on the
veryhidden sheets. Which is not good as the macros are not enabled.

Gord has tried but been unable to assist and could some other kind soul have
a look.Thanks

That worked perfectly

Great thanks alot. A big help.

"Alan Moseley" wrote:

Sorry, I am using Outlook 2003 so we may not have the declaration correct.
Remove everything from within the class module except for the following:-

Dim WithEvents MyExcel As Application
Dim booAllowSave As Boolean
Private Sub Class_Initialize()
Set MyExcel = Application
booAllowSave = False
End Sub
Private Sub Class_Terminate()
Set MyExcel = Nothing
End Sub

Now go to the top of the class code screen and select MyExcel from the
drop-down list of objects, and then WorkbookBeforeClose from the list of
available events. Now include this code in the newly created sub:-

booAllowSave = False

Now choose MyExcel again from the object list and WorkbookBeforeSave from
the list of events. Include the following code within the newly created sub:-

If booAllowSave = False Then
Cancel = True
End If

Recompile the code and save the workbook. Close the workbook and see if it
works. In the mean-time I will see if I can find out the correct declaration
just in case you can't get the above to work.

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk


"Mr. Burton" wrote:

Hi, I've entered what you wrote and I get a compile error on 2 lines:

Private Sub MyExcel_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)

&

Private Sub MyExcel_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI
As Boolean, Cancel As Boolean)

Thanks for helping

"Alan Moseley" wrote:

Try this. In the VBA editor add a new Class Module called Class1. Insert
the following code into it:-

Dim WithEvents MyExcel As Application
Dim booAllowSave As Boolean
Private Sub Class_Initialize()
Set MyExcel = Application
booAllowSave = False
End Sub
Private Sub Class_Terminate()
Set MyExcel = Nothing
End Sub
Private Sub MyExcel_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
booAllowSave = False
End Sub
Private Sub MyExcel_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI
As Boolean, Cancel As Boolean)
If booAllowSave = False Then
Cancel = True
End If
End Sub

Now insert a new module and add the following code into it:-

Dim MyClass As Class1
Public Sub Auto_Open()
Set MyClass = New Class1
End Sub
Public Sub Auto_Close()
Set MyClass = Nothing
End Sub

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk


"Mr. Burton" wrote:

Hi, I need to get rid of the save option. I only want the sheet able save
when the document is closed and it askes if you want to save. I do not want
the document to be saved any other time.

Here is my document (Office 2007 Only).

http://www.savefile.com/files/1848919

It has been made to book out computer and laptop suites.
Trouble is, some people delete others booked sessions and put their own in.

So I have a macro that locks a cell after data is entered into the cell. As
the sheet is protected anybody trying to delete it afterwards cannot.

Trouble was they would have to enable macros to use the macro. That's like
asking a thief to leave his address when he robs a bank.
So the important sheets are veryhidden & I have another macro that unhides
them when you enable macros. Therefore they cannot access the other sheets
until macros are enabled.

My last problem is that if they enable macros then save when they on the
unhidden sheets & save, then quit without saving they document stays on the
veryhidden sheets. Which is not good as the macros are not enabled.

Gord has tried but been unable to assist and could some other kind soul have
a look.Thanks