disable save and saveas from menubar and save via command button

[Guest]

OK... truly stupid situation:

My Excel2003 workbook project has the save and saveas menu commands disabled
via this code in the ThisWorkbook module:

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

In my worksheet I have a button that when clicked runs a routine to make
sure that all of the info is entered and is error trapped, saves a copy of
the workbook and emails it to a database application that records the data.
Works like a champ thanks to the help of Ron DeBruin's site...

Unfortunately, I don't have a way of saving a "blank" workbook to use as the
master file... (blank meaning without the user inputs)... so I thought I'd
try and set another button well out of the visible display area and assign a
similar bit of code to save the workbook (not a copy) while I'm working on it
and before the user inputs their data...

so I have this in the same worksheet as the other event code but this
doesn't seem to do anything at all... obviously I'm missing something simple
but am just too dense to see what it might be.


Public MySave As Boolean 'at top of worksheet module before any other code

Public Sub SaveWorkbook() 'assigned to form button on worksheet
Dim wb As Workbook
Dim Cancel As Boolean

Set wb = ActiveWorkbook

MySave = True
wb.SaveAs "filename"
If Not MySave Then
Cancel = True
Else
MySave = False
End If


End Sub

Help?

TIA,

Steve

 

[Dave Peterson]

I think I'd just disable events when I was doing the save:

Public Sub SaveWorkbook() 'assigned to form button on worksheet
application.enableevents = false
thisworkbook.save '.saveas??? or show a dialog?
application.enableevents = true
End Sub

===
Another way to do the same thing without the macro is to issue the commands from
the immediate window of the VBE:

hit alt-f11 to get to the VBE
hit ctrl-g to get to the immediate window
application.enableevents = false

Back to excel and do any save you want.

Then back to the VBE's immediate window:
application.enableevents = true

Then you don't have to worry about someone finding that button--you only have to
worry about someone disabling event handling!

ps. I'm not sure what MySave does in this code:

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

I would think that this would be sufficient:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
cancel = true
msgbox "Use the correct button to save!!!"
End Sub

 

[Guest]

Dave,

So... all I should have to do is go to the immediate window and disable
events... go to my excel sheet and make the save?

Unbelievable...

I'll try that and then heap the praise in your general direction.

TIA

Steve

 

[Guest]

Dave!

Perfection. A simple solution to my stupid situation.

I can't tell you how much I appreciate the simplicity of just using the
immediate window... and... now I know what it can be used for!

THANKS!!!

Steve

 

[Dave Peterson]

But remember that any of your users who read the newsgroups--or can search
google can do the same thing!

 

[Guest]

Got it.

Really appreciate your help!!!

But remember that any of your users who read the newsgroups--or can search
google can do the same thing!