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

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

On Fri, 16 Mar 2007 15:27:53 -0600, "C Brandt" wrote:


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

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.

--
Jim
"C Brandt" wrote in message
...

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

Good points Jim.

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


Gord

On Fri, 16 Mar 2007 18:28:31 -0400, "Jim Rech" wrote:

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.

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" wrote in message
...
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.

--
Jim
"C Brandt" wrote in message
...

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

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...
--
Jim
"C Brandt" wrote in message
...
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" wrote in message
...
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.

--
Jim
"C Brandt" wrote in message
...

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

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" wrote in message
...
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...
--
Jim
"C Brandt" wrote in message
...
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" wrote in message
...
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.

--
Jim
"C Brandt" wrote in message
...

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

Is it safe to assume that when you double click on the cell that that cell
is now selected?

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 tried the hyperlink, but how do I get the appropriate account number to
the new page?

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

I have no clue how to set up the OnDoubleClick to get it to work

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


--
Jim
"C Brandt" wrote in message
...
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" wrote in message
...
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...
--
Jim
"C Brandt" wrote in message
...
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" wrote in message
...
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.

--
Jim
"C Brandt" wrote in message
...

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