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: 331
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 25 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


Return to General

Who is online

Users browsing this forum: No registered users and 1 guest

cron
suspicion-preferred