Zwei Worksheet_Change verschachteln

Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo hell With ActiveWorkbook.ActiveSheet Set searchObject = .Cells.Find(What:="RG", LookAt:=xlWhole) If Not searchObject Is Nothing Then statusColumn = searchObject.Column If .Range(Target.Address).Column = statusColumn Or .Range(Target.Address).Column = _ statusColumn - 1 Or .Range(Target.Address).Column = statusColumn - 2 Or .Range(Target.Address). _ Column = statusColumn - 7 Then '.Cells(.Range(Target.Address).Row, .Range(Target.Address).Column + 1) = Date .Cells(.Range(Target.Address).Row, statusColumn - 3) = Date '+2 => Datum in übernächste Spalte, -5 => in Zelle 5 Spalten von Änderungszelle End If End With Makro 2: Private Sub Worksheet_Change(ByVal Target As Range) Const myPfad As String = "\\Pfad..." If Target.Column = 2 Then If Target.Value = 4 Then If MsgBox("Bla Bla", vbYesNo) = 6 Then Shell "Explorer.exe " & myPfad, vbNormalFocus End If End If End If End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo hell Const myPfad As String = "\\Pfad..." With ActiveWorkbook.ActiveSheet Set searchObject = .Cells.Find(What:="RG", LookAt:=xlWhole) If Not searchObject Is Nothing Then statusColumn = searchObject.Column If .Range(Target.Address).Column = statusColumn Or .Range(Target.Address).Column = _ statusColumn - 1 Or .Range(Target.Address).Column = statusColumn - 2 Or .Range(Target.Address).Column = statusColumn - 7 Then '.Cells(.Range(Target.Address).Row, .Range(Target.Address).Column + 1) = Date .Cells(.Range(Target.Address).Row, statusColumn - 3) = Date '+2 => Datum in übernächste Spalte, -5 => in Zelle 5 Spalten von Änderungszelle End If End With If Target.Column = 2 Then If Target.Value = 4 Then If MsgBox("Bla Bla", vbYesNo) = 6 Then Shell "Explorer.exe " & myPfad, vbNormalFocus End If End If End If End Sub

Hallo Klaus,
der Warnhinweis verschwindet, jedoch führt er Makro 2 nicht aus.
Kannst du noch mal drüber schauen?
Ich danke dir vielmals!
Bestehendes Makro (1):

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo hell
With ActiveWorkbook.ActiveSheet
Set searchObject = .Cells.Find(What:="RG", LookAt:=xlWhole)
If Not searchObject Is Nothing Then statusColumn = searchObject.Column
If .Range(Target.Address).Column = statusColumn Or .Range(Target.Address).Column =  _
statusColumn - 1 Or .Range(Target.Address).Column = statusColumn - 2 Or .Range(Target.Address).Column = statusColumn - 7 Then
.Cells(.Range(Target.Address).Row, statusColumn - 3) = Date
End If
End With
If Target.Column = 26 _
And Target.Row >= 2 _
And Not Target.Value = 0 _
And Selection.Cells.Count = 1 Then
Application.EnableEvents = False
With Target.Offset(, Target.Value)
If .Value = "" Then
.Value = Date
Else
If MsgBox("Wollen Sie den Reifegrad wirklich ändern?", vbYesNo) = 6 _
Then
.Value = Date
End If
End If
End With
End If
hell:
Application.EnableEvents = True
End Sub

Zu integrierendes Makro (2):

Private Sub Worksheet_Change(ByVal Target As Range)
Const myPfad As String = "\\Pfad..."
If Target.Column = 2 Then
If Target.Value = 4 Then
If MsgBox("Bla Bla", vbYesNo) = 6 Then
Shell "Explorer.exe " & myPfad, vbNormalFocus
End If
End If
End If
End Sub

Mein Vorschlag:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo hell
Const myPfad As String = "\\Pfad..."
With ActiveWorkbook.ActiveSheet
Set searchObject = .Cells.Find(What:="RG", LookAt:=xlWhole)
If Not searchObject Is Nothing Then statusColumn = searchObject.Column
If .Range(Target.Address).Column = statusColumn Or .Range(Target.Address).Column =  _
statusColumn - 1 Or .Range(Target.Address).Column = statusColumn - 2 Or .Range(Target.Address).Column = statusColumn - 7 Then
.Cells(.Range(Target.Address).Row, statusColumn - 3) = Date
End If
End With
If Target.Column = 2 Then
If Target.Value = 4 Then
If MsgBox("Bla Bla", vbYesNo) = 6 Then
Shell "Explorer.exe " & myPfad, vbNormalFocus
End If
End If
End If
If Target.Column = 26 _
And Target.Row >= 2 _
And Not Target.Value = 0 _
And Selection.Cells.Count = 1 Then
Application.EnableEvents = False
With Target.Offset(, Target.Value)
If .Value = "" Then
.Value = Date
Else
If MsgBox("Wollen Sie den Reifegrad wirklich ändern?", vbYesNo) = 6 _
Then
.Value = Date
End If
End If
End With
End If
hell:
Application.EnableEvents = True
End Sub

Beliebteste Forumthreads (12 Monate)

Zwei Worksheet_Change verschachteln

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread