Herbers Excel-Forum - das Archiv

Mehrdeutiger Name: Worksheet_Change

Bild

Betrifft: Mehrdeutiger Name: Worksheet_Change
von: Stamereilers

Geschrieben am: 15.12.2006 20:17:02
Hallo Excel Experten!
Wie bekomme ich es hin das alle 4 Makro miteinander harmonieren?
(Einzeln funktionieren Sie)
Es kommt die Meldung: Mehrdeutiger Name: Worksheet_Change
Option Explicit
'1 Kalender/Termin
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim wks As Worksheet
Dim vRow As Variant, iRow As Integer
Application.ScreenUpdating = False
If Target.Column <> 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Cancel = True
Set wks = Worksheets("Termine")
vRow = Application.Match(CDbl(Target.Value), wks.Columns(1), 0)
If IsError(vRow) Then
Beep
MsgBox "An diesem Tag keinen Termin gefunden!"
Exit Sub
End If
iRow = 2
Do Until IsEmpty(wks.Cells(iRow, 1))
If CDbl(wks.Cells(iRow, 1).Value) = CDbl(Target.Value) Then
wks.Cells(iRow, 3).Value = 1
Else
wks.Cells(iRow, 3).Value = 2
End If
iRow = iRow + 1
Loop
wks.Range("A1").CurrentRegion.Sort key1:=wks.Range("C2"), order1:=xlAscending, header:=xlYes
' wks.Columns(3).ClearContents
Application.Goto wks.Range("A1"), True
Application.ScreenUpdating = True
End Sub

'2 Kalender/Training
Private Sub Worksheet_Change(ByVal Target As Range)
Dim var As Variant
If Target.Column <> 4 Then Exit Sub
With Application
var = .VLookup(Target.Value, _
Worksheets("Training").Columns("A:B"), 2, 0)
If Not IsError(var) Then
Target.Offset(0, 1) = .VLookup(Target.Value, _
Worksheets("Training").Columns("A:B"), 2, 0) '
End If
End With
End Sub

'3 Kalender/Strecke
Private Sub Worksheet_Change(ByVal Target As Range)
Dim var As Variant
If Target.Column <> 6 Then Exit Sub
With Application
var = .VLookup(Target.Value, _
Worksheets("Strecke").Columns("A:B"), 2, 0)
If Not IsError(var) Then
Target.Offset(0, 1) = .VLookup(Target.Value, _
Worksheets("Strecke").Columns("A:B"), 2, 0)
End If
End With
End Sub

'4 Kalender/Schuhe
Private Sub Worksheet_Change(ByVal Target As Range)
Dim var As Variant
If Target.Column <> 8 Then Exit Sub
With Application
var = .VLookup(Target.Value, _
Worksheets("Schuhe").Columns("A:B"), 2, 0)
If Not IsError(var) Then
Target.Offset(0, 8) = .VLookup(Target.Value, _
Worksheets("Schuhe").Columns("A:B"), 2, 0)
End If
End With
End Sub

Vielen Dank für eure Hilfe!
Gruß
Rolf
Bild

Betrifft: AW: Mehrdeutiger Name: Worksheet_Change
von: Matthias G

Geschrieben am: 15.12.2006 20:23:38
Hallo Rolf,
2-4 so zusammenfassen:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim var As Variant
If Target.Column = 4 Then
With Application
var = .VLookup(Target.Value, _
Worksheets("Training").Columns("A:B"), 2, 0)
If Not IsError(var) Then
Target.Offset(0, 1) = .VLookup(Target.Value, _
Worksheets("Training").Columns("A:B"), 2, 0) '
End If
End With
End If
If Target.Column = 6 Then
With Application
var = .VLookup(Target.Value, _
Worksheets("Strecke").Columns("A:B"), 2, 0)
If Not IsError(var) Then
Target.Offset(0, 1) = .VLookup(Target.Value, _
Worksheets("Strecke").Columns("A:B"), 2, 0)
End If
End With
End If
If Target.Column = 8 Then
With Application
var = .VLookup(Target.Value, _
Worksheets("Schuhe").Columns("A:B"), 2, 0)
If Not IsError(var) Then
Target.Offset(0, 8) = .VLookup(Target.Value, _
Worksheets("Schuhe").Columns("A:B"), 2, 0)
End If
End With
End If
End Sub

Gruß Matthias
Bild

Betrifft: AW: Mehrdeutiger Name: Worksheet_Change
von: Stamereilers
Geschrieben am: 15.12.2006 20:37:43
Hallo Matthias!
Vielen Dank läuft super!
Gruß
Rolf
 Bild