CODEWITHSUNDEEP
excelvba
mor diop
mor diop

Reputation: 27

Event click cell

I just created a new file and I put data (line A1 to A10) and I would like to know how to get the data from the cell each time you click on a cell A if it is not empty (example: when I click on the third line of column A so I can have its value). I saw the "Worksheet_SelectionChange" method but it can't work with my example because we have to open the file code and put the function in it and I want to do it directly when creating the file. It's my example

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

Upvotes: 1

Views: 696

Answers (1)

Tim Stack
Tim Stack

Reputation: 3248

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

Upvotes: 1

Related Questions