Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE AS HELP
I am looking to trigger an event when a user selects save as.
I have a template file with raw data that is manipulated on the overview sheet. I would like to remove the raw data tab when the save as is selected in order to make the file smaller, leaving the template intact.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE AS HELP
This sounds dangerous to the user to me. Maybe they're not done with that sheet
yet. I wouldn't do this, but if you want: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) On Error Resume Next Application.DisplayAlerts = False Me.Worksheets("raw data").Delete Application.DisplayAlerts = True On Error GoTo 0 End Sub This is a workbook event and it goes in the ThisWorkbook module of that workbook's project. Instead I'd provide a dedicated macro that would accomplish the same thing. Then tell the user to run that macro when they're finished with that sheet. Or even ask before deleting??? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim resp As Long Dim wks As Worksheet Dim wksName As String wksName = "Raw Data" Set wks = Nothing On Error Resume Next Set wks = Worksheets(wksName) On Error GoTo 0 If wks Is Nothing Then 'already gone, do nothing Else resp = MsgBox(Prompt:="Want to delete " & wksName, _ Buttons:=vbYesNo) If resp = vbYes Then On Error Resume Next Application.DisplayAlerts = False Me.Worksheets(wksName).Delete Application.DisplayAlerts = True On Error GoTo 0 End If End If End Sub Rpettis31 wrote: I am looking to trigger an event when a user selects save as. I have a template file with raw data that is manipulated on the overview sheet. I would like to remove the raw data tab when the save as is selected in order to make the file smaller, leaving the template intact.. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE AS HELP
Thanks for the information, I decided to keep the Raw data as a seperate
file. So no need to delete. Thanks Robert "Dave Peterson" wrote: This sounds dangerous to the user to me. Maybe they're not done with that sheet yet. I wouldn't do this, but if you want: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) On Error Resume Next Application.DisplayAlerts = False Me.Worksheets("raw data").Delete Application.DisplayAlerts = True On Error GoTo 0 End Sub This is a workbook event and it goes in the ThisWorkbook module of that workbook's project. Instead I'd provide a dedicated macro that would accomplish the same thing. Then tell the user to run that macro when they're finished with that sheet. Or even ask before deleting??? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim resp As Long Dim wks As Worksheet Dim wksName As String wksName = "Raw Data" Set wks = Nothing On Error Resume Next Set wks = Worksheets(wksName) On Error GoTo 0 If wks Is Nothing Then 'already gone, do nothing Else resp = MsgBox(Prompt:="Want to delete " & wksName, _ Buttons:=vbYesNo) If resp = vbYes Then On Error Resume Next Application.DisplayAlerts = False Me.Worksheets(wksName).Delete Application.DisplayAlerts = True On Error GoTo 0 End If End If End Sub Rpettis31 wrote: I am looking to trigger an event when a user selects save as. I have a template file with raw data that is manipulated on the overview sheet. I would like to remove the raw data tab when the save as is selected in order to make the file smaller, leaving the template intact.. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save, save as, page setup dimmed out in unprotected excel sheet? | Excel Discussion (Misc queries) | |||
prompt user to save file as {desired_name} and save it to a variab | Excel Programming | |||
Disable save, save as, but allow save via command button | Excel Programming | |||
How to diasble save and save as menu but allow a save button | Excel Programming | |||
Totally Disabling (^ save ) (Save as) and Save Icon – Which code do I use: | Excel Programming |