OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

doubleclick bringing up a value in a message box

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
genher
General User
General User


Joined: 14 Dec 2011
Posts: 7

PostPosted: Thu Dec 15, 2011 12:36 pm    Post subject: doubleclick bringing up a value in a message box Reply with quote

Hi all,

I would like to be able to doubleclick in a table to bring up the corresponding value in an other table. For instance:

if I double click the first cell in the green table it brings up the corresponding value in the blue table and put it in a message box. I know how to this in excel VBA, but I need to convert this spreadsheet to be used in openoffice calc and i am completely clueless on how to do it in OOo basic.




(in VBA on doubleclick it simply goes down so many cells and get the value).

Any help would be grately apreciated.

Thanks
Back to top
View user's profile Send private message
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

PostPosted: Thu Dec 15, 2011 11:49 pm    Post subject: Reply with quote

Hi,
No need for a macro : set a validity check for each cell.

Suppose the table is A1:F10 and the proposed values are in table A21:F30.
Put the cursor in cell A1. Menu Data > Validity :
- tab Validity :
- - Allow : cell range
- - Source : A21
- tab Error Message : uncheck

Copy cell A1 to the whole table A1:F10.
You can now either put any value or choose the value proposed by the drop-down list which appears when the cell is selected.
The table of proposed values could be in another sheet.
_________________
Bernard

OpenOffice.org 1.1.5 fr / Apache OpenOffice 4.0.1 / LibreOffice 4.1.0
MS-Windows 7 Home SP1
This forum is spammed, use instead Apache OpenOffice forums
Back to top
View user's profile Send private message Visit poster's website
genher
General User
General User


Joined: 14 Dec 2011
Posts: 7

PostPosted: Fri Dec 16, 2011 4:00 am    Post subject: Reply with quote

B Marcelly wrote:
Hi,
No need for a macro : set a validity check for each cell.

Suppose the table is A1:F10 and the proposed values are in table A21:F30.
Put the cursor in cell A1. Menu Data > Validity :
- tab Validity :
- - Allow : cell range
- - Source : A21
- tab Error Message : uncheck

Copy cell A1 to the whole table A1:F10.
You can now either put any value or choose the value proposed by the drop-down list which appears when the cell is selected.
The table of proposed values could be in another sheet.


Thanks for the answer but this is not what I am trying to do. I don't want to change (or choose) the value in the first table, I just want the value in the corresponding cell in the 2nd table to pop up in a message box when I doubleclick the cell in the first one.
Back to top
View user's profile Send private message
MK_
General User
General User


Joined: 11 Oct 2011
Posts: 11

PostPosted: Thu Dec 22, 2011 6:00 pm    Post subject: Reply with quote

Hi Genher,

Try the following:

1. Define names to the first (left/uppermost) cell of each area. Let´s say "GreenCell" and "BlueCell". I mean the data area, don´t consider headers, since they are not present on the blue area.

2. Copy/paste this script:
Code:

sub CellOffSet
   Dim iGreenRow as integer
   Dim iGreenCol as integer
   Dim iBlueRow as integer
   Dim iBlueCol as integer   
   Dim iOffsetRow as integer
   Dim iOffsetCol as integer
   Dim oBlueRange as object
   Dim oGreenRange as object
   Dim iNewRow as integer
   Dim iNewCol as integer
   
   ' Get the reference cells
   oBlueCell = thisComponent.NamedRanges.getByName("BlueCell").getReferredCells
   oGreenCell = thisComponent.NamedRanges.getByName("GreenCell").getReferredCells
   ' Get their coordinates
   iBlueRow = oBlueCell.CellAddress.Row
   iBlueCol = oBlueCell.CellAddress.Column
   iGreenRow = oGreenCell.CellAddress.Row
   iGreenCol = oGreenCell.CellAddress.Column
   ' Calculate the offset of the clicked cell in reference to the (green) base cell
   iOffsetRow = ThisComponent.CurrentSelection.CellAddress.Row - iGreenRow
   iOffsetCol = ThisComponent.CurrentSelection.CellAddress.Column - iGreenCol
   ' Calculate the relative position in the blue area
   iNewRow = iBlueRow + iOffsetRow
   iNewCol = iBlueCol + iOffsetCol
   ' display cell's contents
   msgbox  _
thisComponent.getCurrentController().getActiveSheet().getCellByPosition(iNewCol,iNewRow).getString()
end sub


3. Assign the above routine to the double-click event.
3.a. Right-click the sheet that contains your data.
3.b. Select "Sheet Events"
3.c. Select "Double click" and click on the "Macro" button.
3.d. Select the routine.

There are lots of variables just for clarifying purposes.
Be aware to avoid "blue areas" starting in columns smaller than the first one of the "green area".
If you need something more "elegant" you can validate if the double-clicked cell is contained in your green area, the code I posted is just a simple start.
I hope this helps you.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group