I am trying to write a script starting with

Private Sub Worksheet_Change(ByVal Target as Range)

To save the worksheet when any cell in a range is changed. However,
after the script is put into the VB code section, somehow the macro
does not appear in the list of macros for the particular worksheet.
Somehow my script is not recognized as a macro. Can someone please
tell me what I have to do to make sure the thing appears as a macro.
Many thanks in advance.

explained :
I am trying to write a script starting with

Private Sub Worksheet_Change(ByVal Target as Range)

To save the worksheet when any cell in a range is changed. However,
after the script is put into the VB code section, somehow the macro
does not appear in the list of macros for the particular worksheet.
Somehow my script is not recognized as a macro. Can someone please
tell me what I have to do to make sure the thing appears as a macro.
Many thanks in advance.

Worksheet event procedures will never appear in the Macros dialog
because they aren't executable that way. Events respond to user actions
in the UI or by VBA code if the procedure works such that it causes a
worksheet event to fire.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jun 14, 2:29*pm, GS wrote:
explained :

I am trying to write a script starting with

Private Sub Worksheet_Change(ByVal Target as Range)

To save the worksheet when any cell in a range is changed. *However,
after the script is put into the VB code section, somehow the macro
does not appear in the list of macros for the particular worksheet.
Somehow my script is not recognized as a macro. *Can someone please
tell me what I have to do to make sure the thing appears as a macro.
Many thanks in advance.

Worksheet event procedures will never appear in the Macros dialog
because they aren't executable that way. Events respond to user actions
in the UI or by VBA code if the procedure works such that it causes a
worksheet event to fire.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

OK Garry many thanks. Suppose I wanted the worksheet to be saved if
some cell in A1:A5 is changed. What would the script look like, and
how would it be triggered. Thanks for helping my out.

on 6/14/2011, programmernovice supposed :
On Jun 14, 2:29*pm, GS wrote:
explained :

I am trying to write a script starting with
Private Sub Worksheet_Change(ByVal Target as Range)
To save the worksheet when any cell in a range is changed. *However,
after the script is put into the VB code section, somehow the macro
does not appear in the list of macros for the particular worksheet.
Somehow my script is not recognized as a macro. *Can someone please
tell me what I have to do to make sure the thing appears as a macro.
Many thanks in advance.

Worksheet event procedures will never appear in the Macros dialog
because they aren't executable that way. Events respond to user actions
in the UI or by VBA code if the procedure works such that it causes a
worksheet event to fire.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

OK Garry many thanks. Suppose I wanted the worksheet to be saved if
some cell in A1:A5 is changed. What would the script look like, and
how would it be triggered. Thanks for helping my out.

Try...

Private Sub Worksheet_Change(ByVal Target as Range)
If Not Intersect(Target, Range("$A$1:$A$5")) Is Nothing Then _
ThisWorkbook.Save
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jun 14, 3:52*pm, GS wrote:
on 6/14/2011, programmernovice supposed :





On Jun 14, 2:29*pm, GS wrote:
explained :

I am trying to write a script starting with
Private Sub Worksheet_Change(ByVal Target as Range)
To save the worksheet when any cell in a range is changed. *However,
after the script is put into the VB code section, somehow the macro
does not appear in the list of macros for the particular worksheet.
Somehow my script is not recognized as a macro. *Can someone please
tell me what I have to do to make sure the thing appears as a macro.
Many thanks in advance.

Worksheet event procedures will never appear in the Macros dialog
because they aren't executable that way. Events respond to user actions
in the UI or by VBA code if the procedure works such that it causes a
worksheet event to fire.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

OK Garry many thanks. *Suppose I wanted the worksheet to be saved if
some cell in *A1:A5 is changed. *What would the script look like, and
how would it be triggered. *Thanks for helping my out.

Try...

Private Sub Worksheet_Change(ByVal Target as Range)
* If Not Intersect(Target, Range("$A$1:$A$5")) Is Nothing Then _
* * ThisWorkbook.Save
End Sub

--
Garry

Many thanks Garry, really apreciated it.

programmernovice formulated on Tuesday :
On Jun 14, 3:52*pm, GS wrote:
on 6/14/2011, programmernovice supposed :





On Jun 14, 2:29*pm, GS wrote:
explained :

I am trying to write a script starting with
Private Sub Worksheet_Change(ByVal Target as Range)
To save the worksheet when any cell in a range is changed. *However,
after the script is put into the VB code section, somehow the macro
does not appear in the list of macros for the particular worksheet.
Somehow my script is not recognized as a macro. *Can someone please
tell me what I have to do to make sure the thing appears as a macro.
Many thanks in advance.

Worksheet event procedures will never appear in the Macros dialog
because they aren't executable that way. Events respond to user actions
in the UI or by VBA code if the procedure works such that it causes a
worksheet event to fire.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
OK Garry many thanks. *Suppose I wanted the worksheet to be saved if
some cell in *A1:A5 is changed. *What would the script look like, and
how would it be triggered. *Thanks for helping my out.

Try...

Private Sub Worksheet_Change(ByVal Target as Range)
* If Not Intersect(Target, Range("$A$1:$A$5")) Is Nothing Then _
* * ThisWorkbook.Save
End Sub

--
Garry

Many thanks Garry, really apreciated it.

You're welcome! ..always glad to help!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jun 14, 3:52*pm, GS wrote:
on 6/14/2011,programmernovicesupposed :





On Jun 14, 2:29*pm, GS wrote:
explained :

I am trying to write a script starting with
Private Sub Worksheet_Change(ByVal Target as Range)
To save the worksheet when any cell in a range is changed. *However,
after the script is put into the VB code section, somehow the macro
does not appear in the list of macros for the particular worksheet.
Somehow my script is not recognized as a macro. *Can someone please
tell me what I have to do to make sure the thing appears as a macro.
Many thanks in advance.

Worksheet event procedures will never appear in the Macros dialog
because they aren't executable that way. Events respond to user actions
in the UI or by VBA code if the procedure works such that it causes a
worksheet event to fire.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

OK Garry many thanks. *Suppose I wanted the worksheet to be saved if
some cell in *A1:A5 is changed. *What would the script look like, and
how would it be triggered. *Thanks for helping my out.

Try...

Private Sub Worksheet_Change(ByVal Target as Range)
* If Not Intersect(Target, Range("$A$1:$A$5")) Is Nothing Then _
* * ThisWorkbook.Save
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Hi Garry, unfortunately after putting this in nothing happens. I
suspect it has to do with the definition of "target", I am defining a
range of cells in the worksheet as "target", but apparently this
doesn't help. Any ideas? Thanks again for your patience.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("$A$1:$A$5")) Is Nothing Then
ThisWorkbook.Save
End If
End Sub


Gord Dibben MS Excel MVP


On Fri, 17 Jun 2011 12:08:52 -0700 (PDT), programmernovice
wrote:

Hi Garry, unfortunately after putting this in nothing happens. I
suspect it has to do with the definition of "target", I am defining a
range of cells in the worksheet as "target", but apparently this
doesn't help. Any ideas? Thanks again for your patience.

After serious thinking programmernovice wrote :
Hi Garry, unfortunately after putting this in nothing happens. I
suspect it has to do with the definition of "target", I am defining a
range of cells in the worksheet as "target", but apparently this
doesn't help. Any ideas? Thanks again for your patience.

In this event handler, 'Target' holds a ref to the cell that changed.
What the code does is to check if that cell is within your criteria
Range("$A$1:$A$5"). You'll need to revise this ("$A$1:$A$5") to be the
range where you want to monitor for any changes made to its cells.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc