Hi,

I'm trying to get a macro to run when I select a cell in a sheet. I seem to have some of the code, and it works until a certain point. The code I have is (as if running from say "Sheet Y"):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$4" Then
Dim Response As VbMsgBoxResult
Response = MsgBox("This entry is covered in Sheet X. Would you like to edit this information", vbQuestion + vbYesNo)
If Response = vbNo Then Exit Sub
Sheets("Sheet X").Select
*Range("B2").Select*
MsgBox "Please edit the entry as appropriate", vbInformation
End If
End Sub

The problem seems to occur when it reaches the line in the *. I have a feeling this is something to do with the fact it is a sheet specific code, and when I try to do something on another sheet, it doesn't like it.

I'm quite a noob when it comes to programming VBA, but any help on how to get this working would be great. Thanks!

Hi and welcome to the forum.

Your code is in a worksheet's class module.

The Range("B2").Select in your code is a problem because the Range property is not qualified. When the code is in a worksheet's class module, if you do not qualify the range property, then the range property returns a reference to a range on that worksheet, not a range on the activesheet.

At this point you have already activated "Sheet X" so the worksheet pertaining to this event handler code is no longer the activesheet. The problem it is impossible to select a range which is not on the activesheet and, since range("B2") is returning a reference to cell B2 on the de-activated sheet (the one relating to your sheet class module), you will get an error.

To avoid this you need to qualify the range property like this:
So you are explicitly saying that the range B2 belongs to Sheet X and not the Sheet that houses the event handler.


Another thing you need to be aware of, although it is not an issue with your current code, is that selecting a cell within a SelectionChange() event handler is potentially dangerous. Selecting a cell raises the SelectionChange event so, if you happened to select a cell on that worksheet within your code, then it may cause a recursive loop. To avoid this you would have to temporarily disable events via the Application.EnableEvents property.

Hope that helps...

Worked like a charm! Thank you very much!