Select the currently active cell in another worksheet

Question

I am facing a complication with excel macro syntax. My workbook contains several sheets, with the first one titled "Reference Sheet". There are some entries in the other sheets that I do not want the user to edit through the sheet they are currently working on but only through the Reference Sheet.

I locked the cells and used protect sheet, however I want the user to receive a prompt message whenever they double click on one of the cells in the concerned range, asking them if they want to change the selected cell but in the Reference Sheet.

My aim is to have the same cell selected in the current sheet, selected in the Reference Sheet to be able to edit it.

I posted the following code in the corresponding sheet VB but apparently there is an error in the Cells property at the end -> Cells(val1, val2).Select

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("C2:C5,E2")) Is Nothing Then 
    Else
        Dim Msg As String, Title As String
        Dim Config As Integer, Ans As Integer

        Msg = "Do not modify this entry from the current sheet."
        Msg = Msg & vbNewLine
        Msg = Msg & "This modification is only enabled in the Reference Sheet."
        Msg = Msg & vbNewLine
        Msg = Msg & "Would you like to go to the corresponding cell and modify it?"
        Title = "Attention"
        Config = vbYesNo + vbExclamation

        Ans = MsgBox(Msg, Config, Title)

        If Ans = vbYes Then
            Dim val1 As Integer, val2 As Integer

            val1 = ActiveCell.Row
            val2 = ActiveCell.Column
            Worksheets("Reference Sheet").Activate

            Cells(val1, val2).Select
        End If
    End If
End Sub

Your thoughts are much appreciated.

chris neilsen · Accepted Answer · 2014-11-14T09:19:00.427

The reason your code fails is that in a Worksheet module an unqualified range reference (Cells(val1, val2) in your case) refers to the worksheet Module, not the Active worksheet. Since you have just activated another sheet, you are trying to select a cell on an inactive sheet, which causes the error.

A better way to do it is

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("C2:C5,E2")) Is Nothing Then
    Else
        Dim Msg As String, Title As String
        Dim Config As Long, Ans As VbMsgBoxResult

        Msg = "Do not modify this entry from the current sheet."
        Msg = Msg & vbNewLine
        Msg = Msg & "This modification is only enabled in the Reference Sheet."
        Msg = Msg & vbNewLine
        Msg = Msg & "Would you like to go to the corresponding cell and modify it?"
        Title = "Attention"
        Config = vbYesNo + vbExclamation

        Ans = MsgBox(Msg, Config, Title)

        If Ans = vbYes Then
            With Worksheets("Reference Sheet")
                .Activate
                .Range(Target.Address).Select
            End With

        End If
    End If
End Sub

Thank you for taking the time to fix my problem. – Cynthia Kreidy Nov 14 '14 at 10:25 — Cynthia Kreidy, Nov 14 '14 at 10:25

Select the currently active cell in another worksheet

1 Answers1