diasable "save as"

  • Thread starter Thread starter Will Sellers
  • Start date Start date
W

Will Sellers

I setup a vb that disables the save as on the file menu.
the code works as expected. But only if I click on run in the VB editor.
In general I have
sub
command saveas enable=false (not exact syntax )
end sub

How do I get this to execute when I open the workbook?
When I close the workbook I want to reverse the code so that the next
workbookthat is opened does not have saveas greyed.

I have the code, but where to put it, so that it executes, is my problem.
 
I setup a vb that disables the save as on the file menu.
the code works as expected. But only if I click on run in the VB editor.
In general I have
sub
command saveas enable=false (not exact syntax )
end sub

How do I get this to execute when I open the workbook?
When I close the workbook I want to reverse the code so that the next
workbookthat is opened does not have saveas greyed.

I have the code, but where to put it, so that it executes, is my problem.
Click to expand...

I'm not sure about this, but in the VB editor, double-click the ThisWorkbook
item in the VBA Project Explorer tree (hit Ctrl+R if it is not showing) and
select Workbook from the left-hand drop down on the code editing window that
appears. In the right-hand drop down, select the Open event. Put your call
to you subroutine there. Now, save the project and close the workbook. Now,
when you reopen it, I THINK the SaveAs option will be disabled.

Rick
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Worksheet Menu
Bar").Controls("File").Controls("Save As...").Enabled = True
End Sub

Private Sub Workbook_Open()
Application.CommandBars("Worksheet Menu
Bar").Controls("File").Controls("Save As...").Enabled = False
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Will,

Better would be to use the before save event. Put this into the codemodule
of the ThisWorkbook object:

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
Msgbox "Sorry, you can only save me with my original name and path."
Cancel = True
End If
End Sub

HTH,
Bernie
MS Excel MVP
 
If the user disables macros when they open the workbook, they can do
whatever they want.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
Back
Top