Dbl Click and pass data

[DataFanatic]

I have sheet1 with data that shows some detail. I would like to enable the user to double click on a cell and have the data clicked on be passed to another sheet to show additional information. I can use the BeforeDoubleClick action to send me to the next sheet, but cannot fgure out how to carry the caell value. Any help would be greatly appreciated.

 

[SkipVought]

DataFanatic,

There are events in Excel that can be trapped. The Worksheet_SelectionChange event fires when you select a new cell(s). So if you were to SAVE the Cell Reference, then use it to copy on doubleclick

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Sheets("MySaveSheet").[A1] = Target.Address
End Sub
Sub YourDoubleClick
  Sheets("OldSheet").Range(Sheets("MySaveSheet").[A1]).Copy _
    Destination:=Sheets("NextSheet).Range(whatevercell)
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DataFanatic]

Skip, Thanks for the very quick response. I have inserted your code and simply used "Sheet1" and "Sheet2" where you have indicated sheet names. If I interpret your suggestion properly - when I click on cell B2 the value is stored in "Target.Address". Even though I have double clicked the first click stores the value then the second click prompts the next action which is to copy the value to the named cell on the next sheet. As you can tell I am very new to this Excel VBA. Question 1. When you refer to "Oldsheet" do mean "Sheet1" even though "MySaveSheet" is also "Sheet1"?
Q2. What is the "Target.Address" and how is it referred to in the dblclick event?
Thanks in advance for any repsonse.

 

[SkipVought]

MysaveSHeet is a reference sheet where you are storing the value of the last selected cell address

OldSheet is the sheet you are doubleclicking on

NextSheet is the sheet you will copy to

When you Select one or more cells, Worksheet_SelectionChange has as a reference the SelectedCells Range Object as the Target. So Target is a Range Object that represents the selection. Target.Address is the Cell reference of the selection (ie "A1" or "B5:C7&quot

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DataFanatic]

Skip,

I have solved this problem accidentally. Thanks for the brain restart. I used your first formula to place the VALUE in a specific cell and then refer to that cell on the next sheet.

Not elegant at all but it works.
I don't expect an Excel VBA class here but if you could explain what you attempted in your post, I would love it.

Thanks again

 

[SkipVought]

whenever you change the selection, whatever is selected (Target), the address reference of that selection is assigned to the sheet named MySaveSheet in cell reference A1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Sheets("MySaveSheet").[A1] = Target.Address
End Sub

When you doubleclick you are on the ActiveSheet (my change).
Sheets("MySaveSheet&quot.[A1] is where the address reference is stored (ie like "B5&quot
So it says, Copy activesheet cell B5
...and it is pasted to Sheets("NextSheet).Range(whatevercell)

Sub YourDoubleClick()
  ActiveSheet.Range(Sheets("MySaveSheet").[A1]).Copy _
    Destination:=Sheets("NextSheet).Range(whatevercell)
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DataFanatic]

Skip,

Again thanks for the help. By showing this code, it prompted additional learning and I am now working.

 

[SkipVought]

That's how it starts! Glad I could help!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884