 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2007diasable "save as"
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. [quoted text clipped - 8 lines] > > I have the code, but where to put it, so that it executes, is my problem. 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  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >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. [quoted text clipped - 8 lines] > > I have the code, but where to put it, so that it executes, is my problem. 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 >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. [quoted text clipped - 8 lines] > > I have the code, but where to put it, so that it executes, is my problem. 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 >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. [quoted text clipped - 8 lines] > > I have the code, but where to put it, so that it executes, is my problem. Thanks guys Your pointers worked like a charm >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. [quoted text clipped - 8 lines] > > I have the code, but where to put it, so that it executes, is my problem. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.