Newbie seeks help: Coding This Double Click Event

[OTOTO]

I would like to create a double click event which will look at Column A's data in the row which was selected, Column A contains a number which matches only one other cell in Column A of the worksheet "RiskRegister". This will be used to find the row in the "RiskRegister" sheet which will be updated. I want the doubleclick event to paste data from the sheet with the double click event into the riskregister Sheet but only some columns in the row will have data which needs to be pasted. If there's data it will need to be pasted, if not, then the data in the row in the "RiskRegister" sheet can remain. This is far too complex for me to attempt to solve.

 

[jim may]

Post the below into the Sheet module in which you are working... This should get you started...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Column <> 1 Then Exit Sub
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Rng1 = Range(Cells(1, "A"), Cells(Target.Row - 1, "A"))
Set Rng2 = Range(Cells(Target.Row + 1, "A"), Cells(LR, "A"))
    Set frow = Rng1.Find(What:=Target.Value)
    If Not frow Is Nothing Then
        Set Rng1 = Nothing
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "D")).Copy  'Change D to suit (Last data column)
        Range(frow.Address).PasteSpecial xlPasteAll
    Else
    Set nrow = Rng2.Find(What:=Target.Value)
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "D")).Copy   'Change D to suit (Last data column)
        Range(nrow.Address).PasteSpecial xlPasteAll
    End If
Application.CutCopyMode = False
End Sub

 

[jim may]

Post the below into the Sheet module in which you are working... This should get you started...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Column <> 1 Then Exit Sub
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Rng1 = Range(Cells(1, "A"), Cells(Target.Row - 1, "A"))
Set Rng2 = Range(Cells(Target.Row + 1, "A"), Cells(LR, "A"))
    Set frow = Rng1.Find(What:=Target.Value)
    If Not frow Is Nothing Then
        Set Rng1 = Nothing
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "D")).Copy  'Change D to suit (Last data column)
        Range(frow.Address).PasteSpecial xlPasteAll
    Else
    Set nrow = Rng2.Find(What:=Target.Value)
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "D")).Copy   'Change D to suit (Last data column)
        Range(nrow.Address).PasteSpecial xlPasteAll
    End If
Application.CutCopyMode = False
End Sub

 

[Snakehips]

OTOTO,

Some questions...

Are you double-clicking in column A or anywhere in the row?
What range of columns ??? : ??? might have data to transfer to RiskRegister?
Will it be like for like columns in both sheets?

You refer to pasting data. Is it just values?

 

[Snakehips]

OTOTO,

In advance of any response to questions, here is my best guess solution.

Unlike Jim, I am thinking that you are double-clicking in a sheet other than the RiskRegister sheet????

So on that basis....

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
'Assumes jujt column A is to be double clicked  Remove below line if not
If Not Target.Column = 1 Then Exit Sub
On Error Resume Next
RRow = WorksheetFunction.Match(Target, Sheets("RiskRegister").Range("A:A"), 0)
If RRow = "" Then
MsgBox "No Match"
Exit Sub
End If
RtCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
For c = 2 To RtCol
If Not Target.Offset(0, c - 1) = "" Then
Sheets("RiskRegister").Range("A" & RRow).Offset(0, c - 1) = Target.Offset(0, c - 1)
End If
Next c
End Sub

Hope that helps.


Jim, If you get to read this.
Will you please stop spooking me!!!

You are the absolute spitting image of a guy who worked for me for many many years, even down to the check shirt!
The likeness is uncanny. Sadly, he is no longer with us. Every time I see your posts and avatar I can't help refering to you as Albert.

 

[OTOTO]

Snakeships: I'm Clicking in column Q only and looking at the data in column B through M in both sheets. the data is numbers or text depending on the column. I'll give the suggestions a try this evening. Thanks so much guys

 

[OTOTO]

Tony, It's working when I double click in Column A, the code finds the matching cell value in the RiskRegister sheet and then pastes just the information in the row in the doubleclick event sheet into the risk register corresponding cells, leaving the information that was previously in the row in the RiskRegister there where there is nothing in the corresponding cell in the doubleclick event sheet, this is excellent. I would like the double click event to be in column Q though and then search that row's column a value with the RiskRegister's column A for a match, then perform the magic which the code already does.

 

[Snakehips]

OTOTO,

Should just need small change at start of code to ensure that the clicked cell is in column Q (17) rather than A (1)....

'Assumes just column Q is to be double clicked
If Not Target.Column = 17 Then Exit Sub

 

[OTOTO]

I have added this coding and the message box indicating that there is no match comes up. I believe that this is because the code is searching for the match in column A of the RiskRegister to the content in the cell which is double clicked. Is this correct?

OTOTO,

Should just need small change at start of code to ensure that the clicked cell is in column Q (17) rather than A (1)....

'Assumes just column Q is to be double clicked
If Not Target.Column = 17 Then Exit Sub

 

[OTOTO]

Thanks for the jelp Jim, Take a look at Snakechips solution and the present problem to see if you can offer a fix. Thanks!!!