Zellbezüge von Textfeldern verschieben

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
TextBox
Bild

Betrifft: Zellbezüge von Textfeldern verschieben
von: Simon Privat
Geschrieben am: 03.11.2015 08:15:27

Hey,
Ich hatte vor ein paar Tagen schon ein ähnliches Problem gepostet, wofür ich hier eine super Lösung erhalten habe.
Es ging darum, die Zellbezüge von vielen Textfeldern zugleich auf ein neues Tabellenblatt zu beziehen.
Dafür wurde dieser Code verwendet:


Sub aaa()
  Dim s As Shape, t
  Application.ScreenUpdating = False
  For Each s In ActiveSheet.Shapes
    If s.Type = msoTextBox Then
      With s.DrawingObject
        If Len(.Formula) Then
          t = Split(.Formula, "!")
          .Formula = "='Haupttabellenblatt'!" & t(UBound(t))
        End If
      End With
    End If
  Next
End Sub
Nun geht es darum, dass ich all diese Textfelder duplizieren muss (Copy and Paste) und sämtliche Zellbezüge für diese um einen bestimmten Betrag verschieben muss (konkret: um eins nach rechts, sprich aus $F$17 mach $G$17), weshalb ich einen neuen Thread dafür eröffnet habe. In diesem speziellen Fall würde es sogar reichen, alle "Fxx" Zellbezüge zu "Gxx" Zellbezügen zu verändern, da sich alle Textfelder auf die F-Spalte beziehen und auf G verschoben werden sollen. Falls es möglich ist, wäre es aber wohl für zukünftige Zwecke und andere Leute, welche diesen Thread entdecken hilfreicher, möglichst allgemein zu halten.
Gibt es auch hierfür eine Lösung das für alle Textfelder zu machen?
Freue mich über alle Antworten!
Danke schon im Voraus! :)
LG

Bild

Betrifft: AW: Zellbezüge von Textfeldern verschieben
von: Rudi Maintaire
Geschrieben am: 03.11.2015 08:55:36
Hall,
als Anregung:

Sub aa()
  Dim s As Shape
  Application.ScreenUpdating = False
  For Each s In ActiveSheet.Shapes
    If s.Type = msoTextBox Then
      s.Copy
      ActiveSheet.Paste
      With Selection
        .Left = s.Left + s.TopLeftCell.Width
        .Top = s.Top
        .Formula = Replace(s.DrawingObject.Formula, "$F$", "$G$")
      End With
    End If
  Next
End Sub

Gruß
Rudi

Bild

Betrifft: AW: Zellbezüge von Textfeldern verschieben
von: Simon Privat
Geschrieben am: 03.11.2015 09:22:38
Hey Rudi,
Danke für die Antwort :)
Es funktioniert bis zum updaten der Formel, dann jedoch kommt ein Runtime error 1004: Unable to set the Formula property of the TextBox class.
Eine Idee, woran das liegen könnte?
Verstehe ich das richtig, dass das Macro vom ursprünglichen Shape (also der Textbox) die Formel nimmt und dann $F$ durch $G$ ersetzt?
LG

Bild

Betrifft: AW: Zellbezüge von Textfeldern verschieben
von: Rudi Maintaire
Geschrieben am: 03.11.2015 09:26:08
Hallo,
a) kein Schimmer
b) ja
Beispieldatei?
Gruß
Rudi

Bild

Betrifft: AW: Zellbezüge von Textfeldern verschieben
von: Simon Privat
Geschrieben am: 03.11.2015 11:07:52
Bin am verzweifeln.
Versuche die ganze Zeit, eine Beispieldatei zu erstellen. Habe dazu einige Textboxen kopiert und auf einem separaten sheet in der Datei daten eingefügt, auf die die Textboxen zugreifen.
Wenn ich das Makro hier laufen lasse funktioniert es, in meiner ursprünglichen Datei aber nicht....

Bild

Betrifft: AW: Zellbezüge von Textfeldern verschieben
von: Simon Privat
Geschrieben am: 03.11.2015 11:17:33
Also ich habe nun rausgefunden, wieso das bei der Beispieldatei funktioniert und bei der ursprünglichen nicht.
Nachdem ich die Zellbezüge mit dem vorherigen Makro auf das andere Tabellenblatt verändert habe ist zwischen dem = und dem Zellblattbezug aus für mich unerfindlichem Grund immer ein Leerzeichen. Entferne ich dieses Leerzeichen, funktioniert das Makro....
Ich müsste nun also für alle Textfelder das Leerzeichen nach dem = entfernen, oder aber das erste Makro so umschreiben, dass dieses Leerzeichen erst garnicht entsteht.
Liebe Grüße

Bild

Betrifft: AW: Zellbezüge von Textfeldern verschieben
von: Rudi Maintaire
Geschrieben am: 03.11.2015 11:33:51
Hallo,
das erste Makro so umschreiben, dass dieses Leerzeichen erst gar nicht entsteht.


Sub aaa()
       Dim s As Shape, t
       Application.ScreenUpdating = False
       For Each s In ActiveSheet.Shapes
         If s.Type = msoTextBox Then
           With s.DrawingObject
             If Len(.Formula) Then
               t = Split(.Formula, "!")
               .Formula = "='Haupttabellenblatt'!" & Trim(t(UBound(t)))
             End If
           End With
         End If
       Next
     End Sub

Gruß
Rudi

Bild

Betrifft: AW: Zellbezüge von Textfeldern verschieben
von: Simon Privat
Geschrieben am: 03.11.2015 13:01:35
Hammer :) Vielen Dank! Weiß es sehr zu schätzen, dass sich einige Leute hier die Mühe machen! Großes Kompliment an die Community
Nur zum Verständnis:
Kannst mir jemand erklären, was der Teil
If Len(.Formula) Then
t = Split(.Formula, "!")
aus dem Macro genau macht?
LG und nochmal Danke!

Bild

Betrifft: AW: Zellbezüge von Textfeldern verschieben
von: Rudi Maintaire
Geschrieben am: 03.11.2015 13:13:05
Hallo,
wenn die Länge der Formel >0 (also vorhanden) ist, dann teile die Formel am ! und weise sie der Variablen t zu.
Gruß
Rudi

Bild

Betrifft: AW: Zellbezüge von Textfeldern verschieben
von: daniel
Geschrieben am: 03.11.2015 13:16:10
Hi
in der Regel ist die Formel (.Formula) eines Objektes ein Text, der folgendem Schema entspricht:
Tabellenblattname - Ausrufezeichen - Zellbezug
dh konkret beispielsweise; "Tabelle1!A1"
Split zerlegt einen Textstring am angegebenen Trennzeichen in ein Array, dh aus "tabelle1!A1" wird dann
t(0) = "Tabelle1"
t(1) = "A1"
alternativ hätte man die Zeile

.Formula = "='Haupttabellenblatt'!" & Trim(t(UBound(t)))
auch so schreiben können:
.Formula = "='Haupttabellenblatt'" & mid(.Formula, InstrRev(.Formula, "!"))

das

If Len(.Formula) Then

prüft einfach nur, ob überhaupt eine Formel vorhanden ist, wenn nein ist die Länge des Formeltextes = 0 und die Zahl 0 wird als FALSCH interpretiert wenn sie als Boolscher Wert verwendet wird (und jede andere Zahl als WAHR)
Gruß Daniel

Bild

Betrifft: AW: Zellbezüge von Textfeldern verschieben
von: Simon Privat
Geschrieben am: 04.11.2015 12:10:46
Verstanden :)
Ich danke euch beiden!!
LG

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Zellbezüge von Textfeldern verschieben"