AW: Aktive Zelle in Liste suchen und einfügen
06.06.2018 00:40:10
fcs
Hallo Guesa,
mit entsprechenden Ereignis-Makros unter dem Code-Modul "DieseArbeitsmappe" kann man dies leisten.
Dabei wird bei Doppelklick in die Ja- bzw. Nein-Spalte die jeweils andere Spalte geleert. Ein "X" in beiden Spalten ist so nicht möglich
Gruß
Franz
'Code unter DieseArbeitsmappe
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
Dim bolMA_Liste As Boolean
Dim wksMA As Worksheet, ZeileMA As Long
Set wksMA = Me.Worksheets("Mitarbeiterliste")
Select Case Sh.Name
Case "Vorlage für Standorte", wksMA.Name
'do nothing
Case Else
If Target.Row >= 4 Then
If Sh.Cells(Target.Row, 1) "" Then 'Standort ist eingetragen
If Sh.Cells(Target.Row, 2) "" Then 'Name ist eingetragen
bolMA_Liste = False
Application.EnableEvents = False
Select Case Target.Column
Case 4 'Ja
Sh.Cells(Target.Row, 4) = "X"
Sh.Cells(Target.Row, 5).ClearContents
bolMA_Liste = True
Cancel = True
Case 5 'Nein
Sh.Cells(Target.Row, 4).ClearContents
Sh.Cells(Target.Row, 5) = "X"
bolMA_Liste = True
Cancel = True
End Select
If bolMA_Liste = True Then
Dim strSO$, strName$, strVorname$
strSO = Sh.Cells(Target.Row, 1).Text
strName = Sh.Cells(Target.Row, 2).Text
strVorname = Sh.Cells(Target.Row, 3).Text
With wksMA
For ZeileMA = 4 To .Cells(.Rows.Count, 2).End(xlUp).Row
If .Cells(ZeileMA, 1).Text = strSO Then
If .Cells(ZeileMA, 2).Text = strName Then
If .Cells(ZeileMA, 3).Text = strVorname Then
.Cells(ZeileMA, 4).Value = Sh.Cells(Target.Row, 4).Value
.Cells(ZeileMA, 5).Value = Sh.Cells(Target.Row, 5).Value
End If
End If
End If
Next ZeileMA
End With
End If
Application.EnableEvents = True
End If
End If
End If
End Select
End Sub
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
Dim bolMA_Liste As Boolean
Dim wksMA As Worksheet, ZeileMA As Long
Set wksMA = Me.Worksheets("Mitarbeiterliste")
Select Case Sh.Name
Case "Vorlage für Standorte", wksMA.Name
'do nothing
Case Else
If Target.Row >= 4 Then
If Sh.Cells(Target.Row, 1) "" Then 'Standort ist eingetragen
If Sh.Cells(Target.Row, 2) "" Then 'Name ist eingetragen
bolMA_Liste = False
Application.EnableEvents = False
Select Case Target.Column
Case 4 'Ja
If Sh.Cells(Target.Row, 4) = "X" Then
Sh.Cells(Target.Row, 4).ClearContents
bolMA_Liste = True
Cancel = True
End If
Case 5 'Nein
If Sh.Cells(Target.Row, 5) = "X" Then
Sh.Cells(Target.Row, 5).ClearContents
bolMA_Liste = True
Cancel = True
End If
End Select
If bolMA_Liste = True Then
Dim strSO$, strName$, strVorname$
strSO = Sh.Cells(Target.Row, 1).Text
strName = Sh.Cells(Target.Row, 2).Text
strVorname = Sh.Cells(Target.Row, 3).Text
With wksMA
For ZeileMA = 4 To .Cells(.Rows.Count, 2).End(xlUp).Row
If .Cells(ZeileMA, 1).Text = strSO Then
If .Cells(ZeileMA, 2).Text = strName Then
If .Cells(ZeileMA, 3).Text = strVorname Then
Select Case Target.Column
Case 4 'Ja
.Cells(ZeileMA, 4).ClearContents
Case 5 'Nein
.Cells(ZeileMA, 5).ClearContents
End Select
End If
End If
End If
Next ZeileMA
End With
End If
Application.EnableEvents = True
End If
End If
End If
End Select
End Sub