如何传递“目标”的内容范围在“Worksheet_SelectionChange事件到子？ [英] How do I pass the contents of the "Target" range in a "Worksheet_SelectionChange event to a sub?

问题描述

我的工作簿的第1页包含（除了其他数据）A列中的其他工作表。我希望能够单击列A5中的任何单元格：A50，并转到该单元格中列出的相应工作表。我的Sheet1代码是：

  Private Sub Worksheet_SelectionChange（ByVal Target As Range）
如果不相交（Target，Me。范围（A5：A50））没有任何内容SelectWorksheet 
 End Sub 
  

和Module2是：

  Sub SelectWorksheet（）
 Dim strName As String 
 strName = Sheet1.Range（Target ）.Text（Error occurredrs here：Method'Range'of object'Worksheet'failed）
 Sheets（strName）.Select 
 End Sub 
  / pre> 
 
 
如何让我按照我的预期工作？我知道我可以点击相应的工作表选项卡，但我正在尝试学习如何在VBA中编码。谢谢。顺便问一下，如何让我的帖子显示在问题框中输入的代码？
解决方案
为什么不通过单元格名称从单元格到子？ 
  Private Sub Worksheet_SelectionChange（ByVal Target As Range）
如果不相交（Target，Me.Range（A5： A50））不是_ 
和Target.Cells.Count = 1然后选择Worksheet（Target.Value）
 End Sub 
 
 Sub SelectWorksheet（strName As String）
表格（strName）。选择
 End Sub 
  
我也做了一个检查确保选择中只有一个单元格。
 
Sheet 1 of my workbook contains (besides other data) a list of the other worksheets in column A. I wish to be able to click on any cell in column A5:A50 and go to the appropriate worksheet listed in that cell. My Sheet1 code is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A5:A50")) Is Nothing Then SelectWorksheet
End Sub
and Module2 is:
Sub SelectWorksheet()
    Dim strName As String
    strName = Sheet1.Range("Target").Text (Error occurrs here: "Method 'Range' of object 'Worksheet' failed")
    Sheets(strName).Select
End Sub
How do I get this to work as I expect? I know I could just click on the appropriate worksheet tab but I'm trying to learn how to code in VBA. Thanks. By the way, how do I get my post to show the code as typed in the question box?
解决方案
Why not pass the sheet name from the cell to the sub? 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A5:A50")) Is Nothing _
        And Target.Cells.Count = 1 Then SelectWorksheet (Target.Value)
End Sub

Sub SelectWorksheet(strName As String)
    Sheets(strName).Select
End Sub
I've also done a check to make sure that only one cell is in the selection.

                        
这篇关于如何传递“目标”的内容范围在“Worksheet_SelectionChange事件到子？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！