AW: ich kann die Datei nicht öffnen ...
02.02.2020 12:08:12
Nepumuk
Hallo Andre,
manche sachen kann ein Makro trotz UserInterfaceOnly nicht. Z.B. einfügen nach kopieren oder eben eine Gültigkeit einfügen. Also:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim objCell As Range
Dim lngColumn As Long, lngRow As Long
Dim strTemp As String
If Target.Count = 1 Then
If Not Intersect(Target, Range("C12:L67")) Is Nothing Then
Call Target.Validation.Delete
Set objCell = Tabelle2.Columns(1).Find(What:=Cells(Target.Row, 1).Text, _
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not objCell Is Nothing Then
lngRow = objCell.Row
With Tabelle2
For lngColumn = 2 To .Cells(lngRow, .Columns.Count).End(xlToLeft).Column
With .Cells(lngRow, lngColumn)
If IsNumeric(.Text) Then strTemp = strTemp & "," & .Text
End With
Next
End With
If strTemp <> vbNullString Then
Call Unprotect(Password:="nikolai")
With Target.Validation
Call .Add(Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Mid$(strTemp, 2))
.InCellDropdown = True
End With
Call Protect(Password:="nikolai")
End If
End If
End If
End If
End Sub
Gruß
Nepumuk