ON.DOUBLECLICK function

[C Brandt]

I have been seeking a way to transfer control to another worksheet, Bring
data along and populate that worksheet with a single click of the mouse. It
appears as though ON.DOUBLECLICK may do the job, albeit with a double click.

Can someone direct me to more information on how to use the function, all my
sources bo not give me sufficient data to use it as I am a beginning user of
macros.

Any help or clarification will be appreciated.

Craig

 

[Gord Dibben]

BeforeDoubleClick event code may do what you want.

Below is example sheet event code which runs a macro depending upon which cell
is doubleclicked.

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
'Substitute your cells/macro names.
Select Case Target.Address(False, False)
Case "A1"
Cancel = True
MyA1Macro
Case "J10"
Cancel = True
MyJ10Macro
Case "AB275"
Cancel = True
MyAB275Macro
End Select
End Sub

Right-click on your sheet tab and "View Code". Copy/paste into that sheet
module.

There is also a Workbook_SheetBeforeDoubleClick event which would run for all
worksheets.


Gord Dibben MS Excel MVP

 

[Jim Rech]

ON.DOUBLECLICK is a macro function from the obsolete Excel 4 macro language
that was superceded by VBA in Excel 5 in late 1993. There are a variety of
good ways to kick off a macro including a tool bar button, an on-sheet
button and a keyboard shortcut. Double-clicking a cell is probably not one
of them since it has other purposes in Excel.

 

[Gord Dibben]

Good points Jim.

Doubleclick event code is probably way down on the list of "how to do it"


Gord

 

[C Brandt]

Hi guys,

Thanks for the input. This is definitely a work in progress, but is
developing into a very useful tool for my associates, which is my goal.

I have a list of about 350 accounts. In this spreadsheet, I am comparing
what the account currently looks like to another source that dictates what
the account should look like. The environment is very complex and many times
what-you-want does not match what-you-have for very good reasons. The first
sheet in the workbook shows a brief comparison, kind of a go/no-go test. If
it looks questionable, I would like the user to click on the account number
that brings up a more detailed comparison, showing the actual data that does
not compare. The user can make the decision on the needed action, and then
continue.
I expect that we will get about 30% of the accounts with anomalies. 95% of
these anomalies will end up being easily explainable once all the data for
that client is shown. Because of the high number of "hits", I would like the
interface to be precise and very quick. Typing the account number for each
client you would like more detailed information on, then depressing a
button, seems far more work then simply double clicking on a cell and have
the macro move the account number to the client presentation sheet, where
all the data is shown on a single screen. Then with a "Return" button on
this sheet they can go back to the initial sheet.

Now you know What-I-Want and why. The question is, can I have it, and if so
how.

Thanks for any further assistance. It is very much appreciated.



I started working with spread sheets like this about a year ago and thanks
to the help that I have received at this location I have a number of tools
that both reduce the efforts of others and minimizes mistakes. Both they
and I thank you.



Craig

 

[Jim Rech]

Typing the account number for each client ...

Who suggested that? You I think. You're fired!<g>

Don't take me wrong, there is nothing terrible about your double-click idea.
If you can train your users and they like it and it works, super. But just
be aware that there is nothing in Application.OnDoubleClick to tell you what
cell the user clicked. Now, with Gord's approach of using the
BeforeDoubleClick event you have that problem licked, so go that way if you
want.

My point was that it is not generally understood that double-clicking a cell
is the way to drill down into the supporting detail. It's not 'standard',
but, as I said, with training users will know what to do.

But what methods are obvious to a guy with no training? I'd say first a
hyperlink. Everyone who has ever used a web brower knows what a hyperlink
does. What else? A little button or check box to the left of the item (in
a See Detail column possibly). Or maybe a button at the top of the screen
(above a freeze pane line so it doesn't scroll off) that says "Select
account number cell and click me for detail". Any of these will probably
do...

 

[C Brandt]

Jim:
Is it safe to assume that when you double click on the cell that that cell
is now selected? If so, the first like of the macro would copy the selected
cell then change sheets and copy into the key cell and you should be done.
But your idea of selecting the appropriate client's account number then
depressing the button does appear to be very inviting concidering that I
know how to do that and I have no clue how to set up the OnDoubleClick to
get it to work.
I tried the hyperlink, but how do I get the appropriate account number to
the new page?
Thank for your help,
Craig

 

[Jim Rech]

Is it safe to assume that when you double click on the cell that that cell
Yeah, I think so. But you have to add code that checks whether a cell
that's double clicked it one you should copy. A user could double click
anywhere. Also, a user could accidentally double click a cell, a possible
downside of this technique.

I lost sight of what you want to happen with the double click. A hyperlink
is not application in your situation.

Here's an example, plus a little treatise on using the
Worksheet_BeforeDoubleClick event, a technology that was introduced in Excel
97.

''Running this sets the name of the sub to run
''when a double click occurs.
''Note:
'' This is 'Excel 5 technology'. The 'modern'
'' approach uses the Worksheet_BeforeDoubleClick event.
'' To use that, right click the sheet tab and click View Code.
'' Then select Worksheet from the left down down, and
'' Worksheet_BeforeDoubleClick from the right, and add
'' your code. No need to 'set' that handler; it fires automatically
Sub SetOnDoubleClick()
Application.OnDoubleClick = "HandleDoubleclick"
End Sub

Sub HandleDoubleclick()
MsgBox ActiveCell.Address
End Sub

 

[Guest]

If I may offer up a suggestion...

You said: "...Typing the account number for each
client you would like more detailed information on, then depressing a
button, seems far more work then simply double clicking on a cell and have
the macro move the account number to the client presentation sheet, where
all the data is shown on a single screen. Then with a "Return" button on
this sheet they can go back to the initial sheet..."

You're right. Programming the functinality of a button can be quite
tedious... however, I'm sure you're familliar with the WorkBook_Open() and
WorkBook_Activate() routines? I would plant a macro in either one of those
two spots that generates a button over the top of the cell (matching
height/width of cell) with the cell contents as the button text... kind of a
CommandButton1.Caption = Sheet1.Range(cell#).Value - Than all you would have
to do is set the Caption of the button as the Value of an assigned cell in
your comparison sheet. And like Jim said, having a button labeled "Return" at
the top of the compairison sheet (above the freeze line) could return them to
the original sheet. Initially, it sounds like a lot of work (and it is), but
once you're done... you're done! And if you have to do something similar in
the future, it's under your belt and a heck of a lot easier the next time
around! Even if you don't use my idea, I hope I at least sparked some kind of
idea for you that works.

Regards