# Newbie seeks help: Coding This Double Click Event

#### OTOTO

##### Board Regular
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.

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### jim may

##### Well-known Member
Post the below into the Sheet module in which you are working... This should get you started...

Code:
``````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)
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)
End If
Application.CutCopyMode = False
End Sub``````

#### jim may

##### Well-known Member
Post the below into the Sheet module in which you are working... This should get you started...

Code:
``````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)
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)
End If
Application.CutCopyMode = False
End Sub``````

#### Snakehips

##### Well-known Member
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

##### Well-known Member
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....

Code:
``````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

##### Board Regular
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

##### Board Regular
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

##### Well-known Member
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)....

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

#### OTOTO

##### Board Regular
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)....

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

#### OTOTO

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

Replies
1
Views
252
Replies
3
Views
690
Replies
4
Views
240
Replies
3
Views
190
Replies
2
Views
828

1,195,659
Messages
6,010,957
Members
441,578
Latest member
brodiej

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back