Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable save, save as, but allow save via command button
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable save, save as, but allow save via command button
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 TimN wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable save, save as, but allow save via command button
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 Die_Another_Day wrote: 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 TimN wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable save, save as, but allow save via command button
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 Boolean Sub MySaveMacro() MySave = True ActiveWorkbook.SaveAs BlahBlahBlah End Sub Does 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" wrote: 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 Die_Another_Day wrote: 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 TimN wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable save, save as, but allow save via command button
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 wrote: 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 Boolean Sub MySaveMacro() MySave = True ActiveWorkbook.SaveAs BlahBlahBlah End Sub Does 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" wrote: 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 Die_Another_Day wrote: 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 TimN wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable save, save as, but allow save via command button
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" wrote: 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 wrote: 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 Boolean Sub MySaveMacro() MySave = True ActiveWorkbook.SaveAs BlahBlahBlah End Sub Does 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" wrote: 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 Die_Another_Day wrote: 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 TimN wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable save, save as, but allow save via command button
My bad, MySave Must be public ie.
Public MySave as Boolean instead of: Dim MySave as Boolean Charles Chickering TimN wrote: 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" wrote: 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 wrote: 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 Boolean Sub MySaveMacro() MySave = True ActiveWorkbook.SaveAs BlahBlahBlah End Sub Does 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" wrote: 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 Die_Another_Day wrote: 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 TimN wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable save, save as, but allow save via command button
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:=(.Wor ksheets("STD Calc") _ .Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas) If rFound Is Nothing Then Set rCell = ..Worksheets("Data").Range("A65536").End(xlUp).Off set(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" wrote: My bad, MySave Must be public ie. Public MySave as Boolean instead of: Dim MySave as Boolean Charles Chickering TimN wrote: 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" wrote: 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 wrote: 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 Boolean Sub MySaveMacro() MySave = True ActiveWorkbook.SaveAs BlahBlahBlah End Sub Does 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" wrote: 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 Die_Another_Day wrote: 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 TimN wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable save, save as, but allow save via command button
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 wrote: 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:=(.Work sheets("STD Calc") _ .Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas) If rFound Is Nothing Then Set rCell = .Worksheets("Data").Range("A65536").End(xlUp).Offs et(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" wrote: My bad, MySave Must be public ie. Public MySave as Boolean instead of: Dim MySave as Boolean Charles Chickering TimN wrote: 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" wrote: 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 wrote: 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 Boolean Sub MySaveMacro() MySave = True ActiveWorkbook.SaveAs BlahBlahBlah End Sub Does 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" wrote: 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 Die_Another_Day wrote: 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 TimN wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable save, save as, but allow save via command button
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. "Die_Another_Day" wrote: 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 wrote: 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:=(.Work sheets("STD Calc") _ .Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas) If rFound Is Nothing Then Set rCell = .Worksheets("Data").Range("A65536").End(xlUp).Offs et(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" wrote: My bad, MySave Must be public ie. Public MySave as Boolean instead of: Dim MySave as Boolean Charles Chickering TimN wrote: 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" wrote: 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 wrote: 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 Boolean Sub MySaveMacro() MySave = True ActiveWorkbook.SaveAs BlahBlahBlah End Sub Does 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" wrote: 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 Die_Another_Day wrote: 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 TimN wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable save, save as, but allow save via command button
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 "Die_Another_Day" wrote: 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 wrote: 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:=(.Work sheets("STD Calc") _ .Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas) If rFound Is Nothing Then Set rCell = .Worksheets("Data").Range("A65536").End(xlUp).Offs et(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" wrote: My bad, MySave Must be public ie. Public MySave as Boolean instead of: Dim MySave as Boolean Charles Chickering TimN wrote: 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" wrote: 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 wrote: 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 Boolean Sub MySaveMacro() MySave = True ActiveWorkbook.SaveAs BlahBlahBlah End Sub Does 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" wrote: 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 Die_Another_Day wrote: 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 TimN wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disable the save button | Excel Discussion (Misc queries) | |||
Disable 'save' command in Excel 2000 | Excel Discussion (Misc queries) | |||
Can I disable the Save command for an Excel file? | Excel Discussion (Misc queries) | |||
Command Button Save As Application.ExecuteExcel4Macro ("SAVE.AS?() | Excel Discussion (Misc queries) | |||
How to diasble save and save as menu but allow a save button | Excel Programming |