Readdy Write  
0,00 €
Your View Money
Views: Count
Self 20% 0
Your Content 60% 0

Users by Links 0
u1*(Content+Views) 10% 0
Follow-Follower 0
s2*(Income) 5% 0

Count
Followers 0
Login Register as User

Excel vba: recognize an input in cells

05.09.2018 (👁2553)


 

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