vba 一个 excel 宏，用于根据放置在列范围内任意位置的值来切换工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19412999/
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
An excel macro to switch worksheets based on the values placed anywhere in a column range
提问by user1622623
I'm trying to create a macro where it will switch from Sheet1 to Sheet2 when the word "Yes" is typed in any cell in column A. The closest I've come to getting this to work is with the code below that switches worksheets when "Yes" is typed in a specific cell.
我正在尝试创建一个宏，当在 A 列的任何单元格中键入“是”这个词时，它将从 Sheet1 切换到 Sheet2。我最接近的是使用下面的代码来切换工作表当在特定单元格中输入“是”时。
Sub ifs()
If Worksheets("Sheet1").Range("A1").Value = "Yes" Then
Sheets("Sheet2").Select
Else
End If
End Sub
采纳答案by Siddharth Rout
Like I mentioned that you can create a hyperlink in Cell A1which will directly take you to Sheet2 but still if you want vba code then try this. This code will go into the sheet code area of Sheet1
就像我提到的，你可以在 Cell 中创建一个超链接，A1它会直接带你到 Sheet2，但如果你想要 vba 代码，那么试试这个。此代码将进入工作表代码区域Sheet1
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoa
If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Columns(1)) Is Nothing And _
UCase(Target.Value) = "YES" Then _
ThisWorkbook.Sheets("Sheet2").Activate
Letscontinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume Letscontinue
End Sub
NOTE: If you are interested in the Hyperlink approach then THISis worth visiting. Checkout the section Create a hyperlink to a specific location in a workbook
注意：如果您对超链接方法感兴趣，那么这值得一游。结帐部分Create a hyperlink to a specific location in a workbook
回答by user2140261
Sub ifs()
If WorkSheetFunction.CountIf(Worksheets("Sheet1").Range("A:A"),"Yes") > 0 Then
Sheets("Sheet2").Select
End If
End Sub
