1k Aufrufe
Gefragt in Tabellenkalkulation von
Hallo Zusammen,

ich habe folgendes Anliegen:

Ich habe ein Raumbuch mit mehreren Blättern (u.a. Raumliste, Standardblatt) entwickelt
komme jedoch bei folgender Funktion nicht weiter.
Wenn bspw. in Raumliste in A5 irgendein Text eingetragen wird, soll Excel automatisch
ein neues Arbeitsblatt anlegen, dabei das Standardblatt als Vorlage verwenden und es
identisch der Eintragung aus A5 benennen.
Vorzugsweise sollte gleichzeitig die Eintragung in A5 mit dem neu erstellten Arbeitsblatt
verlinkt sein.

Vielen dank für eure Hilfe im Voraus.

Gruß Stefan

16 Antworten

0 Punkte
Beantwortet von
Hallo Stefan,

so wie du das beschreibst, bleibt die Eintragung nicht bei A5 sondern wird in A6 und A7 fortgeschrieben. Lieg ich da richtig?

Das folgende Makro erstellt das neue Sheet an letzter Stelle und fügt zusätzlich in A1 einen Link ein, der zurück zur Raumliste verweist. In der Raumliste kannst du beliebig viele Räume anlegen. Probiers aus. Bei mir klappts super.

Der Code gehört im VBA-Editor (Alt+F11) in das Modul "Raumliste"
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A:A")) Is Nothing _
And Target.Cells.Count = 1 Then
If Not SheetExists(Target.Value) Then
Sheets("Standardblatt").Copy After:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
.Name = Target.Value
.Hyperlinks.Add .Range("A1"), "", "Raumliste!A1", , "Raumliste"
End With
Target.Parent.Select
End If
Target.Hyperlinks.Delete
Target.Hyperlinks.Add Anchor:=Target, Address:="", _
SubAddress:=Target.Value & "!A1", TextToDisplay:=Target.Value
End If
End Sub

Function SheetExists(n As String) As Boolean
On Error Resume Next
SheetExists = IsObject(Sheets(n))
End Function
Gruß Mr. K.
0 Punkte
Beantwortet von
Sollten deine Räume ausschließlich aus Zahlen bestehen, drück im Codemodul mal STRG+H und ersetze das Wort Value durch das Wörtchen Text
0 Punkte
Beantwortet von
Hallo Mr. K.

Vielen Dank für die Funktion, das klappt perfekt.

Nun hätte ich aber noch ein Problem:

Derzeit sind Raumliste und Standardblatt so verknüpft, dass in der Raumliste
in der jeweiligen Raumzeile in 4 Verschiedenen Zellen Verknüpfungen
hinterlegt sind. Das heißt: wenn ich in dem Standardblatt bei einem Pulldown
Menü eine Auswahl tätige erscheint in einer der 4 Zellen ein Häkchen.

Da diese Blätter noch weitere Verknüpfungen und daraus resultierende
Berechnungen haben die Frage, ob mit der von dir erstellten Funktion die
Möglichkeit besteht, das automatisch alle Funktionen mit übernommen
werden.

Ich hoffe, dass ich das verständlich erklärt habe.

Gruß Stefan.
0 Punkte
Beantwortet von
Hallo Stefan,

tut mir leid, aber so richtig verstehe ich deine Frage nicht.
- Warum änderst du das Standardblatt und nicht den eigentlichen Raum?
- Was meinst du mit "dass automatisch alle Funktionen übernommen werden"? Man könnte die Funktionen in der Raumliste per Code von der überliegenden Zeile kopieren und den Bezug auf das Tabellenblatt (Raum) anpassen. Ist das so gemeint? Falls nicht, nenn doch mal bitte deine Formeln, dann können die auch direkt in den Code eingebunden werden.

Kann mich leider erst heute Abend wieder dazu melden. Bis dann
Mr. K.
0 Punkte
Beantwortet von
Hallo Mr. K. ,

sorry, tatsächlich meinte ich den eigentlichen Raum.
Vielleicht erläutere ich kurz wofür die ganze Datei gedacht ist.

Wir sind ein Planungsbüro und die Bürostruktur soll damit vereinfacht werden,
bedeutet: Wenn wir ein neues Projekt mit Plänen erhalten sollen zunächst alle
Räume erfasst werden. Also Raumnummer, Name, Geschoss, Größe etc.
im Zweiten Schritt soll für jeden Raum eine Raumbuch angelegt werden in
dem erfasst wird welche Gewerke in diesem Raum Verwendung finden
(Einrichtungsgegenstände usw.)
Also Vorlage dient das bereits erwähnte Standardblatt.

Nun zum besagtem Problem:

Im jeweiligen Raumbuch sind 4 Gewerke aufgeführt.
Sobald man unter dem jeweiligen Gewerk etwas ausgewählt wurde (Bsp. 1
Heizkörper) soll in der Raumliste, die später als Übersicht dienen soll, ein
Häckchen neben dem Raum unter dem Gewerk Heizung entstehen, damit
man auf einem Blick erkennt in welchem Raum welche Gewerke Verwendung
finden.
Mit deiner Funktion klappt es ja glücklicherweise schon mal, dass man zu
jedem erfassten Raum nicht aufwändig jeden neuen Raum anlegen muss.

Die Funktion die wir dafür verwendet haben, ist folgende.

=WENN(ODER('-1.06 Technik-HeizungDY'!H25>0;'-1.06 Technik-
HeizungDY'!H27>0;'-1.06 Technik-HeizungDY'!H32;'-1.06 Technik-
HeizungDY'!H35);"a";"")

Zur Info noch dazu: das "a" wird unter der Schriftart Marlett als Haken
angezeigt sodass man später auch noch filtern kann.

Des Weiteren sind im Raumbuch diverse Verweise zu den Daten aus der
Raumliste(Raumnummer, Name, Geschoss, Größe etc.)

Dies war in meiner vorherigen Nachricht mit "weiteren Verknüpfungen"
gemeint deren Funktionen idealerweise mit in der VBA-Funktion entsprechend
Anwendung finden.

Ich hoffe das es nun alles Sinn ergibt.

Bis dann, Stefan.
0 Punkte
Beantwortet von
Hi Stefan,

generell geht mit VBA natürlich so ziemlich Alles und noch viel mehr. Aber ich fürchte, ich habe dich immer noch nicht so ganz verstanden. Ich dachte die 4 Gewerke sind in der Raumliste jeweils in einer separaten Spalte zu sehen. Deine Formel zieht aber alle 4 Gewerke in nur eine Spalte. Außerdem beziehen sich die letzten beiden Paramter der ODER-Formel auf eine Zelle in der entweder WAHR oder FALSCH stehen muss. Ist das so beabsichtigt, oder hast du hier das >0 vergessen?

Damit ich dir hier sinnvoll weiterhelfen kann, solltest du am besten mal eine Beispieldatei mit 3 Räumen und Fake-Daten bei einer Platform deier Wahl hochladen. Ich bevorzuge z.B. die Dropbox.

In der Zwischenzeit kann du ja mal diesen Code probieren.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim shn As String

Raumübersicht = "Raumliste" 'Blattname der die Übersicht aller Räume enthält
Eingabespalte = "A" 'Code startet, wenn in Raumliste in dieser Spalte Eingabe erfolgt
SpalteGewerk1 = "B" 'Hier wird ein Haken gesetzt wenn im Raum dieses Gewerk angegeben
SpalteGewerk2 = "C" 'Hier wird ein Haken gesetzt wenn im Raum dieses Gewerk angegeben
SpalteGewerk3 = "D" 'Hier wird ein Haken gesetzt wenn im Raum dieses Gewerk angegeben
SpalteGewerk4 = "E" 'Hier wird ein Haken gesetzt wenn im Raum dieses Gewerk angegeben

Raumvorlage = "Standardblatt" 'Blattname der die Vorlage zum Kopieren bildet
Gewerk1 = "H25" 'Haken in Raumliste wird gesetzt wenn hier was angegeben
Gewerk2 = "H27" 'Haken in Raumliste wird gesetzt wenn hier was angegeben
Gewerk3 = "H32" 'Haken in Raumliste wird gesetzt wenn hier was angegeben
Gewerk4 = "H35" 'Haken in Raumliste wird gesetzt wenn hier was angegeben
Backlink = "A1" 'Zelle wo der Link zurück zur Raumliste erstellt wird
Raumnr = "C1" 'Zelle für Raumnr.
Raumname = "D1" 'Zelle für Raumname


'ab hier Code
shn = Target.Text 'geplanter Sheetname

If Not Intersect(Target, Columns(Eingabespalte & ":" & Eingabespalte)) Is Nothing _
And Target.Cells.Count = 1 And Len(shn) > 0 Then
shn = CorrectSheetname(shn)
If Not SheetExists(shn) Then
Sheets(Raumvorlage).Copy After:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
.Name = shn
.Hyperlinks.Add .Range(Backlink), "", Raumübersicht & "!" _
& Target.Address(False, False), , Raumübersicht
.Range(Raumnr).NumberFormat = "@" 'Textformat damit . = . bleibt
.Range(Raumnr).HorizontalAlignment = xlRight
.Range(Raumnr) = Left(Target.Text, InStr(1, Target.Text, " "))
.Range(Raumname) = Right(Target.Text, Len(Target.Text) - InStr(1, Target.Text, " "))
End With
Target.Parent.Select
End If
Target.Hyperlinks.Delete
Target.Hyperlinks.Add Anchor:=Target, Address:="", _
SubAddress:="'" & shn & "'!" & Raumname, TextToDisplay:=Target.Text
With Target.Parent.Range(SpalteGewerk1 & Target.Row)
.Formula = _
"=IF(OR('" & shn & "'!" & Gewerk1 & ">0,'" & shn & "'!" & Gewerk2 & ">0," _
& "'" & shn & "'!" & Gewerk3 & ",'" & shn & "'!" & Gewerk4 & "),""a"","""")"
.Font.Name = "Marlett"
.Font.Size = 12
End With
End If
End Sub

Function SheetExists(n As String) As Boolean
On Error Resume Next
SheetExists = IsObject(Sheets(Left(n, 31)))
End Function
Function CorrectSheetname(n As String) As String
m = Left(n, 31)
m = Replace(m, ":", " ")
m = Replace(m, "/", " ")
m = Replace(m, "\", " ")
m = Replace(m, "?", " ")
m = Replace(m, "*", " ")
m = Replace(m, "[", " ")
m = Replace(m, "]", " ")
CorrectSheetname = m
End Function

Gruß Mr. K.
0 Punkte
Beantwortet von
Hey Mr. K. ,

zunächst vielen Dank für den neuen Code, habe jedoch festgestellt, dass er noch nicht zu 100%
funktioniert.
Wird der schlechten Beschreibung meinerseits geschuldet sein.

Unter folgenden Link findest du die Datei.

https://www.dropbox.com/s/5i0mlzs4qp0tk9n/Raumbuch.xlsm?dl=0

Ich hoffe, dass du mit Hilfe der Datei, meine vorherigen Beschreibungen zuordnen kannst.

Für deine Mühe schon mal ein großes Dankeschön.

Bis Dann und

Gruß Stefan
0 Punkte
Beantwortet von
Hi Stefan,

probiers mal mit dieser Datei

https://www.dropbox.com/s/iphtqfzqmqv39nq/Raumbuch2.xls?dl=0

Gruß Mr. K.
0 Punkte
Beantwortet von
Hallo Mr. K. ,

zunächst Sorry für die späte Reaktion.

Die Datei funktioniert sehr gut. Vielen Dank dafür.

Welcher Teil des Codes dient dazu um die Verweise des "Standardblattes" zu übernehmen und wie
könnte man den Code fortschreiben wenn die Datei sich im laufe weiterentwickelt und man weitere
Verweise benötigt?

Vielen Dank im Voraus.

Gruß Stefan
0 Punkte
Beantwortet von
Hallo Stefan,

was meinst du mit: "Welcher Teil des Codes dient dazu um die Verweise des "Standardblattes" zu übernehmen"? Ich hatte mir mühe gegeben, den Code soweit wie möglich zu erklären. Es ist aber immer wieder schwierig, grade für Anfänger, sich in einen fremden Code reinzudenken.

Du kannst leider im Standardblatt keinen Verweis auf die Raumliste setzen, der dann für jeden Raum auf eine andere Zeile verweist. Dazu müsste der Code zunächst diese Formel auslesen, verstehen und verändert wieder einfügen. Da macht es mehr Sinn, diese gleich direkt im Code aufzubauen. Das passiert im Block "legt die Formeln für Raumnr, Raumname, Geschoss und Fläche an" Hier gibst du folgendes ein:

.Range(Zelle für Formel).Formula = "=" & Name des Tabellenblatt, das die Raumliste enthält & "!" & Spalte im Tabellenblatt "Raumliste" auf die du verweisen möchtest& Target.Row

Der umgekehrte Formelverweis von Raumliste auf das Raumblatt passiert im Block 'legt die Gewerkformeln in Raumübersicht an

Target.Parent.Range(Spalte in Raumliste wo etwas Haken erscheinen soll & Target.Row).Formula = _
"=IF('" & shn & "'!" & Zelle im Raum auf die verwiesen wird & "Bedingung,""Dannwert"","Sonstwert")"

Vielleicht noch zur Erklärung. Target ist die Zelle in der du eine Eingabe gemacht hast. Mit Target.Parent wird auf das Tabellenblatt verwiesen, in der sich die Eingabezelle befindet. Anker ist die Zelle in der ein Hyperlink angelegt werden soll. shn ist der Tabellenblattname des neu angelegten Raumes.

Wenn du weitere Fragen hast, einfach melden.
...