Excel VBA代码，允许用户在每张纸上选择相同的单元格 [英] Excel VBA code to allow the user to choose the same cell on every sheet

问题描述

我需要我的用户能够在工作簿中的每张32页上选择相同的单元格，但是我不必让他们不得不进入代码来对其进行更改.该代码需要保持受保护的状态.我看到过一些帖子，指示使用Shift键突出显示所有选项卡，然后在一张纸上选择适当的单元格，并在所有其他纸上选择相应的单元格.这种方法的问题在于，其他工作表的相关区域不一定是可见的，用户仍然需要滚动.

I need my user to be able to choose the same cell on every sheet 32 sheets) in the workbook, but I can't have them having to get into the code to change it. The code needs to remain protected. I have seen posts instructing to use the shift key to highlight all of the tabs, then choose the appropriate cell on one sheet and the corresponding cells are selected on all of the other sheets. The problem I have with this method is that the pertinent area of the other sheets is not necessarily visible and the user still has to scroll.

我找到了一些我可以修改以满足自己需求的代码，它很容易更改单元格引用，但是我需要用户能够在不使用代码的情况下进行修改.

I found some code that I was able to modify to suit my needs, its easy enough to change the cell reference, but I need the user to be able to do so without getting into the code.

Sub GoToSelectCellOnEverySheet()

Dim i As Long

For i = 1 To 32
Worksheets(i).Activate

Range("A1").Select

Next i

Worksheets(1).Activate

End Sub

我遇到了我认为可以修改的其他内容，但这只是在我尝试引用用户输入的内容时才出现.我尝试了该行代码的各种变体，但没有成功.我不知道我是否缺少简单的东西，或者我的方法是否完全有缺陷.它死了

I came across something else which I thought I could modify, but this just craps out on me where I try to reference the users input. I have tried all sorts of variations of that line of code, to no avail. I don't know if I am missing something simple or if my approach is completely flawed. It dies when it gets to

Range.("rRange").Select

任何帮助将不胜感激.谢谢！

Any help would be greatly appreciated. Thanks!

Sub RangeDataType()

Dim rRange As Range

    On Error Resume Next

        Application.DisplayAlerts = False

            Set rRange = Application.InputBox(Prompt:= _
                "Please Select A Cell", _
                    Title:="SPECIFY RANGE", Type:=8)

    On Error GoTo 0

        Application.DisplayAlerts = True

        If rRange Is Nothing Then

            Exit Sub

        Else

Dim i As Long

For i = 1 To 32

Worksheets(i).Activate

Range.("rRange").Select

Next i

Worksheets(1).Activate

        End If

End Sub

推荐答案

尝试执行以下操作:

Set rRange = Application.InputBox(Prompt:= _
            "Please Select A Cell", _
                Title:="SPECIFY RANGE", Type:=8)

If rRange Is Nothing Then Exit Sub

Dim i As Long
For i = 1 To 32
    Worksheets(i).Activate
    Range(rRange.Address).Select
Next i

这篇关于Excel VBA代码，允许用户在每张纸上选择相同的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！