Need help writing a macro to Save changes in a worksheet
 

I am using MSExcel 2003

I am trying to write an event macro to save the worksheet after someone
enters a number into a given cell. I have two ranges named "TotalYield" and
"Cummulative". The first is a range of 4 merged cells, the second is a range
of single cells.

So far I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Me.Range("TotalYield")) Is Nothing
Then

ActiveWorkbook.save

End If

End Sub

But the problem is that the worksheet is saved when the range is selected,
not after an entry is made.

Once I have this working for the TotalYield range, I need to do the same for
the Cummulative range.

Any help would be great. Thanks!

Need help writing a macro to Save changes in a worksheet
 

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Me.Range("A1")) Is Nothing Then

ActiveWorkbook.Save

End If
End Sub

"TomK76" wrote:

I am using MSExcel 2003

I am trying to write an event macro to save the worksheet after someone
enters a number into a given cell. I have two ranges named "TotalYield" and
"Cummulative". The first is a range of 4 merged cells, the second is a range
of single cells.

So far I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Me.Range("TotalYield")) Is Nothing
Then

ActiveWorkbook.save

End If

End Sub

But the problem is that the worksheet is saved when the range is selected,
not after an entry is made.

Once I have this working for the TotalYield range, I need to do the same for
the Cummulative range.

Any help would be great. Thanks!

Need help writing a macro to Save changes in a worksheet
 

Mike,

Replacing "TotalYield" with "A1" as you suggested had no effect.

"Mike" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Me.Range("A1")) Is Nothing Then

ActiveWorkbook.Save

End If
End Sub

"TomK76" wrote:

I am using MSExcel 2003

I am trying to write an event macro to save the worksheet after someone
enters a number into a given cell. I have two ranges named "TotalYield" and
"Cummulative". The first is a range of 4 merged cells, the second is a range
of single cells.

So far I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Me.Range("TotalYield")) Is Nothing
Then

ActiveWorkbook.save

End If

End Sub

But the problem is that the worksheet is saved when the range is selected,
not after an entry is made.

Once I have this working for the TotalYield range, I need to do the same for
the Cummulative range.

Any help would be great. Thanks!

Need help writing a macro to Save changes in a worksheet
 

Mike also changed the event to worksheet_change.

ps. I'd use:

me.parent.save
instead of relying on the activesheet.

TomK76 wrote:

Mike,

Replacing "TotalYield" with "A1" as you suggested had no effect.

"Mike" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Me.Range("A1")) Is Nothing Then

ActiveWorkbook.Save

End If
End Sub

"TomK76" wrote:

I am using MSExcel 2003

I am trying to write an event macro to save the worksheet after someone
enters a number into a given cell. I have two ranges named "TotalYield" and
"Cummulative". The first is a range of 4 merged cells, the second is a range
of single cells.

So far I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Me.Range("TotalYield")) Is Nothing
Then

ActiveWorkbook.save

End If

End Sub

But the problem is that the worksheet is saved when the range is selected,
not after an entry is made.

Once I have this working for the TotalYield range, I need to do the same for
the Cummulative range.

Any help would be great. Thanks!



--

Dave Peterson

Need help writing a macro to Save changes in a worksheet
 

Dave and Mike,

I had not noticed the change in the first line. I did have to change the
"A1" to my specific named range, but it looks to be working.

Dave if you have a second, what is the advantage of using the Me.Parent.Save?

Thanks guys, I appreciate it!

"Dave Peterson" wrote:

Mike also changed the event to worksheet_change.

ps. I'd use:

me.parent.save
instead of relying on the activesheet.

TomK76 wrote:

Mike,

Replacing "TotalYield" with "A1" as you suggested had no effect.

"Mike" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Me.Range("A1")) Is Nothing Then

ActiveWorkbook.Save

End If
End Sub

"TomK76" wrote:

I am using MSExcel 2003

I am trying to write an event macro to save the worksheet after someone
enters a number into a given cell. I have two ranges named "TotalYield" and
"Cummulative". The first is a range of 4 merged cells, the second is a range
of single cells.

So far I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Me.Range("TotalYield")) Is Nothing
Then

ActiveWorkbook.save

End If

End Sub

But the problem is that the worksheet is saved when the range is selected,
not after an entry is made.

Once I have this working for the TotalYield range, I need to do the same for
the Cummulative range.

Any help would be great. Thanks!



--

Dave Peterson