VBA Excel 2016 "BeforeDoubleClick"

Question

Hi again folks,

I have read about the VBA event "BeforeDoubleClick" and not sure I appreciate the full scope of its use.

I want to be able to click/double click a cell in worksheet 1 and have its value transferred to a specific cell in worksheet 2.

This is to make it easy for the user; I don't want them to have to use keyboard commands to copy and paste.

Is this possible? Although I am using a Mac, which is up to date as is Excel 2016, I'll try a Windows solution.

Can anybody point me to the best list of VBA functions and statements? Is it on Microsoft's site?

Thanks very much.

Kerry

Answer 1

Try the code below, customize the name of the sheet "Sheet 2" to your needs.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  'Write the value into the other sheet
  Sheets("Sheet 2").Range("A1").Value = Target.Value
  'Cancel the event, otherwise we edit the cell aftwards
  Cancel = True
End Sub

BTW, it is also possible to write the value into the same cell in the other sheet, in this case use this code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  'Write the value into the other sheet
  Sheets("Sheet 2").Range(Target.Address).Value = Target.Value
  'Cancel the event, otherwise we edit the cell aftwards
  Cancel = True
End Sub

If your intention is to make a backup of cells (and restore them later), have a look into this file:

https://www.dropbox.com/s/v618dtuzfv5uik9/Backu...

Andreas.

Answer 2

Thanks very much, Alexander and Andreas. Looks like the doubleclick routine is quite straight-forward. Both suggestions work.

Answer 3

Hello Kerry.Hill,

CAn you try this and tell me if it works for you ? It should work on both Mac and Windows

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
    Dim cell As Object
 
    For Each cell In Range("A1")
 
        Selection.Copy
        
        Sheets("Feuil2").Range("A1").Value = cell.Value
 
    Next cell
    
End Sub

You to place this code in the sheet's project (and not in a module). You can also add a message to notify the user the value has been copied.

The macro works on double click.

Tell me if you need more help,