[Solved] For Each Loop

[vbuser7777]

I need to re-write the following vba code to be compatible with OpenOffice Basic. Any ideas?

Code: Select all

Private Sub Auto_Close()

ActiveSheet.Unprotect Password:="xxxxxx"

For Each c In Worksheets("WI Personal Property Form").Range("A7:E2000").Cells
   If IsEmpty(c) Then
      c.Locked = False
   Else
      c.Locked = True
   End If
Next

ActiveSheet.Protect Password:="xxxxx"

ActiveWindow.Close Savechanges:=True

End Sub

[Hagar Delest]

Hi and welcome to the forum!

Do you really use OOo 2.0??? This version is really deprecated now. Latest is 3.2.1 and latest 2.x branch is 2.4.x. You should upgrade.

Moved to the macro forum BTW.

[vbuser7777]

Actually, I just spoke to IT and apparently I have an outdated version. I'm getting upgraded to 3.0.1.

Thanks for raising the question.

[Hagar Delest]

Why not 3.2.1?

[vbuser7777]

They've not upgraded to that, I guess. Our headquarters (where I am) is on MS Excel. Our branch offices are using OpenOffice.

[Charlie Young]

Well, I'm on 3.2.1. Hopefully the code here won't be affected.

I'm slightly confused by the VBA code though - it unprotects the ActiveSheet, then operates on one called "WI Personal Property Form," which implies to me that the latter is the same as the former. Perhaps I am missing something.

In any case, though ooBasic does have a For...Each these days, I do not see how it can be used in this case, because the Cells collection refers to all of the used cells on a sheet. This shouldn't be much of a problem though, just loop through all the cells in A7:E2000.

Code: Select all

Private Sub Auto_Close()
	Dim ActiveWindow As Object
	Dim ActiveSheet As Object
	Dim WI_Personal_Property_Form As Object
	Dim c As Object
	Dim p As New com.sun.star.util.CellProtection
	Dim col As Long 
	Dim row As Long 
		
	ActiveWindow = ThisComponent
	ActiveSheet = ActiveWindow.CurrentController.ActiveSheet
	ActiveSheet.unprotect("xxxxxx")

	WI_Personal_Property_Form = ActiveWindow.Sheets.getByName("WI Personal Property Form").getCellRangeByName("A7:E2000")
	For col = 0 to WI_Personal_Property_Form.Columns.Count - 1
		For row = 0 to WI_Personal_Property_Form.Rows.Count - 1
			c = WI_Personal_Property_Form.getCellByPosition(col,row) 
			p = c.CellProtection
			If c.getType() = com.sun.star.table.CellContentType.EMPTY Then
				p.IsLocked = False
			Else
				p.IsLocked = True
			End If
			c.CellProtection = p
		Next row
	Next col

	ActiveSheet.protect("xxxxxx")
	ActiveWindow.store()
	ActiveWindow.Close(True)

End Sub

I don't think the Private on the Sub declaration is relevant here.

[vbuser7777]

Thanks for this, Charlie. It works! However, I can run the code manually, and it does everything perfectly. However, it won't run when the document closes.

Any thoughts on that front?

[Charlie Young]

I haven't worked in Excel for a little while. I had forgotten its way of attaching macros to events.

To get my auto_close to act when the document closes, go to Tools > Customize > Events and associate the macro with "Document is going to be closed." You will want to remove the ActiveWindow.Close line at the bottom, as some other process is handling that. The user?

[vbuser7777]

That did it! It's working perfectly now. Thanks so much for the help with (and the lesson in) OpenOffice Basic. I'm sure this won't be my last question posed in this community. Thanks again.