Page 1 of 1
[Calc] Get ActiveCell (with changing column or row)
Posted: Thu Sep 25, 2008 4:25 pm
by onidarbe
Get the ActiveCell with or whitout changing to an other row or column (offset)
column=columnname
row+1=number
Code: Select all
function fActiveCell(optional vChangeColOrRow) as object
oActiveSheet=ThisComponent.CurrentController.ActiveSheet
vViewData=ThisComponent.CurrentController.viewData
vViewData=join(split(vViewData,";"),"/") 'replace ; with /
vViewData=join(split(vViewData,":"),"/") 'replace : with /
vViewData=join(split(vViewData,"+"),"/") 'replace + with /
vViewData=split(vViewData,"/") 'split the string
iCol=val(vViewData(6))
iRow=val(vViewData(7))
if not isError(vChangeColOrRow) then 'change row or column
if isNumeric(vChangeColOrRow) then 'change row
iRow=int(val(vChangeColOrRow))
else 'change column
for iCol=0 to 999
if oActiveSheet.GetCellByPosition(iCol,0).String=vChangeColOrRow then exit for
if oActiveSheet.GetCellByPosition(iCol,0).String="" then 'Column-label not found
msgbox "Column-name " & chr(34) & vChangeColOrRow & chr(34) & " is not found !!!"
exit function
endif
next
endif
endif
fActiveCell=oActiveSheet.GetCellByPosition(iCol,iRow)
end function
Re: [Calc] Get ActiveCell (with changing column or row)
Posted: Fri Jun 26, 2009 7:30 am
by ooappler
Great great code, I just need funtion for changing column of the ActiveCell, Thanks.
Re: [Calc] Get ActiveCell (with changing column or row)
Posted: Sun Feb 08, 2015 5:58 am
by RichieRH
What is this used for? I cant use it, showing error. Or maybe could you please give an example in calc so I can download it.
Re: [Calc] Get ActiveCell (with changing column or row)
Posted: Sun Feb 08, 2015 6:37 am
by FJCC
If you create a macro module with the code provided and a sub like this
Code: Select all
Sub Main2
ActiveCell = fActiveCell()
print ActiveCell.AbsoluteName
End Sub
the sub will print the name of the active cell. The function fills the strange lack in the api of a way to get the active cell. Instead of just printing the cell name, you would usually want to act on the cell in some way.
I think the code for changing the column of the active cell is not correct. Changing the row seems to work. Call the function with a number as an argument and the cell returned by the function will be the one in the given row of which ever column contains the active cell.
Re: [Calc] Get ActiveCell (with changing column or row)
Posted: Fri Oct 23, 2015 12:56 pm
by RichieRH
Please give me the example , calc file, so I could understand what is the code used for........
Re: [Calc] Get ActiveCell (with changing column or row)
Posted: Sat Oct 24, 2015 3:51 am
by FJCC
I can't give you a useful example of the function but here is an implementation of it. Enter in K2 a number between 1 and 20 or one of the column headers (Alpha, Beta, etc.). Click a cell in the range A2:H21. Click the button and the macro will print either:
1. The cell value that is in the column of the selected cell but has the row index in K2 or
2. The cell value that is in the row of the selected cell but has the column header in K2.
Re: [Calc] Get ActiveCell (with changing column or row)
Posted: Sun Oct 25, 2015 5:02 am
by RichieRH
Hai...FJCC,
I had download your file, and I had it with some errors.Did I make wrong steps?
I figure it out and may suggest you, that the problem is caused by vChangeColOrRow didn't have object variable yet..............but I can't fix it cause I don't really understand about this programming code. I t s because of my lack of skill in English Language..