Spreadsheet Macro: Cell reference to position of "Macro cell" instead of position of active cell

Desktop editors
,
1

Hello everybody

This is a how-to-question about Macro Coding in OnlyOffice Spreadsheets.

Technical Background: OnlyOffice V. 9.4.0 on MacOS and Linux Mint.

Usage Background: I connected one universal Macro to different pictures located in different cells i an OnlyOffice Spreadsheet (way above 200 cells in total). When the universal Macro is started at any of its locations, it will start its work in relation of its actual cell location.

  • E.g.: Pictures are set into cells A1, B1, C1, … F1. One universal Macro is assigned to each of these pictures/cells. The Macro will copy the neighbour cell in the same row. So if the Macro in cell B1 is activated, cell B2 will be copied. Or if the Macro in cell F1 is activated, cell F2 will be copied.
  •     let sourceSheet = Api.GetActiveSheet();

    let activeCell = sourceSheet.GetActiveCell();

    let row = activeCell.GetRow();

    // Values from current sheet
    let value1 = sourceSheet.GetRange("B" + row).GetValue();

My Macro code is based on the location of the “active cell” (see code above). As a result I need to click on the actual cell where the Macro is attached to, so that this very cell is becoming the “active cell”. Then I may click on the picture in this cell to start the Macro.

Problem: The Macro is designed to work in relation to the “active cell” (the cell which is clicked on). But Macros can be started/clicked-on without making it’s current cell the active cell.

  • E.g.: Cell A1 is active. It is the cell where I clicked on the latest. When I start the Macro by pressing the picture in cell E1 this very cell E1 will not become the active cell. The active cell will stay cell A1. As a result the Macro will copy cell A2 instead of E2 because it is designed to do so by code (the code works with the “active cell”).

There is no problem as long as I make the intended cell of the Macro to the “active cell” first (simply by clicking on the cell) and then starting the Macro in this cell. But I am human and make mistakes. Often I forget to make the inteded cell the “active cell” and the results I am getting are not correct, of course.

Goal: I would like to activate a Macro so this Macro will start its task in relation to its current cell position without the need to click on the respective cell first (to make it the “active cell” manually).

Solutions:

  1. Making the intended cell the “active cell” manually before starting the Macro (as describet above). Not the safest solution: I might forget to do this.
  2. Coding Macros with defined cell references (the Macro in cell A1 will reference to it self by using the cell A1 as fixed position in the code; the Macro in cell B1 will be referenced to B1 as well etc.). This will require a specific Macro for every intended cell. Not very handy for larger spreadsheets.
  3. Change the Macro Code in some beneficial way?
  4. Any other solution I am not aware of…

Question: Does anybody see a solution for this topic?

Thanks a lot. Best regards

2

Hello @onlyoffice-user

Basically speaking, you want the macro to run in a reference to a cell the image visually located in, i.e. if an image in cell A1 is clicked, calculate the values in row 1.

I’m afraid this is true, because images are not tied to specific cells, but rather floating on top of them, thus it’s practically impossible to find where specific image is located and on top of what cell it is floating. Think of the image as a button, it just runs the code - if the code requires active cell, then you must click necessary cell before clicking the image and starting the code.

With that said, I’m afraid the this is the only solution for such design.

3

Hello @Constantine

Thank you for clarification. I kind of expected this during my own trouble shooting.

One solution would be to include an additional code into the Macro, so that the Macro would prompt a custom pop-up message/warning if the Macro would be clicked on without its cell position is the active cell. But as far as I am aware of, OnlyOffice does not offer custom pop-up messages for a Spreadsheet, nor is there be a possible code do such a position-check of cells (active cell vs. cell position of the Macro/picture → I draw this conclusion as a result of your technical explenation). Would you agree?

Thanks for your time.