May I get the coordinates of the current cell as a variable?

English
, ,
#1

A user-defined function for OpenOffice Calc

I’m trying to write a Basic function that could return the address of the currently selected cell.

If placed in a cell, the function should keep showing the address of the selected cell, refreshing it when I select a new one. It should behave like the information in the “Active cell reference” box, shown within standard OpenOffice toolbars, on the left side of the formula bar. Only in my case I need not only to see it but also manage it as a variable.

Any suggestion?

What I’ve written so far is the code below. It works but it keeps showing the coordinates of the same cell where I place the function.

REM  *****  BASIC  *****
Private Function getCellAddress()
    oActiveCell = ThisComponent.getCurrentSelection()
    oConv = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
    oConv.Address = oActiveCell.getCellAddress
    getCellAddress = oConv.UserInterfaceRepresentation
End Function

All suggestions will be appreciated :pray::wink:

#2

Dont describe how you wish to solve a problem, describe the Problem!

#3

What’s “the current cell”? “The currently selected cell” will be ambiguous in many cases: Often a range of more than one cell is selected - or no cell at all is actually selected, (though the.CurrentSelection will give the one cell having the focus in this apecial case).

#4

The “currently selected cell” or “currently selected range” is what you can read in the “Active cell reference” box, shown on the left side of the standard LibreOffice formula bar.

#5

If you definitely need a refresh “onSelectionChanged”, you will need to use the respective sheet event.
However, I don’t understand for what this should be necessary. If you sit in front of the screen, you anyway see where the focus is. If a formula should depend on the current focus, simply use the function in the proper place inside the formula… If you want to get higher efficiency, you can refer to a cell containing the formu8la simply calling the function, and the referenced value will be recalculated automatically when used…

#6

This of mine is a sort of experiment.
I’m trying to obtain an “intelligent” behaviour from data-validation drop-down lists.
I aim to get the elements of the list from a cell range. The content of the latter should depend on the coordinates of the current row or column.
A drop-down list whose content depends on the context is a pretty common task with a PHP page and a DBMS: I’m trying to obtain a similar behaviour with a spreadsheet.

#7

If your dropdown is context-sensitive in the mentioned way, it will use a calculated reference to a cell range. The calculation simply needs to call the function In its formula, and no extra refresh will be needed.
The problem with a validity range in Calc is then that you need to have selected the cell for which the validity is defined. If this is the cell you want to base the range on, there should be simpler means. If that is a different cell the concept may get very unhandy. You would need to select an additional cell telling the validity formula what range to use. With this respect a spreadsheet will behave very differently as compared to a DBMS form or whatever.
If you describe more precisely what you actually want to achieve, a simple solution might be found.
Context dependent validity CellRange isn’t a problem. Problems come from “How to pass the parameters”.

#8

I didn’t dare to ask for more help, but since you are kindly offering… :wink:
My whole problem is synthesised in this file:
link text

Again many thanks for your time. :pray::+1::blush:

#9

Sorry. I simply don’t understand.
Your sheets obviously try to estrablish a surrogate for a database where you have two data tables and one sheet with “relations”. Semantically any of these relations shall tell something like
name HAS oneoftheitemsdescribedbycode WITH serialNumber
and you want to get the available serial numbers selectable from a “dropdown”.
However, many itemcodes haven’t assigned any serialnumnber, while others have assigned a list.
Calc Validity can’t use a textual list of this kind directly.
Recent versions would accept a formula splitting such a textual list into an arraya of entries, but Calc still hasn’t a standard function for the purpose.
Anyway: If you do the splitting by a user function (I might supply), the Validity dropdown would always also offer entries for tools already entrusted to someone else.

#10

Having converted your sheet “Stock” to a sufficiently normalized form, you could mark enrtrusted tools there by formulas, and based on that exclude them from the dropdown.
I’m afraid your attempt is too ambiguous (complicated), and a solution by an actual DB would be preferrable. After all, the task is all but unique to your shop.
BTW. Basical normalization is always a good idea.
(I can no longer see in what way the results you want to achieve are lelated to the subject of this thread.)

#11

My idea was to create a one-column cell range (let’s call it serialsRange) to be populated, by formulas, with all the serial numbers (if any) of a certain item in Stock.

The selection of the item whose serial numbers should be exploded in such a list would depend on the Item I’m editing in the Equipment sheet.

This is the sequence I’ve imagined:

  1. I start adding, or editing, a row in the sheet Equipment;

  2. if I could rely on a variable containing $Equipment.D{row-number}, where {row-number} is the line coordinate mentioned in the original post, I would use it to refresh the content of serialsRange, so that, when I click on the drop-down button, I may see only the serials related to the item I’m working on.

I’m not sure to have explained it in a comprehensible way. :relaxed:

#12

The way you tried it, you need to convert the comma-separated lists into sequences (in the Sense of the software). Since there isn’t a standard function for the purpose, you need to either:

a) Write such a function [or use my old XTEXTSPLIT()]
b) Use complex formulas to workaround the lack of such a function based on REGEX() [V6.2 or higher] probably.
c) Reorganize your Stock sheet to get one row per serial number. (What I called “normalize” earlier.)

Only the option c will open a usable way to also exclude a serial number from the validation dropdown if it already was assigned to a different trustee.

Think it through this way: You haven’t tool-classes on stock, but single tools, one by one. An adequate designator for such an item should be a class-identifier combined with one serial-number (if needed.)
That’s what “normilzed representation” means in the context.

#13

Of course, you are right about normalization, but I realize now that I haven’t yet explained my point.

Here is an updated version of my previous file

Try this

  1. click on cell F4 dropdown list, and you will see the very set of serial numbers related to the item in row number 4

  2. now click on cell F5 dropdown list, and you will keep seeing the previous set, still related to the item in row number 4

  3. to see the actual set of serial numbers for the item in row 5, you have to change manually the address written in I2.

Got the point? When you replace the address in I2 the list below gets refreshed. You can try the value in I2 with F6 and see the effect.

I’m trying to have the list refreshed not from the address written in I2, but automatically from the address of the cell I’m working on.

Again thanks for your assistance :wink::+1:

#14

Sorry. I won’t create a google account to get your new example.
If you trust in Google, why don’t you use “google sheets”? That should be great though I can’t be sure.
(I distrust Google and their strategies very much though some of my former students are working for them.)

#15

Point taken :wink:

You may try to download the same file here.

Yours gratefully

#16

See the annex to my answer.
And, please, accept the attached .ods as my final contribution to this thread.
I still don’t think it’s of much use to to make the results in a specific range depend on the cell currently having the focus (selection inapt; can be multi-cell). Insofar my suggestions are OT regarding the wording of your question.
Use helpers per row preparing the dropdown-range for every row.
(A professional solution would use a DB anyway.)

#17

You’ve already provided a lot more support than I expected for my little issue.

Again thanks for the time and take care.

#18

Call ActiveCell() function with first param 1 or 2 or 3 from event handler like as

Sub onActiveCellChanged(oEvent As Variant)
Const RESULT_CELL = "A1"
	oEvent.getSpreadsheet().getCellRangeByName(RESULT_CELL).setString(ActiveCell(3))
End Sub

Assign this as a handler for the “Selection Changed” sheet event

EventSelectionChanged.png

#19

@JohnSun: Any serious reason to use ~25loc for that?

def selection_changes(event ):
    target = event.Spreadsheet.getCellRangeByName("A1")
    target.String = event.AbsoluteName.split(':')[0] #to hide Range-selections
#20

@karolus Please try select any ranges with Ctrl instead single cell or single range. Or get address of active cell on prev (or next) sheet. BTW, for a range active cell is not top-left - drag selection from bottom to top or from right to left and the result will be wrong

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.