Welcome
Welcome to <strong>SpreadCE / Spread32</strong>.

You are currently viewing our boards as a guest, which gives you limited access to view most discussions and access our other features. By joining our free community, you will have access to post topics, communicate privately with other members (PM), respond to polls, upload content, and access many other special features. Registration is fast, simple, and absolutely free, so please, <a href="/profile.php?mode=register">join our community today</a>!

on.doubleclick reference output help

Post your comments, questions, bug reports and feature requests here.

on.doubleclick reference output help

Postby vernondillinger » Thu Apr 06, 2017 7:08 pm

hello, I'm trying to write a macro that checks to see if a particular cell
was clicked.

I got on.doubleclick to work, but checking the reference has proved tricky.
the following code works, but is there another way to do the same thing
using ADDRESS or CELL or another statement?

thank you, paul

note; I'd prefer one IF statement, but this code works...
=IF(COLUMN(CALLER())=2)
=IF(ROW(CALLER())=3)
=FORMULA((Button1!C3+1),Button1!C3)
=END.IF()
=END.IF()
=RETURN()
vernondillinger
 
Posts: 5
Joined: Thu Apr 06, 2017 7:01 pm

Re: on.doubleclick reference output help

Postby vernondillinger » Thu Apr 06, 2017 10:08 pm

hate to answer my own question, but after some (more) experimentation,
I found this works:
=IF((CALLER())=Button1!B3)

interestingly enough, if you do this:
=IF((CALLER())=B3)
...any cell EXCEPT B3 and C3 on Button1 page will increment the value.

but if you do this:
=IF(NOT((CALLER())=B3))
...NO cell except B3 and C3 on Button1 will increment the value.

Not sure why C3 is included, but glad that CALLER() can be referenced
anyways. Double-clicking C3 in most top code will NOT increment!

good day!
:?
vernondillinger
 
Posts: 5
Joined: Thu Apr 06, 2017 7:01 pm

Re: on.doubleclick reference output help

Postby stephenbye » Fri Apr 07, 2017 11:03 am

The CALLER function returns the reference of the cell that was double-clicked.
The function
=IF((CALLER())=Button1!B3)
does not compare the references, it compares the contents of that cell with the contents of the cell B3, so other cells would match it as well.

The function
REFTEXT(CALLER())
or
REFTEXT(CALLER(),TRUE)
will return the cell reference like this
"[filename.xls]Sheet1!R3C2"
or this
"[filename.xls]Sheet1!$B$3"

The function
CELL("address",CALLER())
will currently return the cell reference like this
"Sheet1!$B$3"
although it should return the address like this
"[filename.xls]Sheet1!$B$3"
This will be corrected in a future version of the software.

However these text versions of the calling cell address are still not easy to work with, compared to using the ROW() and COLUMN() functions.

To do it more compactly you can use this
=IF(AND(COLUMN(CALLER())=2,ROW(CALLER())=3))
=FORMULA(CALLER()+1)
=END.IF()
or just this
=IF(AND(COLUMN(CALLER())=2,ROW(CALLER())=3),FORMULA(CALLER()+1))

You might find it easier to insert a Spin Control and link it to cell B3, then no macro will be required.
stephenbye
Site Admin
 
Posts: 332
Joined: Sun Aug 12, 2007 11:56 pm

Re: on.doubleclick reference output help

Postby vernondillinger » Fri Apr 07, 2017 6:41 pm

Thanks for the reply!

Great to hear that Spread32 will be updated, and (I hope) remain a portable app.
Maybe pay versions could be more configurable and with more functions?

Thanks for the clarification about CALLER(), after some consideration, I've
decided to keep with the COLUMN/ROW approach. Seems more earthy to
me for some reason.

Gratis,
Paul
Attachments
GEB-2017--double-click08 {screenshot}.png
screenshot
GEB-2017--double-click08 {screenshot}.png (10.98 KiB) Viewed 58 times
vernondillinger
 
Posts: 5
Joined: Thu Apr 06, 2017 7:01 pm

Re: on.doubleclick reference output help

Postby vernondillinger » Mon Apr 24, 2017 4:28 pm

oh yeah, just a suggestion for the new version:

{{... offcell symbol toggle}}
So, if there's data in the cell that is not viewable, the standard "..." appears at the end.
example:
|viewa|ble
|vi...|ble

Pretty sure excel doesn't have that feature, but its something helpful.

thanks again,
paul
vernondillinger
 
Posts: 5
Joined: Thu Apr 06, 2017 7:01 pm

Re: on.doubleclick reference output help

Postby stephenbye » Thu Apr 27, 2017 12:53 pm

Thank you for your interesting suggestion, I will consider it for a future version.
It would need to be a feature that the user could switch on or off, as the program is intended to copy the behaviour of Microsoft Excel as far as possible. It would also have to be able to place the ellipsis at either end, in case the text of a cell is centered or right-justified.
Right now I am busy adding support for XLSX files, and new worksheet functions, to bring the program into line with Excel 2016.
stephenbye
Site Admin
 
Posts: 332
Joined: Sun Aug 12, 2007 11:56 pm


Return to General

Who is online

Users browsing this forum: No registered users and 1 guest

cron
suspicion-preferred