Have a function that copies what a user inputs in one cell to the adjacent
cell if certain conditions are met. Works fine when I type the value into
the cell, but when I select the value from a validation list I recieve the
error 1004. Simplified the condition on the code below. When I place a
break on .Value = Target.Value, Target.Value contains the value selected from
the validation list.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ERR_CleanUp
Dim i_row As Integer
Dim i_column As Integer

Application.DisplayAlerts = False
Application.EnableEvents = False

i_row = Target.Row
i_column = Target.Column
If Target.Address = "$P$5" Then
With Worksheets(1).Cells(i_row, i_column + 1)
.Value = Target.Value
End With
End If
CleanUp:
Application.DisplayAlerts = True
Application.EnableEvents = True
Exit Sub
ERR_CleanUp:

If Err.Number = 1004 Then
MsgBox "Excel has had a brain fart.", vbInformation
Else
MsgBox Err.Number & "- " & Err.Description
End If
Resume CleanUp
End Sub

Try changing $P$5 to Cells(5, 16). That will put your taget and your offset
in the same type cell reference.

"S Isfeld" wrote:

> Have a function that copies what a user inputs in one cell to the adjacent
> cell if certain conditions are met. Works fine when I type the value into
> the cell, but when I select the value from a validation list I recieve the
> error 1004. Simplified the condition on the code below. When I place a
> break on .Value = Target.Value, Target.Value contains the value selected from
> the validation list.
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ERR_CleanUp
> Dim i_row As Integer
> Dim i_column As Integer
>
> Application.DisplayAlerts = False
> Application.EnableEvents = False
>
> i_row = Target.Row
> i_column = Target.Column
> If Target.Address = "$P$5" Then
> With Worksheets(1).Cells(i_row, i_column + 1)
> .Value = Target.Value
> End With
> End If
> CleanUp:
> Application.DisplayAlerts = True
> Application.EnableEvents = True
> Exit Sub
> ERR_CleanUp:
>
> If Err.Number = 1004 Then
> MsgBox "Excel has had a brain fart.", vbInformation
> Else
> MsgBox Err.Number & "- " & Err.Description
> End If
> Resume CleanUp
> End Sub

Give this a whirl...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ERR_CleanUp

Application.EnableEvents = False
If Target.Address = "$P$5" Then Target.offset(0,1).Value = Target.Value
CleanUp:
Application.EnableEvents = True
Exit Sub
ERR_CleanUp:

If Err.Number = 1004 Then
MsgBox "Excel has had a brain fart.", vbInformation
Else
MsgBox Err.Number & "- " & Err.Description
End If
Resume CleanUp
End Sub
--
HTH...

Jim Thomlinson


"S Isfeld" wrote:

> Have a function that copies what a user inputs in one cell to the adjacent
> cell if certain conditions are met. Works fine when I type the value into
> the cell, but when I select the value from a validation list I recieve the
> error 1004. Simplified the condition on the code below. When I place a
> break on .Value = Target.Value, Target.Value contains the value selected from
> the validation list.
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ERR_CleanUp
> Dim i_row As Integer
> Dim i_column As Integer
>
> Application.DisplayAlerts = False
> Application.EnableEvents = False
>
> i_row = Target.Row
> i_column = Target.Column
> If Target.Address = "$P$5" Then
> With Worksheets(1).Cells(i_row, i_column + 1)
> .Value = Target.Value
> End With
> End If
> CleanUp:
> Application.DisplayAlerts = True
> Application.EnableEvents = True
> Exit Sub
> ERR_CleanUp:
>
> If Err.Number = 1004 Then
> MsgBox "Excel has had a brain fart.", vbInformation
> Else
> MsgBox Err.Number & "- " & Err.Description
> End If
> Resume CleanUp
> End Sub

Still the same problem, works fine when I enter the value into the cell but
errors when I pick from the validation list.

"Jim Thomlinson" wrote:

> Give this a whirl...
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ERR_CleanUp
>
> Application.EnableEvents = False
> If Target.Address = "$P$5" Then Target.offset(0,1).Value = Target.Value
> CleanUp:
> Application.EnableEvents = True
> Exit Sub
> ERR_CleanUp:
>
> If Err.Number = 1004 Then
> MsgBox "Excel has had a brain fart.", vbInformation
> Else
> MsgBox Err.Number & "- " & Err.Description
> End If
> Resume CleanUp
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "S Isfeld" wrote:
>
> > Have a function that copies what a user inputs in one cell to the adjacent
> > cell if certain conditions are met. Works fine when I type the value into
> > the cell, but when I select the value from a validation list I recieve the
> > error 1004. Simplified the condition on the code below. When I place a
> > break on .Value = Target.Value, Target.Value contains the value selected from
> > the validation list.
> >
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > On Error GoTo ERR_CleanUp
> > Dim i_row As Integer
> > Dim i_column As Integer
> >
> > Application.DisplayAlerts = False
> > Application.EnableEvents = False
> >
> > i_row = Target.Row
> > i_column = Target.Column
> > If Target.Address = "$P$5" Then
> > With Worksheets(1).Cells(i_row, i_column + 1)
> > .Value = Target.Value
> > End With
> > End If
> > CleanUp:
> > Application.DisplayAlerts = True
> > Application.EnableEvents = True
> > Exit Sub
> > ERR_CleanUp:
> >
> > If Err.Number = 1004 Then
> > MsgBox "Excel has had a brain fart.", vbInformation
> > Else
> > MsgBox Err.Number & "- " & Err.Description
> > End If
> > Resume CleanUp
> > End Sub

In case of situation like Worksheet("data").Range(variable,what could
be the format of value in variable???

thanks.