Make a file save and close itself based on the value of a cell

  • Thread starter Thread starter Michael Lanier
  • Start date Start date
M

Michael Lanier

If Sheet1!A1's value is = 1, then I would like for the file to save
and then close itself. Is this possible? Thanks in advance.

Michael
 
Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

If Target.Value = 1 Then

Parent.Save
Parent.Close
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
 
Bob,

Thanks for your help. I'm at a bit of a loss. I placed your macro in
a new file. I then assigned the file a name and saved it. When I
triggered the event by enter the number 1 in A1 and it saved the file
as intended. When I reopened it and made a minor change and then
reentered the 1 value in A1, nothing happened. Do you have any
further ideas? Thanks.

Michael
 
Hi Michael, the way the macro is set up, it will close without enabling
events again, so when you re-open the file the events are disabled. There
are a couple of ways to get around this. Below is my suggestion:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A2" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

If Target.Value = 1 Then

Parent.Save
Application.EnableEvents = True '<<<add this line
Parent.Close
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub
 
Back
Top