[Gelöst] Makro zur Bestimmung, ob Formel in Zelle

[oerki]

Hallo zusammen,

ich möchte gerne ein Makro schreiben, das mir nur die Zellen ausblendet und schreibschützt, in denen Formeln stehen.

Ich habe mehrere Mappen, die im Bereich A1 bis Z100 geschützt sind. Diese kann ich über aber (mit Hilfe eines Dialogs und mit dem richtigen Passwort wieder entsperren). Allerdings soll der Bearbeiter dann nicht die Formel löschen können.

Bisher habe ich das und das funktioniert auch ohne Probleme:

Code: Alles auswählen

Sub Auto_Zellsperre_aus

		for MP = 1 to Month(Date)-2
		'Msgbox MP
			osheet = thisComponent.getSheets().getByIndex(MP)  ' die Tabelle
			oSheet.unprotect("schutz")  'Tabellenschutz aufheben - PW kann auch ein leerer String sein
			oZelle = oSheet.getCellrangeByName("A1:Z100")   'die Zelle A1
			oCellSchutz = oZelle.CellProtection  'das Zellschutzobjekt
			oCellSchutz.IsLocked = false   'Zellschutz setzen
			oCellSchutz.IsFormulaHidden = true   'Formeln ausblenden
			oZelle.CellProtection = oCellSchutz   'Zellschutzobjekt zurückschreiben
			oSheet.protect("schutz")   'Tabelle wieder schützen
		next MP
		
End Sub

Und

Code: Alles auswählen

Sub Auto_Zellsperre_an

		for MP = 1 to Month(Date)-2
		'Msgbox MP
			osheet = thisComponent.getSheets().getByIndex(MP)  ' die Tabelle
			oSheet.unprotect("schutz")  'Tabellenschutz aufheben - PW kann auch ein leerer String sein
			oZelle = oSheet.getCellrangeByName("A1:Z100")   'die Zelle A1
			oCellSchutz = oZelle.CellProtection  'das Zellschutzobjekt
			oCellSchutz.IsLocked = true   'Zellschutz setzen
			oCellSchutz.IsFormulaHidden = true   'Formeln ausblenden
			oZelle.CellProtection = oCellSchutz   'Zellschutzobjekt zurückschreiben
			oSheet.protect("schutz")   'Tabelle wieder schützen
		next MP
		
End Sub

Das funktioniert auch. Wenn ich "Auto_Zellsperre_aus" ausführe, dann wird der komplette Zellschutz entfernt und die Formeln ausgeblendet. Allerdings kann jeder die Formeln (aus versehen, oder gewollt) löschen und das bringt dann natürlich wenig.

Kann man da ein Makro vorschalten, dass prüft, ob eine Formel in der Zelle ist und dann dort den Zellschutz nicht aufhebt und da wo keine Formel ist den Zellschutz an?

Ich habe bisher viel gesucht und finde nicht den richtigen Ansatz.
Habe folgendes Makro gefunden, allerdings spuckt das mir auch einen Wert aus, wenn keine Formel, sondern ein sonstiger Eintrag in der Zelle steht.

Code: Alles auswählen

Sub getCellInfo
'get the first sheet of the spreadsheet doc
xSheet = ThisComponent.Sheets(0)

'Get  Cell A1
oCell = xSheet.getCellByPosition(0,0)

'It has a numeric value, a string 2 versions of a formula and an error:
print oCell.value, oCell.string,oCell.formula, oCell.FormulaLocal, oCell.Error

End Sub

Vielleicht ist es ja ganz einfacht und es kann mir einer auf die Sprünge helfen?

Vielen Dank schon einmal!

VG
Steffen

[Stephan]

Code: Alles auswählen

Sub Main
tmp = ThisComponent.Sheets(0).getCellRangeByName("A1:Z100")
zellen = tmp.queryFormulaCells(7) '7, siehe: http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/FormulaResult.html
For each zelle in zellen.Cells
	'tue was mit zelle
Next 
End Sub

Gruß
Stephan

[oerki]

Hallo Stephan,

das hilft schon mal, ich erkenne das Prinzip, aber irgendwie macht das Makro es nur ganz, oder gar nicht...

Ich habe deins und meins kombiniert und habe folgendes probiert:

Code: Alles auswählen

Sub Main

tmp = ThisComponent.Sheets(0).getCellRangeByName("A1:Z100")
zellen = tmp.queryFormulaCells(7) '7, siehe: http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/FormulaResult.html
For each zelle in zellen.Cells

         osheet = thisComponent.getSheets().getByIndex(0)  ' die Tabelle
         oSheet.unprotect("schutz")  'Tabellenschutz aufheben - PW kann auch ein leerer String sein
         oZelle = oSheet.getCellrangeByName("A1:Z100")   'die Zelle A1
         oCellSchutz = oZelle.CellProtection  'das Zellschutzobjekt
         oCellSchutz.IsLocked = true   'Zellschutz setzen
         oCellSchutz.IsFormulaHidden = true   'Formeln ausblenden
         oZelle.CellProtection = oCellSchutz   'Zellschutzobjekt zurückschreiben
         oSheet.protect("schutz")   'Tabelle wieder schützen

Next 
End Sub

Wenn irgendwo eine Formel in dem Bereich steht, dann wird es ausgeführt und sperrt den ganzen Bereich "A1:Z100".
Wenn keine Formel in dem Bereich steht, dann wird nichts ausgeführt und keine einzige Zelle gesperrt.

Aber jetzt die Frage, wie wird nur die eine Zelle gesperrt in der die eine Formel steht, bzw. jede Zelle in dem Bereich, in dem eine Formel steht? Und die anderen Zellen sollen nicht gesperrt werden...

Wäre um weitere Hilfe echt dankbar. Ich komme mit dem Thema einfach nicht zum Ende.

VG
Steffen

[Stephan]

Ich habe deins und meins kombiniert und habe folgendes probiert:
...

Hä?
Ich ging natürlich davon aus das Du Dich (zumindest etwas) mit Makros auskennst wenn Du hier zu Makros fragst.

so wie folgt sollte das Gleiche wie in Deinem Makro "Sub Auto_Zellsperre_an" nur für die Zellen mit Formeln geschehen:

Code: Alles auswählen

Sub Main

ThisComponent.Sheets(0).unprotect("schutz")

tmp = ThisComponent.Sheets(0).getCellRangeByName("A1:Z100")
zellen = tmp.queryFormulaCells(7)
For each zelle in zellen.Cells
   oCellSchutz = zelle.CellProtection
   oCellSchutz.IsLocked = true
   oCellSchutz.IsFormulaHidden = true
   zelle.CellProtection = oCellSchutz 
Next 

ThisComponent.Sheets(0).protect("schutz")
End Sub

Gruß
Stephan

[oerki]

Es funktioniert!

Hä?
Ich ging natürlich davon aus das Du Dich (zumindest etwas) mit Makros auskennst wenn Du hier zu Makros fragst.

Ich würde jetzt nicht sagen, dass ich kompletter Anfänger bin. Aber bisher hatte ich so ein "Problem" noch nicht.
Aber ich verstehe was da jetzt passiert und habe so wieder was dazu gelernt.

Vielen Dank für die Hilfe!!

VG
Steffen