I want to call a macro from a cell in a spreadsheet, and I want to access the particular cell to use its contents in the macro. It seems an obvious thing to want to do, but I can’t seem to find out how. Can someone tell me how to address ‘current cell’ or even ‘current sheet?’
sub test theSelection=ThisComponent.CurrentSelection if NOT theSelection.SupportsService("com.sun.star.sheet.SheetCell") then exit sub theCell=theSelection msgbox "Selected: "+theCell.AbsoluteName end sub
You will find a lot of very valuable information in the texts by Andrew Pitonyak.
Many thanks, Lupp. I think that is perfect. Andrew Pitonyak’s OOME is my Bible for basicmacro, but I missed this, although it now pops up straight away.
Toggle the text in the current cell.
sub test theSelection=ThisComponent.CurrentSelection if NOT theSelection.SupportsService("com.sun.star.sheet.SheetCell") then : exit sub : End If theCell=theSelection 'msgbox "Selected: "+theCell.AbsoluteName If UCase(theCell.String) = "" Then : theCell.String = "Debit" : Exit Sub : End If If UCase(theCell.String) = "DEBIT" Then : theCell.String = "Credit" : Exit Sub : End If If UCase(theCell.String) = "CREDIT" Then : theCell.String = "Debit" : Exit Sub : End If end sub
That’s also OK.
What means “current cell”? Cell with calculated formula at now? Or active cell (outlined bold border)?
REM Function to determine the active cell REM Parameters: REM iSheet - the index of the sheet to which the active cell is searched. REM Isn't required. By default - the active sheet REM oDoc - workbook, for which the active cell is searched. REM Isn't required. Default - the current document REM Returns a reference to the active cell (or Null if it isn't possible to determine) REM Author: Vladislav Orlov aka JohnSUN, Ukraine, Kiev, 2012 REM by UROS> http://www.oooforum.org/forum/viewtopic.phtml?t=19348 REM by Villeroy> http://user.services.openoffice.org/en/forum/viewtopic.php?f=20&t=38672 # p177590 Function ActiveCell (Optional iSheet As Long, Optional oDoc As Variant) As Object Dim arrayOfString () ' An array of text strings to parse Dim lRow&, lColumn& ' The coordinates of the desired cell Dim tmpString$ ' Time line Dim oCurrentController ' controller of the analyzed document Dim oSheets As Variant ' All the pages of the current book Dim oSheet As Variant ' Active (test) page of the book REM Check input parameters and set the default values : If IsMissing (oDoc) Then oDoc = ThisComponent If NOT oDoc.SupportsService ("com.sun.star.sheet.SpreadsheetDocument") Then Exit Function oCurrentController = oDoc.getCurrentController() If IsMissing (iSheet) Then oSheet = oCurrentController.getActiveSheet() iSheet = oSheet.getRangeAddress().Sheet Else If (iSheet < 0) Then Exit Function oSheets = ThisComponent.getSheets() If (iSheet>= oSheets.getCount ()) Then Exit Function oSheet = oSheets.getByIndex (iSheet) EndIf tmpString = oCurrentController.getViewData() arrayOfString () = Split (tmpString, ";") If UBound (arrayOfString) <( 3 + iSheet) Then Exit Function tmpString = arrayOfString ( 3 + iSheet) If InStr (tmpString, "+")> 0 Then arrayOfString () = Split (tmpString, "+") Else arrayOfString () = Split (tmpString, "/") EndIf lColumn = CLng (arrayOfString ( 0) ) lRow = CLng (arrayOfString ( 1) ) Set ActiveCell = oSheet.getCellByPosition (lColumn, lRow) End Function
Thank you very much, JohnSUN. More complicated than I required, but useful code.