Cell on Click Event?

[Simon Lloyd]

Hi all, is there such thing as an On Click event for a cell rather than the worksheet change event? i have helped an Op at another forum who wanted a link or way of clicking a cell and copying the entire row to another sheet this is what i posted:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim Rng As Range
Set Rng = Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)
If Not Intersect(Target, Range("D:D")) Is Nothing Then
Target.EntireRow.Copy Destination:=Rng
End If
End Sub
the only thing is it isn't a link as such as a selection event, so moving through cells n column D via cursor up n down triggers the event rather than actually clicking the cell....is there anyway to achieve this?

Regards,
Simon

[mvidas]

Hi Simon,

Unfortunately, no.

What about using _BeforeDoubleClick?Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Rng As Range
Set Rng = Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)
If Not Intersect(Target, Range("D:D")) Is Nothing Then
Target.EntireRow.Copy Destination:=Rng
Cancel = True
End If
End SubMatt

[Simon Lloyd]

Matt, dunno why i didnt think of that, one other little thought i had was...is it possible to make the target cell when copied i hyperlink to the original destination?

Thanks for the insight!

Regards,
SImon

[mvidas]

I don't see why not.. change the innards of the If block to: Target.EntireRow.Copy Destination:=Rng
Rng.Worksheet.Hyperlinks.Add Rng.Offset(0, 3), "", "'" & _
Me.Name & "'!" & Target.Address(0, 0), , Target.Text
Cancel = TrueMatt

[Simon Lloyd]

Matt, here's what i had, it's a WIP but i think the stella is taking its toll!

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim MyRange, Rng As Range
Set Rng = Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)
If Not Intersect(Target, Range("D:D")) Is Nothing Then
Target.EntireRow.Copy Destination:=Rng
With Rng
.Hyperlinks.Add Anchor:=Selection, Address:=Rng, SubAddress:=Target, TextToDisplay:="Original Data"
End With
End If
End Sub
i get an invalid procedure argument with the .hyperlinks

Regards,
Simon

[mvidas]

Maybe I need to give Stella another shot.. in Toronto a couple years back I was in a bar and noticed someone there who was quite intoxicated yelling "STELLA!" with a giant bottle of it in his hand. I ordered one and just tasted rotten to me, but I've noticed 2 or 3 of my friends lately that drink it almost exclusively.

I'm guessing your error is because you have "Selection" in there as the anchor (I'm guessing the selection on your Sheet1) but you're adding the hyperlink to rng (which is on Sheet2). Thats why i did it the way i have it above (rng.worksheet.hyperlinks.add anchor:=rng.offset(0,3) ...). hyperlinks can be added to ranges too so I'm guessing the sheet difference is where your issue is; I'm just used to adding them to a worksheet object.

Matt

[Simon Lloyd]

Cheers Matt!, i will look at that...as for Stella she's a great companion! LOL it is an aquired taste but it is brewed and distributed in quite a few areas now so buy the one that suites your taste but its stronger than american beer so maybe thats why the distate for it!

Regards,
Simon

[mvidas]

I like strong beers, I think its just the way it was brewed that I didnt like, maybe it was sitting around too long too :) just a rotten lager, tasted kinda like a wheat beer though I don't think it is
I'll definitely have to try it again (though I doubt it'll top guinness in my book)

[Simon Lloyd]

well there u go guiness is an aquired taste, stella can be malty depending which continent brewed it (InterBrew, england... not malty), and if it was from a draft pump then it you could be right as it is difficult to keep a good pint..it requires maticulous cleaning and maintainance......i think we have drifted as i am having a great deal of trouble equating this to VBA, Excel or that kind of world!

Lol....give it another go Matt you wll be converted!

Regards,
Simon