AW: Mit der Excel Zelle den Registername anpassen
04.12.2017 11:01:27
Michael
Hallo!
Mit folgendem Code (alles in das Modul der Arbeitsmappe) werden die Blattnamen aller Blätter jeweils auf den Text ihrer jeweiligen Zelle "C1" gesetzt - sowohl beim Öffnen der Mappe, als auch direkt bei Änderung dieser Zelle.
Private Sub Workbook_Open()
Dim Ws As Worksheet
For Each Ws In Me.Worksheets
If Not Ws.Range("C1") = "" Then
Ws.Name = BlattNameSauber(Ws.Range("C1"))
End If
Next Ws
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Sh
If Target.Address(0, 0) = "C1" And _
Target.Cells.Count = 1 And _
Target "" Then
.Name = BlattNameSauber(Target.Text)
End If
End With
End Sub
Private Function BlattNameSauber(B$)
Dim i&
For i = 1 To Len(B)
Select Case LCase(Mid(B, i, 1))
Case Is = "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", _
"n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", _
BlattNameSauber = BlattNameSauber & Mid(B, i, 1)
Case Is = 1, 2, 3, 4, 5, 6, 7, 8, 9, 0
BlattNameSauber = BlattNameSauber & Mid(B, i, 1)
Case Is = " ": BlattNameSauber = BlattNameSauber & "_"
Case Else: BlattNameSauber = BlattNameSauber
End Select
Next i
End Function
Passt?
LG
Michael