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..

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

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