Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unanswered: Run a Macro when a cell is selected

    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!

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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:
    Code:
    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
        Sheets("Sheet X").Range("B2").Select
        MsgBox "Please edit the entry as appropriate", vbInformation
        End If
    End Sub
    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...

  3. #3
    Join Date
    Mar 2010
    Posts
    2
    Worked like a charm! Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •