Hi Gord

I followed your steps and have the following code in VB but now when I use
"ctrl s" or F12 to test that Save/Save As doesn't work it, crashes Excel.
Any
ideas why? Silena

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Saved = True
ThisWorkbook.Close

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True

End Sub

In place of this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub

I think all you need is:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
End Sub

Leave the other sub as is. None of this will work if macros are not
enabled, so this is not hard to defeat.

--
Jim
"Silena K-K" <(E-Mail Removed)> wrote in message
news:6798F693-FD20-4C1D-85C4-(E-Mail Removed)...
| Hi Gord
|
| I followed your steps and have the following code in VB but now when I use
| "ctrl s" or F12 to test that Save/Save As doesn't work it, crashes Excel.
| Any
| ideas why? Silena
|
| Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
| ThisWorkbook.Saved = True
| ThisWorkbook.Close
|
| End Sub
|
| Private Sub Workbook_BeforeClose(Cancel As Boolean)
| ThisWorkbook.Saved = True
|
| End Sub
|

Thanks Jim for help on the 'save' issue.

I found your reply to disabling the copy command earlier this year ...

Sub DisableCopyCtrl()
Dim Ctrls As CommandBarControls
Dim Ctrl As CommandBarControl
Set Ctrls = CommandBars.FindControls(, 19)
For Each Ctrl In Ctrls
Ctrl.Enabled = False
Next
End Sub

....but haven't been able to get it to work. I pasted it into a general
module and saved it (not sure how to "enable" other than save??)

Can you help with this? Thanks Silena


"Jim Rech" wrote:

> In place of this:
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> ThisWorkbook.Saved = True
> ThisWorkbook.Close
> End Sub
>
> I think all you need is:
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> Cancel = True
> End Sub
>
> Leave the other sub as is. None of this will work if macros are not
> enabled, so this is not hard to defeat.
>
> --
> Jim
> "Silena K-K" <(E-Mail Removed)> wrote in message
> news:6798F693-FD20-4C1D-85C4-(E-Mail Removed)...
> | Hi Gord
> |
> | I followed your steps and have the following code in VB but now when I use
> | "ctrl s" or F12 to test that Save/Save As doesn't work it, crashes Excel.
> | Any
> | ideas why? Silena
> |
> | Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> | ThisWorkbook.Saved = True
> | ThisWorkbook.Close
> |
> | End Sub
> |
> | Private Sub Workbook_BeforeClose(Cancel As Boolean)
> | ThisWorkbook.Saved = True
> |
> | End Sub
> |
>
>
>

Like all code it has to be run to do something<g>.

Since a sub named Auto_Open will execute automatically when Excel opens a
workbook you can cause my sub to be run like this:

Sub Auto_Open()
DisableCopyCtrl
End Sub

This can go in the same standard module. Users can disable macros though.
If they do this code will not run.

--
Jim
"Silena K-K" <(E-Mail Removed)> wrote in message
news:19F43DB6-A094-4E99-924C-(E-Mail Removed)...
| Thanks Jim for help on the 'save' issue.
|
| I found your reply to disabling the copy command earlier this year ...
|
| Sub DisableCopyCtrl()
| Dim Ctrls As CommandBarControls
| Dim Ctrl As CommandBarControl
| Set Ctrls = CommandBars.FindControls(, 19)
| For Each Ctrl In Ctrls
| Ctrl.Enabled = False
| Next
| End Sub
|
| ...but haven't been able to get it to work. I pasted it into a general
| module and saved it (not sure how to "enable" other than save??)
|
| Can you help with this? Thanks Silena
|
|
| "Jim Rech" wrote:
|
| > In place of this:
| >
| > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
| > Boolean)
| > ThisWorkbook.Saved = True
| > ThisWorkbook.Close
| > End Sub
| >
| > I think all you need is:
| >
| > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
| > Boolean)
| > Cancel = True
| > End Sub
| >
| > Leave the other sub as is. None of this will work if macros are not
| > enabled, so this is not hard to defeat.
| >
| > --
| > Jim
| > "Silena K-K" <(E-Mail Removed)> wrote in message
| > news:6798F693-FD20-4C1D-85C4-(E-Mail Removed)...
| > | Hi Gord
| > |
| > | I followed your steps and have the following code in VB but now when I
use
| > | "ctrl s" or F12 to test that Save/Save As doesn't work it, crashes
Excel.
| > | Any
| > | ideas why? Silena
| > |
| > | Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
| > Boolean)
| > | ThisWorkbook.Saved = True
| > | ThisWorkbook.Close
| > |
| > | End Sub
| > |
| > | Private Sub Workbook_BeforeClose(Cancel As Boolean)
| > | ThisWorkbook.Saved = True
| > |
| > | End Sub
| > |
| >
| >
| >