Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Macro on leaving a cell

I am trying to change the Tab/Enter order of the cells in a few specific groups of a spreadsheet. The code below kinda works but only if the cell changes and you don't press arrow keys to leave. Is there a way to fix or replace this code to work even if the cell contents don't change and using Enter/Tab/Arrow keys (would still like to be able to use mouse to click elsewhere) And maybe I am an idiot, but I can't seem to get the code to properly format here after several attempts.

Global oModifyListener

Global oCell

dim document as object dim dispatcher as object dim args(1) as new com.sun.star.beans.PropertyValue

REM call this method once to set the ModifyListener. REM to destroy the listener, call oCell.removeModifyListener( oModifyListener ) Sub SetModifyListener() oDoc = ThisComponent If NOT oDoc.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then Exit Sub REM ( change "A1" to the cell which should trigger the callback if modified ) Dim strCellAddress oModifyListener = createUnoListener("CellModify_","com.sun.star.util.XModifyListener") oSheet = ThisComponent.CurrentController.ActiveSheet REM first range strCellAddress = "D1:D10" REM Your Cell Address here. oCell = oSheet.getCellRangebyName( strCellAddress ) oCell.addModifyListener( oModifyListener ) REM seond range
strCellAddress = "H1:H10" oCell = oSheet.getCellRangebyName( strCellAddress ) oCell.addModifyListener( oModifyListener ) End Sub

Sub CellModify_modified( oEvent ) Dim mycol : mycol = CurrentColumn() Dim myrow : myrow = CurrentRow() If mycol = 4 then If myrow = 10 then goThere("E1") ElseIf myrow < 10 then goThere("A"+(myrow+1)) EndIf Elseif mycol = 8 then If myrow = 10 then goThere("A1") ElseIf myrow < 10 then goThere("E"+(myrow+1)) EndIf EndIf
End Sub

Sub CellModify_disposing( oEvent ) REM not sure what this is for, just seems to spam on exit of spreadsheet REM Msgbox "Disposing" End Sub

Function CurrentColumn() As Long Dim ODoc As Object Dim OSel As Object oDoc = ThisComponent oSel = oDoc.GetCurrentSelection() If Not oSel.supportsService("com.sun.star.sheet.SheetCellRange") Then Exit Function If (oSel.Columns().Count() > 1) Then Exit Function CurrentColumn = oSel.CellAddress.Column()+1 End Function

Function CurrentRow() As Long Dim ODoc As Object Dim OSel As Object oDoc = ThisComponent oSel = oDoc.GetCurrentSelection() If Not oSel.supportsService("com.sun.star.sheet.SheetCellRange") Then Exit Function If (oSel.Rows().Count() > 1) Then Exit Function CurrentRow = oSel.CellAddress.Row()+1 End Function

REM it would be nice if I could use coordinates instead of cell name Sub gotoCell goThere("B4") End Sub

Sub goThere(CellName As String) dim args(0) as new com.sun.star.beans.PropertyValue dim oDoc as object dim dispatcher as object

oDoc = ThisComponent.CurrentController.Frame args(0).Name = "ToPoint" args(0).Value = CellName dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") dispatcher.executeDispatch(oDoc, ".uno:GoToCell", "", 0, args())

End Sub

click to hide/show revision 2
None

Macro on leaving a cell

I am trying to change the Tab/Enter order of the cells in a few specific groups of a spreadsheet. The code below kinda works but only if the cell changes and you don't press arrow keys to leave. Is there a way to fix or replace this code to work even if the cell contents don't change and using Enter/Tab/Arrow keys (would still like to be able to use mouse to click elsewhere) And maybe I am an idiot, but I can't seem to get the code to properly format here after several attempts.

 Global oModifyListener

Global oCell

oCell dim document as object dim dispatcher as object dim args(1) as new com.sun.star.beans.PropertyValue

com.sun.star.beans.PropertyValue REM call this method once to set the ModifyListener. REM to destroy the listener, call oCell.removeModifyListener( oModifyListener ) Sub SetModifyListener() oDoc = ThisComponent If NOT oDoc.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then Exit Sub REM ( change "A1" to the cell which should trigger the callback if modified ) Dim strCellAddress oModifyListener = createUnoListener("CellModify_","com.sun.star.util.XModifyListener") oSheet = ThisComponent.CurrentController.ActiveSheet REM first range strCellAddress = "D1:D10" REM Your Cell Address here. oCell = oSheet.getCellRangebyName( strCellAddress ) oCell.addModifyListener( oModifyListener ) REM seond range
strCellAddress = "H1:H10" oCell = oSheet.getCellRangebyName( strCellAddress ) oCell.addModifyListener( oModifyListener ) End Sub

Sub Sub CellModify_modified( oEvent ) Dim mycol : mycol = CurrentColumn() Dim myrow : myrow = CurrentRow() If mycol = 4 then If myrow = 10 then goThere("E1") ElseIf myrow < 10 then goThere("A"+(myrow+1)) EndIf Elseif mycol = 8 then If myrow = 10 then goThere("A1") ElseIf myrow < 10 then goThere("E"+(myrow+1)) EndIf EndIf
End Sub

Sub Sub CellModify_disposing( oEvent ) REM not sure what this is for, just seems to spam on exit of spreadsheet REM Msgbox "Disposing" End Sub

Sub Function CurrentColumn() As Long Dim ODoc As Object Dim OSel As Object oDoc = ThisComponent oSel = oDoc.GetCurrentSelection() If Not oSel.supportsService("com.sun.star.sheet.SheetCellRange") Then Exit Function If (oSel.Columns().Count() > 1) Then Exit Function CurrentColumn = oSel.CellAddress.Column()+1 End Function

Function Function CurrentRow() As Long Dim ODoc As Object Dim OSel As Object oDoc = ThisComponent oSel = oDoc.GetCurrentSelection() If Not oSel.supportsService("com.sun.star.sheet.SheetCellRange") Then Exit Function If (oSel.Rows().Count() > 1) Then Exit Function CurrentRow = oSel.CellAddress.Row()+1 End Function

Function REM it would be nice if I could use coordinates instead of cell name Sub gotoCell goThere("B4") End Sub

Sub Sub goThere(CellName As String) dim args(0) as new com.sun.star.beans.PropertyValue dim oDoc as object dim dispatcher as object

object oDoc = ThisComponent.CurrentController.Frame args(0).Name = "ToPoint" args(0).Value = CellName dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") dispatcher.executeDispatch(oDoc, ".uno:GoToCell", "", 0, args())

args()) End Sub

Sub