Ask Your Question

Access current cell from macro in Calc (solved)

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

Pansmanser gravatar image

updated 2016-02-20 11:16:36 +0100

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 close merge delete

2 Answers

Sort by » oldest newest most voted

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

Lupp gravatar image
sub test
if NOT theSelection.SupportsService("")  then exit sub
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


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

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 Villeroy> # 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 ("") Then Exit Function
    oCurrentController = oDoc.getCurrentController()
    If IsMissing (iSheet) Then
        oSheet = oCurrentController.getActiveSheet()
        iSheet = oSheet.getRangeAddress().Sheet
        If (iSheet < 0) Then Exit Function
        oSheets = ThisComponent.getSheets()
        If (iSheet>= oSheets.getCount ()) Then Exit Function
        oSheet = oSheets.getByIndex (iSheet)
    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, "+")
        arrayOfString () = Split (tmpString, "/")
    lColumn = CLng (arrayOfString ( 0) )
    lRow = CLng (arrayOfString ( 1) )
    Set ActiveCell = oSheet.getCellByPosition (lColumn, lRow)
End Function
edit flag offensive delete link more


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

Pansmanser gravatar imagePansmanser ( 2016-02-20 11:11:32 +0100 )edit
Login/Signup to Answer

Question Tools



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

Seen: 2,371 times

Last updated: Feb 20 '16