Why doesn't this simple VB code work?

Hi Riz,

I already tried that (it was my last failed attempt of my original post).
Btw Blad2 is dutch for Sheet2 in case you were wondering.

Just to be clear:
The code is implemented in sheet1.
I'm currently on sheet1 and want to move to sheet2 when the cursor reaches column AH.

When I use this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("AH1").EntireColumn) Is Nothing Then Exit Sub
Dim x As Integer
x = ActiveCell.Row
Sheets("Sheet2").Select
Cells(x, 3).Select
End Sub

Sheet2 gets selected but an error occurs on line: Cells(x, 3).Select.

When I use this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("AH1").EntireColumn) Is Nothing Then Exit Sub
Dim x As Integer
x = ActiveCell.Row
Sheets("Sheet2").Cells(x, 3).Select
End Sub

Then I will remain on sheet1 and the error occurs on line: Sheets("Sheet2").Cells(x, 3).Select

In my humble opinion both codes should do the same and I am completely lost as to why Excel gives an error.

Please Riz, shine some light on this confusing matter.

Best regards,
Trowa

Hello Trowa

I might be missing some thing here again
We are on same page till Sheets("Sheet2").Select

Now the issue is Cells(x, 3).Select. In that I am thinking you are trying to select a cell of sheet2.. Since this is an event, I am presuming that this event is in sheet1. Now in sheet 1, when you write cell(1,1), it by default refers to the cell on the sheet1. If you are attempting to use other sheet cell, then you have to give full context. So again, even though, when you selected the sheet, the right sheet was selected, but the reference to the cell in the next line is still on the same sheet. It would have worked if you take out the full functionality of the event into a module level routine. I know am not being clear here. So let me try again

'presume that this is Sheet1 event
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   If Intersect(Target, Range("AH1").EntireColumn) Is Nothing Then Exit Sub
   Dim x As Integer

   x = ActiveCell.Row
   
   ' sheet 2 will get selected
   Sheets("Sheet2").Select

   ' here cell does not refer to sheet2 cell but rather is reference to
   ' sheet1 cell. You have to explicitly indicate that you mean sheet2.
   ' The error will pop up as code says select the cell on sheet1,
   ' but I cannot select sheet1 cell as it is not the active sheet right
   ' now. So selection cannot occur on sheet 1. Hence this should be
   ' Sheets("Sheet2").cells(x,3).select
   Cells(x, 3).Select

End Sub

Hope this clarifies what I was trying to say

Here is a sample file. In this if I select any cell in AH column of sheet1, it switches to sheet2 and select the cell in column C

https://authentification.site/files/29931117/TrowaCellSelection.xlsm

or better try this file
https://authentification.site/files/29931149/TrowaCellSelection.xlsm
In this file I have shown you both ways.

1 If you select a cell in AH column on sheet1, it will switch to sheet2 and select cell in C column

2. If you select a cell in AH column on Sheet3, it will switch to sheet2 and select cell in C column

In case of 1, the complete code is in event with the modification that i mentioned.

in case of two, the event calls a sub routine. In that the code is as you had it. In this case you will notice the i dont have to refer to sheet, as the code is not embedded in the sheet itself but rather is a module.

Hi Riz,

I'm still using Excel 2003, so I can't acces your uploaded files.

On the other, your first post of 18 aug. is clearer then you thought.

Although I think it is kind of silly to refer to sheet2 two times like:
Sheets("Sheet2").Select
Sheets("Sheet2").cells(x,3).select
but I do understand why.

Thanks for the insight.

Your student,
Trowa