Runtime error 1004

[Guest]

Hi all,

I got error message "Select method of Range class failed"
from target.offset(0, -1).Select in change event when I try to assign the
target.

Clara

 

[Guest]

sounds like what might happen if you click in Column A.
Taget being assigned A? - and Offset instructing excel to
move one-cell -to-the-LEFT (-1) << which if OFF THE SHEET !!

 

[Guest]

BUt target in on Column B

 

[Guest]

I believe you would also get that same error if you put that code in the
selectionchange event. So, assuming that what you're trying to do is prevent
the user from selecting any cell in column B by moving it to column A, try
something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 Then
Application.EnableEvents = False
Target.Offset(0, -1).Select
Application.EnableEvents = True
End If
End Sub

If this is not what you're trying to do, then perhaps post a little bit more
of your code and a little more info on what you're trying to do...

 

[Guest]

Hi Vergel,

I have two sheets: Let's say sheet1 and sheet2.When sheet1 is active, I run
a macro which is trying to assign a value to a cell on sheet2 and I make
event enable.So a change event is triggerd on the sheet2, and
target.offset(0,-1).select is going to be executed , error message apear.

Clara

 

[dq]

Clara,

If I understand well your code is trying to select a cell on sheet two
while sheet1 is active. That doesn't work. You can use 'select' only
on the active sheet. So either call sheet2.activate first, select the
cell and then reactivate sheet1, or change your program so it doesn't
need to select the cell eg 'Set MyCell = target.offset(0,-1).select

DQ