Automatically Put a Value in a specific cell.
 

Here is what I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("S7:S37"), Target) Is Nothing Then
Worksheets("Daily Log of Students Seen").Range("S38").Value =
ActiveCell.Value
End If
End Sub

But, this causes Cell S38 to equal the cell in the target range that is
entered. I actually want something else to happen. Once I intercept a cell
in the target range and change it, I want Cell S38 to take the value of the
changed cell once I exit it.


--
Thanks
Shawn

If you want S38 of the daily log sheet to change, maybe using the
worksheet_change event would be a better choice:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub 'ok if more than one cell changed?

On Error GoTo ErrHandler:
If Intersect(Me.Range("S7:S37"), Target) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
Worksheets("Daily Log of Students Seen").Range("S38").Value _
= Target.Value
End If

ErrHandler:
Application.EnableEvents = True

End Sub


I'm guessing that "daily log" and the current sheet are different. If they're
not, then maybe:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub 'ok if more than one cell changed?

On Error GoTo ErrHandler:
If Intersect(Me.Range("S7:S37"), Target) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
Me.Range("S38").Value = Target.Value
End If

ErrHandler:
Application.EnableEvents = True

End Sub


Shawn wrote:

Here is what I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("S7:S37"), Target) Is Nothing Then
Worksheets("Daily Log of Students Seen").Range("S38").Value =
ActiveCell.Value
End If
End Sub

But, this causes Cell S38 to equal the cell in the target range that is
entered. I actually want something else to happen. Once I intercept a cell
in the target range and change it, I want Cell S38 to take the value of the
changed cell once I exit it.

--
Thanks
Shawn

--

Dave Peterson

Shawn

Don't use selectionchange but simply change event and Target.Value

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Application.Intersect(Range("S7:S37"), Target) Is Nothing Then
Worksheets("Daily Log of Students Seen").Range("S38").Value = _
Target.Value
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben Excel MVP

On Mon, 23 May 2005 10:10:48 -0700, "Shawn"
wrote:

Here is what I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("S7:S37"), Target) Is Nothing Then
Worksheets("Daily Log of Students Seen").Range("S38").Value =
ActiveCell.Value
End If
End Sub

But, this causes Cell S38 to equal the cell in the target range that is
entered. I actually want something else to happen. Once I intercept a cell
in the target range and change it, I want Cell S38 to take the value of the
changed cell once I exit it.