Excel – VBA code – Cell change

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

‘ The variable KeyCells contains the cells that will
‘ cause an alert when they are changed.
Set KeyCells = Range(“h5:h50”)

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

‘ Display a message when one of the designated cells has been
‘ changed.
‘ Place your code here.
‘ MsgBox (“Cell ” & Target.Address & ” has changed.”)
‘ MsgBox (“Cell ” & Target.Column & ” has changed.”)
‘ Target.Address
Cells(Target.Row, Target.Column – 1).Value = Date

End If
End Sub

Microsoft Office – Excel – *.xla

Depending on version of Windows and Office:

windows xp:

c:\Program Files\Microsoft Office\Office12\Library\

C:\Documents and Settings\nazwa_użytkownika\Dane aplikacji\Microsoft\Dodatki\

windows 7 :

c:\Program Files\Microsoft Office 15\root\office15\Library\

C:\Użytkownicy\nazwa_użytkownika\AppData\Roaming\Microsoft\Dodatki\

C:\Users\<USER>\AppData\Roaming\Microsoft\AddIns

c:\Program Files (x86)\Microsoft Office\Office12\Library\

Excel, VBA, Table

TU: http://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

A oprócz tego taki kod w VBA, żeby okreslić nazwę tabeli w której mamy zaznaczoną komórkę:

Sub DetermineActiveTable()
Dim SelectedCell As Range
Dim TableName As String
Dim ActiveTable As ListObject
Set SelectedCell = ActiveCell
‘Determine if ActiveCell is inside a Table
On Error GoTo NoTableSelected
TableName = SelectedCell.ListObject.Name
Set ActiveTable = ActiveSheet.ListObjects(TableName)
On Error GoTo 0
‘Do something with your table variable (ie Add a row to the bottom of the ActiveTable)
ActiveTable.ListRows.Add AlwaysInsert:=True
Exit Sub
‘Error Handling
NoTableSelected:
MsgBox “There is no Table currently selected!”, vbCritical
End Sub