Runtime error 1004
 

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
--
thank you so much for your help

Runtime error 1004
 

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 !!


"clara" wrote:

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
--
thank you so much for your help

Runtime error 1004
 

BUt target in on Column B
--
thank you so much for your help


"JMay" wrote:

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 !!


"clara" wrote:

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
--
thank you so much for your help

Runtime error 1004
 

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...



--
Hope that helps.

Vergel Adriano


"clara" wrote:

BUt target in on Column B
--
thank you so much for your help


"JMay" wrote:

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 !!


"clara" wrote:

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
--
thank you so much for your help

Runtime error 1004
 

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
--
thank you so much for your help


"Vergel Adriano" wrote:

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...



--
Hope that helps.

Vergel Adriano


"clara" wrote:

BUt target in on Column B
--
thank you so much for your help


"JMay" wrote:

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 !!


"clara" wrote:

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
--
thank you so much for your help

Runtime error 1004
 

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