vba 在另一个工作表中选择当前活动的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26925988/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select the currently active cell in another worksheet
提问by Cynthia Kreidy
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.
我面临着 excel 宏语法的复杂性。我的工作簿包含几张纸，第一张名为“参考表”。我不希望用户通过他们当前正在处理的工作表编辑其他工作表中的一些条目，而只能通过参考工作表进行编辑。
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
我在相应的工作表 VB 中发布了以下代码，但显然最后的 Cells 属性中有错误 -> 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.
非常感谢您的想法。
回答by chris neilsen
The reasonyour code fails is that in a Worksheetmodule an unqualified range reference (Cells(val1, val2)in your case) refers to the worksheet Module, not the Activeworksheet. Since you have just activated another sheet, you are trying to select a cell on an inactive sheet, which causes the error.
您的代码失败的原因是，在Worksheet模块中，不合格的范围引用（Cells(val1, val2)在您的情况下）是指工作表 Module，而不是Active工作表。由于您刚刚激活了另一个工作表，您正试图在非活动工作表上选择一个单元格，这会导致错误。
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
