I have the following piece of code. The Msgbox prints correctly, whereas the
very next line gives the error:
1004: Application defined or object-defined error


Private Sub Worksheet_Change(ByVal Target As Range)

myRow = Target.Row
MsgBox Cells(myRow, "E") ' prints the message
correctly.
Cells(myRow, "E") = "=$E$3" ' throws the error


Anybody has any idea why I am getting this error.


--
- Mangesh
------------------------------------------
Office 2003 & Windows XP

forgot to mention something. The cell I am changing has a list validation.
If I change it manually, no error, whereas if I change it by clicking on the
drop-down list (data validation), I get the error.

Mangesh


"Mangesh Yadav" wrote in message
...
I have the following piece of code. The Msgbox prints correctly, whereas
the
very next line gives the error:
1004: Application defined or object-defined error


Private Sub Worksheet_Change(ByVal Target As Range)

myRow = Target.Row
MsgBox Cells(myRow, "E") ' prints the message
correctly.
Cells(myRow, "E") = "=$E$3" ' throws the error


Anybody has any idea why I am getting this error.


--
- Mangesh
------------------------------------------
Office 2003 & Windows XP

Mangesh,

I tried your code - it did not give any error. It kept redisplaying the
message box since you do not turn off the Application.EnableEvents property
before making a change.

Also just some comments on your code .. it is preferable to not use Cells -
as that is an unqualified reference and has given me some problems in the
past.

With ActiveSheet (or a specific sheet)
.Cells(....)
end with

is preferable.

Alok

"Mangesh Yadav" wrote:

forgot to mention something. The cell I am changing has a list validation.
If I change it manually, no error, whereas if I change it by clicking on the
drop-down list (data validation), I get the error.

Mangesh


"Mangesh Yadav" wrote in message
...
I have the following piece of code. The Msgbox prints correctly, whereas
the
very next line gives the error:
1004: Application defined or object-defined error


Private Sub Worksheet_Change(ByVal Target As Range)

myRow = Target.Row
MsgBox Cells(myRow, "E") ' prints the message
correctly.
Cells(myRow, "E") = "=$E$3" ' throws the error


Anybody has any idea why I am getting this error.


--
- Mangesh
------------------------------------------
Office 2003 & Windows XP

Hi Alok,

Thanks for the response. Actually I am close to isolating the error. I have
one UDF which if not used in a particular cell, then the error does not
come.

Also, the error comes only when I choose an item by selecting it with the
mouse, and no error when entering manually in the cell.

Mangesh




"Alok" wrote in message
...
Mangesh,

I tried your code - it did not give any error. It kept redisplaying the
message box since you do not turn off the Application.EnableEvents
property
before making a change.

Also just some comments on your code .. it is preferable to not use
Cells -
as that is an unqualified reference and has given me some problems in the
past.

With ActiveSheet (or a specific sheet)
.Cells(....)
end with

is preferable.

Alok

"Mangesh Yadav" wrote:

forgot to mention something. The cell I am changing has a list
validation.
If I change it manually, no error, whereas if I change it by clicking on
the
drop-down list (data validation), I get the error.

Mangesh


"Mangesh Yadav" wrote in message
...
I have the following piece of code. The Msgbox prints correctly,
whereas
the
very next line gives the error:
1004: Application defined or object-defined error


Private Sub Worksheet_Change(ByVal Target As Range)

myRow = Target.Row
MsgBox Cells(myRow, "E") ' prints the message
correctly.
Cells(myRow, "E") = "=$E$3" ' throws the error


Anybody has any idea why I am getting this error.


--
- Mangesh
------------------------------------------
Office 2003 & Windows XP