[Solved] Clear unprotected cells in calc w/basic macro

[GWS]

Is there a simple way to clear all the cells in a range that are unpotected? Want to clear out a spred sheet for next years entries.

Could "scan" the range and check each cell status for unprotect and then do a delete. Found a search and replace but don't see how to check for protect.

Thanks

Gary

[Villeroy]

Use a template.

[GWS]

If I understand templates they are a fixed layout to be reused as a starting place. Is this correct?

If so this will not work as the data and possibly the number of rows will change during the year. Thus the procedure must retain the present (protecred cells) state of the sheet but clear all the filled in (unprotected cells) monthly entries made during the year.

This gives a "clean" sheet with all the protected data in tact for the next years entries.

Did this in 123 years ago by checking each cell in the range and clearing the unprotected cell one by one. This will likely work in Oo but is slow in 123.

Thanks

Gary

[Villeroy]

Use a database for many years. Queries can calculate results for the existing records of one year (or any other time span).

[Villeroy]

How to clear unprotected cells:
Use styles for the protection status.
menu:Find>Replace...
[More Options...]
[x] Search for Styles
Pick your unprotected style and find all occurrences.

Hit the backspace key.

[B Marcelly]

Hi,
I found out that clearing a cell individually works on unprotected cells (of course) and does not throw an error on protected cells (which stay unchanged of course).

A simple loop works. Here 4000 cells are scanned in less than a second, only a few cells are unprotected.

Code: Select all

Sub clearZoneCells
Dim sh1 As Object, zone As Object, rAddr As Object
Dim x As Long, y As Long

sh1 = ThisComponent.Sheets(0)
zone = sh1.getCellRangeByName("A1:H500")
rAddr = zone.RangeAddress
for x = rAddr.StartColumn to rAddr.EndColumn
    for y = rAddr.StartRow to rAddr.EndRow
    	sh1.getCellByPosition(x,y).String = ""
    next
next
End Sub

______
Bernard

[Villeroy]

For arbitrary formatted ranges or sheets it would be more efficient to getUniqueFormatCellRanges which returns an array of SheetCellRanges with equal formattings, check if the respective element is unlocked, then element.clearContents(c.s.s.CellFlags).
http://wiki.services.openoffice.org/wik ... mat_Ranges
http://api.openoffice.org/docs/common/r ... anges.html
http://api.openoffice.org/docs/common/r ... arContents

Code: Select all

sub clearUnprotectedRanges(obj)
REM pass a range or sheet object
with com.sun.star.sheet.CellFlags
	flags = .VALUE + .STRING + .FORMULA
end with
ufr = obj.getUniqueCellFormatRanges()
e = ufr.createEnumeration()
while e.hasMoreElements()
	rgs = e.nextElement()
	if NOT rgs.CellProtection.IsLocked then
		rgs.clearContents(flags)
	endif
wend
End Sub

[GWS]

Looks like either version will work. I am only checking 400 to 500 cells so Marcelly's suggestion would be nearly instant.

Villeroy, I haven't tried either but don't understand the "unique" point. Are you refering to the protect/unprotect flag?

Thanks to both.

Gary

[Villeroy]

Install this add-on.
http://ooomacros.org/user.php#221020
It can show the UniqueCellFormatRanges in a GUI.

"advertisment": http://user.services.openoffice.org/en/ ... 878#p49878

[GWS]

Sorry but I am lost!

Villeroy, I clicked your link and got to where it ask to add the extension which I clicked. It then opens a file list to OPEN a file but no SpecialCells any where.

How do I get to the file?

Thanks

Gary

[Villeroy]

DOwnload the zip archive, extract the extension (oxt) and install the extension (menu:Tools>Extensions...).
Restart the office, get a populated and heavily formatted spreadsheet. Play around with menu:Tools>Add-Ons>SpecialCells.
The grouped mode of the formatting dialog shows the uniqueFormatRanges in the yellow list, denoted by their style names. The right list lists the ranges of the selected uniqueFormatRanges.
All ranges in a uniqueFormatRanges share the same attributes. My macro loops through all uniqueFormatRanges (the yellow list) within the given range (the range selection when calling the tool), and clears all cell contents if the ranges of a uniqueFormatRanges are not locked.

[GWS]

Got to the "uniqueFormatRanges" area but will try to work on a data base as suggested by Villeroy.

Will post on Base forum some questions I need help on for the set up of the various tables required.

Thanks

Gary

[edwards142]

My problem is slightly different from yours. In my case, I suddenly started getting Unprotected Formula Error in my Excel workbook. When I searched a lot I found that this problem mainly occurs because all cells are locked and kept protected for unauthorized or accidental changes. In such cases, worksheet cells having formula is not locked for protection.

After doing lot of research now I know how to [b][url=httpx://www.repairmsexcel.com/blog/fix-excel-unprotected-formula-error]remove Unprotected Formula error from Excel[/url][/b]. You can also follow the fixes mentioned in this post.