|
Hallo,
meinst du so?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As Integer
If Selection.Count > 1 Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "In diesen Bereich dürefen sie nur eine Zelle wählen!"
Exit Sub
End If
If Intersect(Target, Range("A2:A6")) Is Nothing Then Exit Sub
For a = 1 To ThisWorkbook.Sheets.Count
If Sheets(a).Name = Target.Offset(0, 1).Text Then
MsgBox "Tabelle mit den Namen: " & Target.Offset(0, 1) & " ist schon vorhanden"
Application.EnableEvents = False
Target.Offset(0, 1) = ""
Application.EnableEvents = True
Exit Sub
End If
Next a
Application.EnableEvents = False
If Target.Offset(0, 1).Text > "" Then
Sheets("Vorlage").Copy Before:=Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.Name = Target.Offset(0, 1)
Target.Offset(0, 1) = ActiveSheet.Name
'## das hier ??
Target.offset(0, 2) = Date
'############
ElseIf Target.Offset(0, 1).Text = "" Then
On Error Resume Next 'Sicherheit wegen EnableEvents
Application.DisplayAlerts = False
Sheets(Target.Offset(0, 1).Text).Delete
Target.Offset(0, 1) = ""
Application.DisplayAlerts = True
End If
Application.EnableEvents = True
End Sub
Gruß Werner
|