Simple retrieve Row and Column from selected cell

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
RMichalowski
Posts: 6
Joined: Thu Apr 30, 2015 10:48 pm

Simple retrieve Row and Column from selected cell

Post by RMichalowski »

Hello,

I would greatly appreciate how to get the current row and column in the spreadsheet based on the selected cell. I want to first validate that the user is in an appropriate column, then I want to input values into certain columns. I think I can accomplish this with the appropriate code to retrieve the row and column.

The VBA equivalent to ActiveCell.Row & Activecell.Column

Thank You
Open Office 4.1 on Windows 7 / Ubuntu 14.04
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Simple retrieve Row and Column from selected cell

Post by RoryOF »

Get the address of current cell
viewtopic.php?t=33400
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
RMichalowski
Posts: 6
Joined: Thu Apr 30, 2015 10:48 pm

Re: Simple retrieve Row and Column from selected cell

Post by RMichalowski »

Hi,

From the post it uses:

cell = ThisComponent.getCurrentSelection
column = cell.CellAddress.Column
row = cell.CellAddress.Row

These values do not output to a mesgbox. How do I know it's working and what do I compare it to for a value.

I want to select a cell, If that cell is in column F I want it to perform a function, else do not perform the function.

If it is in F I want to write values in Columns A and B in the same row.

IF column = "6" Then
msgbox "Correct Column"
Else
Msgbox "Not Correct"
End If

Sorry, there is not much explanation of the functions.

Thank you.
Open Office 4.1 on Windows 7 / Ubuntu 14.04
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Simple retrieve Row and Column from selected cell

Post by UnklDonald418 »

These values do not output to a mesgbox.

Code: Select all

MsgBox "row = " & row + 1 & "  column = " & Chr$(column + 65) 
The spreadsheet display shows rows beginning at 1 and columns at A. The API returns them both as numbers beginning at 0, so some conversion is required for the MsgBox to display values matching the spreadsheet screen.
The column letter will fail to display the correct letter if you select a cell beyond column Z If needed a more complicated conversion could resolve that issue.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Simple retrieve Row and Column from selected cell

Post by UnklDonald418 »

Based on the starting value of A = 0 your test should be

Code: Select all

IF column = 5 Then
  msgbox "Correct Column"
Else
  Msgbox "Not Correct"
End If
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Simple retrieve Row and Column from selected cell

Post by UnklDonald418 »

One more thing, Section 15.2 of Andrew Pitonyak's book “OpenOffice.org Macros Explained” includes a discussion and code examples of how to convert spreadsheet cell addresses.
If you don't have that book it can be downloaded from
http://www.pitonyak.org/oo.php
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply