Ask Your Question
0

Access current cell from macro in Calc (solved) [closed]

asked 2016-02-19 21:29:01 +0100

Pansmanser gravatar image

updated 2020-07-21 06:17:28 +0100

Alex Kemp gravatar image

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?'

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-21 06:17:59.891125

2 Answers

Sort by » oldest newest most voted
2

answered 2016-02-19 21:54:32 +0100

Lupp gravatar image
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.

edit flag offensive delete link more

Comments

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.

Pansmanser gravatar imagePansmanser ( 2016-02-20 11:15:20 +0100 )edit

Dear @Lupp,

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.

lonk gravatar imagelonk ( 2020-08-04 19:15:51 +0100 )edit
2

answered 2016-02-20 10:28:00 +0100

JohnSUN gravatar image

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
edit flag offensive delete link more

Comments

Thank you very much, JohnSUN. More complicated than I required, but useful code.

Pansmanser gravatar imagePansmanser ( 2016-02-20 11:11:32 +0100 )edit

Question Tools

2 followers

Stats

Asked: 2016-02-19 21:29:01 +0100

Seen: 4,566 times

Last updated: Feb 20 '16