I have an Excel form my boss(NOT excel savvy) wants to use in a presentation. The form has two main tables and he wants to be able to double click on a line item in one table and have it transfer to the other table and vice-versa. Is there some way to program this type of functionality to a double click? In the visual basic editor, maybe? How else can I accomplish the same thing and make my boss happy?
--
cyberindio

Use the beforedoubleclick event.

right click on the worksheet tab with the tables and select view code.

In the left dropdown select Workbook and in the right dropdown select
BeforeDoubleClick

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel
As Boolean)

End Sub

will be placed in the module.

You can then write code in this routine

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel
As Boolean)
Dim rng1 as Range, rng2 as Range, rng3 as Range, rng4 as Range

If target.count 1 then exit sub
set rng1 = Range("Table1")
set rng2 = Range("Table2")
if not intersect(Target,rng1) is nothing and not intersect(Target,rng2) is
nothing then
if not intersect(Target.rng1) is nothing then
set rng3 = Intersect(rng1,Target.EntireRow)
set rng4 = rng2.Columns(1).SpecialCells(xlBlanks)(1)
rng3.copy Destination:=rng4
else
set rng3 = Intersect(rng2,Target.EntireRow)
set rng4 = rng1.columns(1).specialCells(xlblanks)(1)
rng3.copy Destination:=rng4
end if
Cancel = True
End if
End Sub

The above untested code should get you started. Probably needs a bit more
work to do exactly what you want - i.e. did you want to move one line to the
other table. Then you would need to go through source table and removed the
data, then slide the data up as an example.

--
Regards,
Tom Ogilvy


"Cyberindio" wrote in message
...
I have an Excel form my boss(NOT excel savvy) wants to use in a
presentation. The form has two main tables and he wants to be able to double
click on a line item in one table and have it transfer to the other table
and vice-versa. Is there some way to program this type of functionality to a
double click? In the visual basic editor, maybe? How else can I accomplish
the same thing and make my boss happy?
--
cyberindio

Thanks for the code, Tom, but I'm having some trouble. The editor tells me there is an ambiguous name error in the first line of the Module "Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel
As Boolean)". I've experimented some but can't get the code to run.
What might I be doing wrong?

--
cyberindio


"Tom Ogilvy" wrote:

Use the beforedoubleclick event.

right click on the worksheet tab with the tables and select view code.

In the left dropdown select Workbook and in the right dropdown select
BeforeDoubleClick

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel
As Boolean)

End Sub

will be placed in the module.

You can then write code in this routine

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel
As Boolean)
Dim rng1 as Range, rng2 as Range, rng3 as Range, rng4 as Range

If target.count 1 then exit sub
set rng1 = Range("Table1")
set rng2 = Range("Table2")
if not intersect(Target,rng1) is nothing and not intersect(Target,rng2) is
nothing then
if not intersect(Target.rng1) is nothing then
set rng3 = Intersect(rng1,Target.EntireRow)
set rng4 = rng2.Columns(1).SpecialCells(xlBlanks)(1)
rng3.copy Destination:=rng4
else
set rng3 = Intersect(rng2,Target.EntireRow)
set rng4 = rng1.columns(1).specialCells(xlblanks)(1)
rng3.copy Destination:=rng4
end if
Cancel = True
End if
End Sub

The above untested code should get you started. Probably needs a bit more
work to do exactly what you want - i.e. did you want to move one line to the
other table. Then you would need to go through source table and removed the
data, then slide the data up as an example.

--
Regards,
Tom Ogilvy


"Cyberindio" wrote in message
...
I have an Excel form my boss(NOT excel savvy) wants to use in a
presentation. The form has two main tables and he wants to be able to double
click on a line item in one table and have it transfer to the other table
and vice-versa. Is there some way to program this type of functionality to a
double click? In the visual basic editor, maybe? How else can I accomplish
the same thing and make my boss happy?
--
cyberindio