Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
disable save and saveas from menubar and save via command button
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
disable save and saveas from menubar and save via command button
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 Steve E wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
disable save and saveas from menubar and save via command butt
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 "Dave Peterson" wrote: 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 Steve E wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
disable save and saveas from menubar and save via command butt
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" wrote: 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 Steve E wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
disable save and saveas from menubar and save via command butt
But remember that any of your users who read the newsgroups--or can search
google can do the same thing! Steve E wrote: 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" wrote: 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 Steve E wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
disable save and saveas from menubar and save via command butt
Got it.
Really appreciate your help!!! "Dave Peterson" wrote: But remember that any of your users who read the newsgroups--or can search google can do the same thing! Steve E wrote: 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" wrote: 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 Steve E wrote: 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 -- Dave Peterson |
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) | |||
Disable save, save as, but allow save via command button | Excel Programming |