VBA宏在所有工作表上选择相同的单元格 [英] VBA Macro To Select Same Cell on all Worksheets

问题描述

我是VBA的新手，这种特殊的动作似乎超出了我目前的知识范围.

I'm somewhat newer to VBA, and this particular action seems like it may be out of my current scope of knowledge.

是否可以对VBA进行编码，以使其在所有工作表上主动选择与当前所选单元格相同的单元格?我有一个模型，可以让我的团队在Sheet1的A列中同时输入有关产品SKU的数据，但是由于我们要为每个项目输入大量信息，因此我使用了多个工作表

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

例如，如果我在Sheet1上选择了单元格H4，是否可以在切换到其他工作表时使所有其他工作表激活单元格H4?

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?

这是我到目前为止在测试工作簿上提出的内容，但是它似乎不起作用:

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代码，允许用户在每张纸上选择相同的单元格

但这要求用户实际输入他们想要选择的单元格.我希望它是自动的.

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.

推荐答案

您可以将以下内容发布到工作簿中的每个工作表中.

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 many sheets, 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.

这篇关于VBA宏在所有工作表上选择相同的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！