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]

Click inside the code window, and then begin pressing the F8 key. Each press of the F8 key runs one line of code after the other. This is a great way of watching the results of your code.
Write back with any error messages or questions. Jim

 

[Snakehips]

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.

OTOTO,

I'm a little confused. From the above post I understood that everything was fine other than you wanted the code to fir off a double click in column Q rather than A in the first sheet.

Ie double click in Q sheet 1
Find Match for sheet 1 col A in RiskRegister col A
Then copy row to row in RiskRegister sheet.

My code change in post#5 has only altered the selection column from A to Q.

Please clarify if different.

 

[OTOTO]

OTOTO,

I'm a little confused. From the above post I understood that everything was fine other than you wanted the code to fir off a double click in column Q rather than A in the first sheet.

Ie double click in Q sheet 1
Find Match for sheet 1 col A in RiskRegister col A
Then copy row to row in RiskRegister sheet.

My code change in post#5 has only altered the selection column from A to Q.

Please clarify if different.

You are correct except for the lack of verbiage in the "copy row to row in RiskReister sheet" statement (I only want to copy the cells in the row which has numbers or texts in columns B through M and leave the rest of the risk register cell values/texts in place. I think that you understand this given the way that your suggestion was coded.

For some reason when I leave out the "select column Q cell only" code you suggested the function worked perfectly. Now though, the double click event does not return a match. My initial thought is that this must be because the function is searching for the values or text which match the cell which was double clicked. If that's not the case then perhaps the function is looking for formatting as well as cell content?

Thanks for your help -S

 

[OTOTO]

Additionally, the coding pastes over all values from "Sheet1" into the "RiskRegister" row but I only want the values from columns A or B (doesn't matter) to column M pasted. I'm not sure where to add this limitation to the copying in the code

 

[Snakehips]

OTOTO,

Appologies. I was too quick to think that the change from A to Q required only that one line adjustment.

Because of the impact of the A to Q I have changed the code slightly.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
'Check if clicked column is Q (17)
If Not Target.Column = 17 Then Exit Sub
'Get row
QRow = Target.Row
On Error Resume Next
RRow = WorksheetFunction.Match(Range("A" & QRow), Sheets("RiskRegister").Range("A:A"), 0)
If RRow = "" Then
MsgBox "No Match"
Exit Sub
End If
For c = 1 To 12  'column B = 2  M = 13  so offsets from A are 1 to 12
If Not Range("A" & QRow).Offset(0, c) = "" Then
Sheets("RiskRegister").Range("A" & RRow).Offset(0, c) = Range("A" & QRow).Offset(0, c)
End If
Next c
End Sub

Hope that's more like it.

 

[OTOTO]

You could charm a snake with a chip on his shoulder with all your help. Thanks a million

OTOTO,

Appologies. I was too quick to think that the change from A to Q required only that one line adjustment.

Because of the impact of the A to Q I have changed the code slightly.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
'Check if clicked column is Q (17)
If Not Target.Column = 17 Then Exit Sub
'Get row
QRow = Target.Row
On Error Resume Next
RRow = WorksheetFunction.Match(Range("A" & QRow), Sheets("RiskRegister").Range("A:A"), 0)
If RRow = "" Then
MsgBox "No Match"
Exit Sub
End If
For c = 1 To 12  'column B = 2  M = 13  so offsets from A are 1 to 12
If Not Range("A" & QRow).Offset(0, c) = "" Then
Sheets("RiskRegister").Range("A" & RRow).Offset(0, c) = Range("A" & QRow).Offset(0, c)
End If
Next c
End Sub

Hope that's more like it.

 

[OTOTO]

Coming back to this one after a while.
This solution does not copy blank cells. If there is content in the cell in the sheet("RiskRegister"), but no content in the cell on the sheet where the double click is happening, the sheet("RiskRegister") content stays.
Is there a way to code this differently so that the blankness of a blank cell will copy into the sheet("RiskRegister")?

 

[Snakehips]

What if cells B:M are all blank, do you wish to copy all 12 blanks to the Risk Register?

 

[OTOTO]

What if cells B:M are all blank, do you wish to copy all 12 blanks to the Risk Register?

Snakehips, I would want it to copy all contents, whether blank or populated. SO, your suggested would be fine, although that circumstance will not arise.

 

[Snakehips]

OTOTO,

Not tested bt try...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
'Check if clicked column is Q (17)
If Not Target.Column = 17 Then Exit Sub
'Get row
QRow = Target.Row
On Error Resume Next
RRow = WorksheetFunction.Match(Range("A" & QRow), Sheets("RiskRegister").Range("A:A"), 0)
If RRow = "" Then
MsgBox "No Match"
Exit Sub
End If


Sheets("RiskRegister").Range("B" & RRow & ":M" & RRow).Value = Range("B" & QRow & ":M" & QRow).Value


End Sub