Why doesn't this simple VB code work?

Solved/Closed
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Aug 16, 2011 at 10:30 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Aug 23, 2011 at 09:44 AM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 16, 2011 at 10:41 AM
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
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Aug 18, 2011 at 09:49 AM
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
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 18, 2011 at 05:43 PM
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
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 18, 2011 at 05:49 PM
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
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 18, 2011 at 05:57 PM
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.
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Aug 23, 2011 at 09:44 AM
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
0