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

[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

 

[Bob Phillips]

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.

 

[Michael Lanier]

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

 

[JLGWhiz]

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

 

[Michael Lanier]

Thanks for your help. I'll be giving it a try shortly.

Michael