时间戳

首页 > 解决方案 > 事件点击单元格

excel - 事件点击单元格

问题描述

我刚刚创建了一个新文件并放入了数据（A1 到 A10 行），我想知道每次单击单元格 A 时如何从单元格中获取数据，如果它不为空（例如：当我单击A列的第三行，所以我可以得到它的值）。我看到了“Worksheet_SelectionChange”方法，但它不适用于我的示例，因为我们必须打开文件代码并将函数放入其中，我想在创建文件时直接执行此操作。这是我的例子

sub test(name as string)
  sheet.add

  ActiveSheet.name=name
  i=2

  while i < 10 
    cells(i,1)=i
    i=i+i
  Wend
end sub

标签： excelvba

解决方案

A Worksheet_SelectionChange event does not necessarily have to reside in a sheet-specific module. It could also for example reside in a class module, and important for this instance, the ThisWorkbook module (although there it is named a Workbook_SheetSelectionChangeevent).

If you always want to fire the Workbook_SheetSelectionChange event, regardless of in what sheet the selection changed, try the following

'place in the ThisWorkbook module
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

'Do stuff

MsgBox "Changes were made in worksheet: " & Sh.Name, vbInformation

End Sub

If you only want to fire the event on the latest made WS

1) You could make sure the latest made WS is always the last in the WB

'In a regular module
Sub addnwsheet()
Dim shtname As String

With ThisWorkbook

shtname = "temp" & .Sheets.Count
'add a sheet at the end
.Sheets.Add(after:=.Sheets(.Sheets.Count)).Name = shtname

End With

End Sub

'place in the ThisWorkbook module
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Sh.Index = ThisWorkbook.Sheets.Count Then
    'Do stuff

    MsgBox "Selection changed in worksheet: " & Sh.Name, vbInformation
End If

End Sub

2) A more secure method is to declare the newly made sheet as a Global variable. These are persistent and public.

'In a regular module
Global nwsht As Sheet

Sub addnwsheet()
Dim shtname As String

With ThisWorkbook

    shtname = "temp" & .Sheets.Count
    'add a sheet at the end
    .Sheets.Add(after:=.Sheets(.Sheets.Count)).Name = shtname
    Set nwsht = .Sheets(shtname)

End With

End Sub

'place in the ThisWorkbook module
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Sh = nwsht Then
    'Do stuff

    MsgBox "Selection changed in worksheet: " & Sh.Name, vbInformation
End If

End Sub

EDIT

If you want to specify the range the event should trigger on

'place in the ThisWorkbook module
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim r as Range
    Set r = Workbooks(REF).Sheets(REF).Range("D2:D100") 'event will only get triggered by this range

    If Not Intersect(Target, r) Is Nothing Then
        'Do stuff

        MsgBox "Selection changed in worksheet: " & Sh.Name, vbInformation
    End If

    End Sub

推荐阅读