vba VBA宏在所有工作表上选择相同的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23023293/
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
VBA Macro To Select Same Cell on all Worksheets
提问by user3325382
I'm somewhat newer to VBA, and this particular action seems like it may be out of my current scope of knowledge.
我对 VBA 有点新，这个特定的操作似乎超出了我目前的知识范围。
Is there a way to code VBA to have it actively select the same cell on all worksheets as the current cell selected? I have a model I've put together to allow my team to enter data simultaneously regarding product SKUs in Column A on Sheet1, but due to the large amount of information that we enter per item, I used multiple sheets
有没有办法编码 VBA 让它在所有工作表上主动选择与当前选择的单元格相同的单元格？我有一个模型，我已经放在一起，让我的团队在 Sheet1 的 A 列中同时输入有关产品 SKU 的数据，但由于我们为每个项目输入的信息量很大，我使用了多张表
For example, if I have cell H4 selected on Sheet1, is it possible to have all other sheets active cell H4 upon switching to the other worksheets?
例如，如果我在 Sheet1 上选择了单元格 H4，是否可以在切换到其他工作表时让所有其他工作表活动单元格 H4？
This is what I've come up with so far on a test workbook, but it does not seem to work:
到目前为止，这是我在测试工作簿上提出的，但似乎不起作用：
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case LCase(Sh.Name)
Case Is = "sheet1", "sheet2", "sheet3"
If CurRow > 0 Then
With Application
.EnableEvents = False
.Goto Sh.Cells(CurRow, CurCol), Scroll:=True
Sh.Range(ActCellAddr).Select
.EnableEvents = True
End With
End If
End Select
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Select Case LCase(Sh.Name)
Case Is = "sheet1", "sheet2", "sheet3"
CurRow = ActiveWindow.ScrollRow
CurCol = ActiveWindow.ScrollColumn
ActCellAddr = ActiveCell.Address
End Select
End Sub
I've located this code below:
我在下面找到了这个代码：
Excel VBA code to allow the user to choose the same cell on every sheet
Excel VBA 代码允许用户在每张工作表上选择相同的单元格
But this requires the user actually enter the cell they'd like to have selected. I am looking for it to be automatic.
但这需要用户实际输入他们想要选择的单元格。我正在寻找它是自动的。
Any tips or suggestions? Any help is greatly appreciated.
任何提示或建议？任何帮助是极大的赞赏。
回答by Jerome Montino
You can post the following to every sheet in your workbook.
您可以将以下内容发布到工作簿中的每个工作表。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set CurrWS = ActiveSheet
For Each WS In ThisWorkbook.Worksheets
WS.Activate
WS.Range(Target.Address).Select
Next
CurrWS.Activate
End Sub
Every time you select a cell, it will cycle through all the worksheets and select the same cell there. The downside to this is obvious: if you have too manysheets, it's going to be tedious. The other problem is that it's going to cycle through everything. So it might mess up some other sheets if you're going to use this for data entry.
每次选择一个单元格时，它都会在所有工作表中循环并在那里选择相同的单元格。这样做的缺点是显而易见的：如果你有太多的床单，那将会很乏味。另一个问题是它会循环遍历所有内容。因此，如果您打算使用它进行数据输入，它可能会弄乱其他一些工作表。
Otherwise, if it's just selecting the cell, then this is harmless though the flicker can be noticeable at times, based on how many sheets you have.
否则，如果它只是选择单元格，那么这是无害的，尽管闪烁有时会很明显，这取决于你有多少张纸。
Not as elegant as one would want, but it works. Good luck and let us know if this helps.
不像人们想要的那样优雅，但它有效。祝你好运，如果这有帮助，请告诉我们。
回答by Tim Williams
Worth noting there is a workbook-level event handler which handles the same event, so you only need to add the code once to the ThisWorkbook code module:
值得注意的是有一个工作簿级别的事件处理程序来处理相同的事件，因此您只需将代码添加到 ThisWorkbook 代码模块：
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Shrepresents the ActiveSheet.
Sh代表 ActiveSheet。
Probably also worth disabling events while you're selecting the ranges on the other sheets, or that will re-trigger your event handler (don't forget to turn event handling back on before exiting your code!)
可能还值得在您选择其他工作表上的范围时禁用事件，或者这将重新触发您的事件处理程序（不要忘记在退出代码之前重新打开事件处理程序！）
回答by Tom Gonzales
This approach will test for hidden sheets. It selects all non-hidden sheets, selects the target cell then returns to the original sheet. It works pretty fast even if you have many many tabs.
这种方法将测试隐藏的工作表。它选择所有非隐藏的工作表，选择目标单元格然后返回到原始工作表。即使您有很多标签，它的工作速度也非常快。
targetcell = ActiveCell.Address
OriginSheet = ActiveSheet.Name
Dim ws As Worksheet
For Each ws In Sheets
If ws.Visible = True Then ws.Select (False)
Next ws
range(targetcell).Select
Sheets(OriginSheet).Select
