Blattname aus Zelle dynamisch übernehmen

[Chrisfrisbee]

Hallo erst einmal von einem Neuling und Grüße an das Forum.

Ich knabbere schon seit längerem an einem Problem in Excel 2016, und hoffe nun, das Ihr mir helfen könnt.
Ich schildere Euch mein selbst versuchtes "halbgelöstes" Problem.
Also, ich möchte nachstehende aus dem Internet gefischte Lösung so umwandeln, das sämtliche Tabellenblätter die betreffende Zelle je Tabellenblatt (hier A1) z.B. als Namen übernehmen. Mit nachstehener VBA-Programmierung in das Modul "diese Arbeitsmappe" funktioniert das auch. Aber...
Wenn diese Zellen (A1) jedoch Ihren Namen über eine Formel aus einem anderen Tabellenblatt beziehen, ändert sich der Namen des Tabellenblattes nicht mehr. Das klappt bisher nur bei direkten händischen Eintrag in der Zelle.

Programcode in Modul "diese Arbeitsmappe" kopiert:
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
                                 ByVal Target As Range)
'Quelle: Message-ID: <abx9roo6fzfk$.gwmxk0omvrvl.dlg@40tude.net>
   If Target.Address(0, 0) = "A1" And Target.Value <> "" Then
      If Not SheetExists(Target.Value) Then
         Sh.Name = Target.Value
      Else
         Application.Undo
      End If
   End If
End Sub

Public Function SheetExists(strName As String) As Boolean
   On Error Resume Next
   SheetExists = Not Sheets(strName) Is Nothing
End Function


Wäre wirklich schön, wenn Ihr mir helfen könnt.
Denkt bitte jedoch daran, das ich eigentlich wenig Ahnung von Excel habe:-)

Ich freue mich auf Eure Antworten

Grüße
Chrisfrisbee

[Chrisfrisbee]

Hallo,
komisch... ich kann den Threat nicht mehr editieren und Das Wort Forum (Grüße an das..) ist irgendwie rausgeflogen. Genau so verhält es sich mit dem Wort Internet (aus dem... gefischten).
Keine Ahnung warum.

Grüße
Chrisfrisbee

[Mourad]

Hallo,

neue User haben bis zu einer gewissen Anzahl an Beiträgen eingeschränkte Rechte, um Spam usw. vorzubeugen.
Warum aber jetzt die zwei Wörter entfernt wurden, müsste ich genauer nachsehen. Diese beiden Wörter sind
jedenfalls nicht in der Sperrliste. Ich habe Deinen Beitrag wieder um die Wörter ergänzt.

Gruß

[maninweb]

Hallo,

probiere mal folgende Code. Lösche zuvor vollständig Deinen Code aus dem Modul DieseArbeitsmappe
und ersetze den dann mit dem Code hier. Und dann einmal testen.

Code: Visual Basic

1.   Option Explicit
2.  
3.   Private Sub Workbook_SheetActivate(ByVal Sh As Object)
4.    
5. '   Länge des Texts in A1 prüfen...
6.    
7.     If Len(ActiveSheet.Cells(1, 1).Value) > 0 And _
8.        Len(ActiveSheet.Cells(1, 1).Value) < 32 Then
9.      
10. '     Gleichheit von Text in A1 und Blattname prüfen...
11.        
12.       If ActiveSheet.Cells(1, 1).Value <> _
13.          ActiveSheet.Name Then
14.        
15. '       Existenz des Blattnames prüfen...
16.        
17.         If Not SheetExists(ActiveSheet.Cells(1, 1).Value) Then
18.          
19. '         Umbenennen...
20.          
21.           ActiveSheet.Name = ActiveSheet.Cells(1, 1).Value
22.          
23.         End If
24.        
25.       End If
26.      
27.     End If
28.    
29.   End Sub
30.  
31.   Private Sub Workbook_SheetChange(ByVal Sh As Object, _
32.                                    ByVal Target As Range)
33.    
34. '   Target auf A1 prüfen...
35.    
36.     If Target.Row = 1 And Target.Column = 1 Then
37.      
38. '     Länge des Texts in A1 prüfen...
39.      
40.       If Len(Target.Value) > 0 And _
41.          Len(Target.Value) < 32 Then
42.        
43. '       Existenz des Blattnames prüfen...
44.        
45.         If Not SheetExists(Target.Value) Then
46.          
47. '         Umbenennen...
48.          
49.           ActiveSheet.Name = Target.Value
50.          
51.         End If
52.        
53.       End If
54.      
55.     End If
56.    
57.   End Sub
58.  
59.   Private Function SheetExists(strName As String) As Boolean
60.  
61.     On Error Resume Next
62.    
63.     SheetExists = Not Sheets(strName) Is Nothing
64.    
65.   End Function
66.  

Workbook_SheetChange spricht nur das manuelle Ändern der Zelle an, daher ging das nicht bei Dir.
Workbook_SheetActivate spricht das Aktivieren der Tabelle an, wodurch dann jedes mal, wenn das
Blatt aktiviert wird, eine Prüfung Zellinhalt für A1 mit Blattname erfolgt.

Gruß

Nachtrag: noch eine kleine Korrektur eingefügt.

[Chrisfrisbee]

Hallo Maninweb,
danke dafür. Leider funzt das bei mir nicht. Ich habe alles aus "diese Abeitsmappe" im VBA-Projekt Explorer gelöscht und den o.g. Code einkopiert (allerdings auf Word kopiert und mit einem Stick an meinem Notebook ins Excel über Kopieren / Einfügen übernommen). Klappt denn das bei dir??
Vielleicht stimmt auch von meiner Routine etwas nicht:-(
Übrigens betrifft es bei mir die Zellen G8 aus jedem Tabellenblatt. Habe dafür alle A1 umgeschrieben. Funzt aber auch nicht.
Hast du vielleicht eine Idee woran es liegen kann??
Wäre dir unendlich dankbar wenn ich die Funktion zu laufen kriege

Mit der Korrektur leider auch nicht....

Grüße
Chrisfrisbee

[maninweb]

Hallo,

klappt bei mir prima. Statt Word, verwende mal den normalen Editor (einfach als Textdatei speichern).
Wenn Du G8 adressieren möchtest, dann ersetze alle (!) Cells(1, 1) durch Cells(8, 7).
Wenn das nicht klappt, dann lade doch mal Deine Beispieldatei hier hoch, anonymisiert, natürlich.

Gruß

[Chrisfrisbee]

Hallo Mainweb,
danke für die Hilfe. Mache mich morgen dran und melde mich umgehend.
Noch eine Frage: die A1 muss ich aber zusätzlich auch auf g8 umschreiben, oder??

Grüße
Christian

[maninweb]

Hallo,

ok.

Die A1 im Quellcode sind alle Kommentare (grün) und haben keine Wirkung auf den Code.
Die kannste natürlich ändern, zur Dokumentation. Für den Code ist z.B. Cells(y, x) relevant.

Oh, und ich sehe gerade, dass habe ich vergessen habe, Target zu ändern. Hier dann mal der
komplett geänderte Code, der sich auf G8 bezieht.

Code: Visual Basic

1.   Option Explicit
2.  
3.   Private Sub Workbook_SheetActivate(ByVal Sh As Object)
4.    
5. '   Länge des Texts in G8 prüfen...
6.    
7.     If Len(ActiveSheet.Cells(8, 7).Value) > 0 And _
8.        Len(ActiveSheet.Cells(8, 7).Value) < 32 Then
9.      
10. '     Gleichheit von Text in G8 und Blattname prüfen...
11.        
12.       If ActiveSheet.Cells(8, 7).Value <> _
13.          ActiveSheet.Name Then
14.        
15. '       Existenz des Blattnames prüfen...
16.        
17.         If Not SheetExists(ActiveSheet.Cells(8, 7).Value) Then
18.          
19. '         Umbenennen...
20.          
21.           ActiveSheet.Name = ActiveSheet.Cells(8, 7).Value
22.          
23.         End If
24.        
25.       End If
26.      
27.     End If
28.    
29.   End Sub
30.  
31.   Private Sub Workbook_SheetChange(ByVal Sh As Object, _
32.                                    ByVal Target As Range)
33.    
34. '   Target auf G8 (Zeile 8, Spalte 7) prüfen...
35.    
36.     If Target.Row = 8 And Target.Column = 7 Then
37.      
38. '     Länge des Texts aus Target prüfen...
39.      
40.       If Len(Target.Value) > 0 And _
41.          Len(Target.Value) < 32 Then
42.        
43. '       Existenz des Blattnames prüfen...
44.        
45.         If Not SheetExists(Target.Value) Then
46.          
47. '         Umbenennen...
48.          
49.           ActiveSheet.Name = Target.Value
50.          
51.         End If
52.        
53.       End If
54.      
55.     End If
56.    
57.   End Sub
58.  
59.   Private Function SheetExists(strName As String) As Boolean
60.  
61.     On Error Resume Next
62.    
63.     SheetExists = Not Sheets(strName) Is Nothing
64.    
65.   End Function

Gruß

[Chrisfrisbee]

Hallo Maninweb,
hat mir keine Ruhe gelassen dein Vorschlag
Hab´s gerade probiert. Klappt "fast" so wie ich mir das vorstelle. Super, und schon einmal vielen, vielen Dank dafür:-)

Allerdings habe ich noch drei Problemchen für die "perfekte" Lösung.
1: wie schaffe ich eine Vorlage mit z.B. 30 Tabellenblätter in der nicht die Tabellenblätter bereits bezeichnet sind. Aktuell setzt sich nur das 1. Tabellenblatt mit den einzulesenden Namen von Tabelle 1(G8) auf -0- zurück wenn die Zelle G8 nicht beschrieben ist. Alle folgenden Blätter bleiben auf den zuletzt eingelesenen Namen stehen, auch wenn G8 in diesen Tabellenblättern keinen Wert mehr beinhaltet.

2: Kann ich denn Tabellenblätter von dieser Funktion ausschließen? Z.B. das 1. Tabellenblatt mit der einzufügenden Namensliste für die Einlesefunktion in
die anderen Blätter weil dort Zelle G8 anderweitig benutzt wird?

3: Gibt es eine Funktion, das nicht erst mit dem Öffnen des Tabellenblattes die Tabelle aus der Zelle G8 umbenannt wird. Am schönsten wäre es, wenn mit dem Einlesen der Namen z.B. vom Tabellenblatt 1 "alle" anderen G8 programmierten Tabellenblätter sich direkt umbenennen ohne das das Blatt vorher geöffnet werden muss. Dann kann ich über den Tabellenblattnamen die richtige Tabelle suchen ohne si einzeln öffnen zu müssen.

Ich hoffe, das meine Wünsche nicht unverschämt sind. Aber du hast mit der tollen VBA von dir bei mir Begehrlichkeiten geweckt .
Ich bin dir auch schon jetzt sehr dankbar für die tolle Unterstützung.

So, jetzt geh ich aber Bubu machen....

Grüße
Chrisfrisbee
 

[maninweb]

Hallo,

mit VBA kannste einiges machen, ich schlage aber vor, dass Du hier mal eine Beispieldatei hochlädst.

zu 1: habe ich nicht so richtig verstanden.
zu 2: das ginge
zu 3: das ginge auch

Gruß

[Chrisfrisbee]

Hallo,
nochmal ein großes Dankeschön an Maninweb.
hab jetzt genau das was ich wollte.
Super Forum, super Support

Grüße
Chrisfrisbee