Prev: Hiding Rows Using IF Statement
Next: COM add-ins
From: Michael Lanier on 4 Apr 2010 17:19
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
From: Bob Phillips on 4 Apr 2010 17:25
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.



--

HTH

Bob

"Michael Lanier" <michaelrlanier(a)gmail.com> wrote in message
news:8cba8cd4-c265-4b7e-9052-a2de53f43934(a)5g2000yqj.googlegroups.com...
> 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


From: Michael Lanier on 4 Apr 2010 17:58
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
From: JLGWhiz on 4 Apr 2010 19:42
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" <michaelrlanier(a)gmail.com> wrote in message
news:693b9f59-3a77-48f0-a68d-5ef22431cbfe(a)q23g2000yqd.googlegroups.com...
> 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


From: Michael Lanier on 5 Apr 2010 06:34
Thanks for your help. I'll be giving it a try shortly.

Michael
 | 
Pages: 1
Prev: Hiding Rows Using IF Statement
Next: COM add-ins