Why doesn't this simple VB code work? [Solved/Closed]

Report
Posts
2586
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 20, 2020
-
TrowaD
Posts
2586
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 20, 2020
-
Hello,

What I want to do is the following:
When a cell in column AH is selected move to the next sheet and select the cell in column C same row. I use the following 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
The code get stuck on the line Cells(x, 3).Select.

I don't understand why. I tried some other notifications like:
Cells(x, "C").Select
Cells(x, 3).Activate
Range("C" & x).Select
Also in combination with the line before this one like:
Sheets("Blad2").Cells(x, 3).Select

What am I not seeing?

Thanks in advance for the time taken to aswer.

Best regards,
Trowa

1 reply

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
Hi Trowa

The issue is "Cells(x, 3).Select"
In worksheet where you had this code, this cell is reference on the same sheet and not the sheet which you selected. Try with
Sheets("Sheet2").Cells(x, 3).Select
TrowaD
Posts
2586
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 20, 2020
390
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
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
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
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
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

http://www.speedyshare.com/files/29931117/TrowaCellSelection.xlsm
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
or better try this file
http://www.speedyshare.com/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.
TrowaD
Posts
2586
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 20, 2020
390
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