Excel vba:
recognize an input in cells
How to
monitor the input in an excel sheet when changing a cell in a specific area.
solution
You can
examine the Worksheet Change on Target Range in Excel
Create Worksheet_Change Event
To do this,
open the vba macro area under Alt-F11.
In the
vbaProject area you select the worksheet as a table (xxx)
Then you
can open the object area worksheet (cells top left)
And on the
right, select the event: Change
Then you
can query the input area as follows with Target.Address
|
Private Sub Worksheet_Change(ByVal Target As Range)
'--------< Worksheet_Change(ImputCell) >--------
'*After_Cell_Input_Change
If Target.Address Like "$B$*" Then
MsgBox "treffer"
End If
'--------</ Worksheet_Change(ImputCell) >--------
End Sub
|
Other options for checking the input area
The
following vba code possibilities can be used to detect the area of a complete
column
1)
Comparison of the Address
About
wildcard like *
2)
Comparison via Intersect (input area, allowed_range)
a) via
exact address designation B1: B1000
b) via
column address without lines B: B
c) about Columns property
|
Private Sub Worksheet_Change(ByVal Target As Range)
'--------< Worksheet_Change(ImputCell) >--------
'*After_Cell_Input_Change
If Target.Address Like "$B$*" Then
MsgBox "treffer Target.Address Like '$B$*'"
End If
'oder
If Not Intersect(Target, Range("B1:B10000")) Is Nothing Then
MsgBox "treffer Intersect(Target, Range('B1:B10000'))"
End If
'oder
If Not Intersect(Target, Range("B:B")) Is Nothing Then
MsgBox "treffer Intersect(Target, Range('B:B'))"
End If
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
MsgBox "treffer Intersect(Target, Columns('B:B'))"
End If
'--------</ Worksheet_Change(ImputCell) >--------
End Sub
|