OpenOffice Macros: The Worksheet_Activate Method: How to simulate Excel's Worksheet Activate Method in OpenOffice Calc

OpenOffice.org Calc does not have a Worksheet_Activate method, but don't let that be a show stopper when migrating from Microsoft Excel.

Migrating from Microsoft Excel macros to OpenOffice.org Calc macros can be a tortuous affair - mainly because each uses their own version of the Basic programming language, and these versions are nearly (but not quite) the same.

For example, take Excel's built in Worksheet_Activate subroutine - this is very useful because it runs every time a user changes form one work sheet to another. However, Calc doesn't have a Worksheet_Activate subroutine - posing a potential show stopper for any migration.

But that's where an OpenOffice listener comes in - with this a Calc spreadsheet macro developer can create their own Worksheet_Activate subroutine.

The OpenOffice.org Listener

An OpenOffice.org listener is simply a process that runs in the background and which sits and waits for particular events - in this case the event will be the user activating one of the work sheets in the Calc spreadsheet.

A Macro for the Listener to use

Before adding the OpenOffice Calc listener it is worthwhile creating the macro that is going to run when the listener fires (i.e. when it sees the event that it is looking for); the reason for creating the macro first is that it allows the user to test it easily, something like:

Sub show_relevant_sheet

Dim oSheet

oSheet = thisComponent.getCurrentSelection.getSpreadSheet

Select Case oSheet.Name

Case "Sheet1"

thiscomponent.sheets.getByName("Sheet2").isVisible = True

thiscomponent.sheets.getByName("Sheet3").isVisible = False

Case "Sheet2"

thiscomponent.sheets.getByName("Sheet1").isVisible = False

thiscomponent.sheets.getByName("Sheet3").isVisible = True

Case "Sheet3"

thiscomponent.sheets.getByName("Sheet1").isVisible = True

thiscomponent.sheets.getByName("Sheet2").isVisible = False

End Select

End Sub

The idea behind this macro is quite straight forward - it gives a simple work flow from one sheet to another:

if Sheet1 is activated then Sheet3 is hidden and sheet2 is visible
if Sheet2 is activated then Sheet1 is hidden and sheet3 is visible
if Sheet3 is activated then Sheet2 is hidden and sheet1 is visible

Once the user has tested this macro thoroughly then the next stage is to add the listener itself.

Creating an OpenOffice Listener

The code for the listener is quite simple, and needs to do three things:

define the listener as a global object
define a macro that will start the listener
define a macro that the listener will run

So, the code will be something like:

Dim oListener As Object

Sub setListenerOn

oListener = createUnoListener ("SHEET_","com.sun.star.beans.XPropertyChangeListener")

thiscomPonent.CurrentController.addPropertyChangeListener ("ActiveSheet",oListener)

End Sub

Sub SHEET_propertyChange(oEvent)

show_relevant_sheet

End Sub

There's one critical point to take note of here - the first parameter passed to the createUnoListener method is actually the prefix for the name of the subroutine that the listener will run - the subroutine must then end in the string propertyChange; so in this case the name formed is SHEET_propertyChange.

Conclusion

Even though OpenOffice Basic does not include built in subroutines such as Worksheet_Activate this is not a show stopper when it comes to migrating from Microsoft Excel macros to OpenOffice macros - all a user has to do is to add their own listener to do the job.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---