Disable save, save as, but allow save via command button

[TimN]

All,

I have been searching through the archives, but can't locate an answer for
this.

I have a command button that when clicked saves the file as the name in cell
B1.
I also want to disable the Save, Save As and save button on the toolbar. To
accomplish that I located the code below. However when I insert that code,
my command button is also disabled. How can I disable the Save, Save As and
save button on the toolbar but still allow my Save button (command button) to
work?

Please help. Thanks

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wb As Workbook
Set wb = ActiveWorkbook
Cancel = True
End Sub

 

[Die_Another_Day]

Create a global variable, then set it to true when you use your command
button Like this:
Dim MySave as Boolean

Sub MySaveMacro()
MySave = True
ActiveWorkbook.SaveAs BlahBlahBlah
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim wb As Workbook
Set wb = ActiveWorkbook

If Not MySave Then Cancel = True
End Sub

HTH

Charles Chickering

 

[Die_Another_Day]

Oops you also need to set the Boolean back to false after you save.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim wb As Workbook
Set wb = ActiveWorkbook


If Not MySave Then Cancel = True
MySave = False
End Sub

Charles

 

[TimN]

Couple of questions:

When you say create a global variable what do i need to do and where do i
define that?
Also, the code:
Dim MySave as BooleanDoes that go in the click event for the command button? And the other code
goes in "This Worbook" under the before Save Event?

Sorry for the dumb questions, I am just trying to sort all this out.

 

[Die_Another_Day]

To create a global variable, create a normal module, then at the very
top, before any other sub routines place the code:
Dim MySave as Boolean
This Dims MySave as a Boolean that can be accessed from anywhere in the
VBProject.
The Code from the "MySaveMacro" can go in your CommandButton_Click
Event. All you are doing is using that Global variable to tell the
"BeforeSave" event that you clicked your button, the way this works is
that all Booleans are defaulted to false when the code runs, in your
CommandButton_Click we set the Boolean to true:
MySave = True
then we tell it to save. This triggers the "BeforeSave" Event, which
with this line:
If Not MySave Then Cancel = True
checks to see if "MySave" has been set to true, if not then cancel the
save. Now we need to reset MySave so that it won't get triggered again
without your button like this:
MySave = False

And yes the Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As Boolean) code does go in the thisworkbook.

HTH

Charles

 

[TimN]

OK,

I made the changes and I get a Compile Error Variable not defined. It
highlights the line "MySave = True" in the command button click event. What
am I doing wrong??

 

[Die_Another_Day]

My bad, MySave Must be public ie.
Public MySave as Boolean
instead of:
Dim MySave as Boolean


Charles Chickering

 

[TimN]

Charles,

It won't save a copy when I click my command button. Below is my code. Any
suggestions?

In a seperate module i have only the following:
Public MySave As Boolean

In the sheet1 click event i have this code for command button:
Private Sub CommandButton4_Click()
'Command Button to save and copy
'rCell Makes a copy of the initial calculations and saves to the Data
worksheet
'rFound looks for a duplicate date and if found copies over it else copies
to next avail row

MySave = True
Dim rCell As Range
Dim rFound As Range
With Application.ThisWorkbook
Set rFound =
..Worksheets("Data").Columns("B").Find(What:=(.Worksheets("STD Calc") _
.Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas)
If rFound Is Nothing Then
Set rCell =
..Worksheets("Data").Range("A65536").End(xlUp).Offset(1, 0)
Else
Set rCell = rFound.Offset(-3, -1)
End If
Worksheets("STD Calc").Range("B17:Q37").Copy
rCell.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With

'the following opens the "Save As" MsgBox in Excel so the user
'can save to the location they desire. File is saved as name of employee in
cell C2
Dim RetVal As Variant
RetVal = Application.GetSaveAsFilename(Range("C2"))

If RetVal <> False Then
ThisWorkbook.SaveAs RetVal & "xls"

End If

End Sub

And Finally the code in "This Workbook":

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Removes the save button from the toolbar and removes save from file menu
'User should only save by using save command button
Dim MySave As Boolean
Dim wb As Workbook
Set wb = ActiveWorkbook
If Not MySave Then Cancel = True
MySave = False
End Sub

 

[Die_Another_Day]

Tim, set a breakpoint at the *** line:
And Finally the code in "This Workbook":


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'Removes the save button from the toolbar and removes save from file
menu
'User should only save by using save command button
Dim MySave As Boolean
Dim wb As Workbook
Set wb = ActiveWorkbook
***If Not MySave Then Cancel = True
MySave = False
End Sub

Then in the Immediate window type:
?MySave
If the code doesn't stop there then make sure Events are enabled by
typing this into the Immediate window:
Application.EnableEvents = True

Charles

 

[TimN]

Charles,
I did as you indicated. The result is after adding a breakpoint at the ***
line and running the macro, I get a yellow arrow and yellow highlight on the
same line as the breakpoint. I assume that means that is the line causing
the problem?

Also when I type ?MySave in the Immediate window and hit enter, False
returns in the next line.

 

[TimN]

FYI...

I got it to work with the following changes.

Thanks for your assistance.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Removes the save button from the toolbar and removes save from file menu
'User should only save by using save command button

If Not MySave Then
Cancel = True
Else
MySave = False
End If
End Sub