Add data from a cell when clicking on another cell

[Darren_workforce]

Hello,

I wondered if this could be done without using Form Controls. What I was hoping to do was generate a list of names in C:D. When clicking on one of the names, that name would be added into Column A (starting at A2) and then if A2 is occupied, clicking another name from C:D would then cause that name to populate in A3, and so on and so on. Can something like that be done?

Thank you in advance!!

 

[Jeffrey Mahoney]

Please try this:

Book2
	A	B	C	D
1	Names		Blake	Janice
2			Jeff	Frank
3			Herald	Melissa
4			Jonsey	Sarah
Sheet1

Put this code in the SHEET level area in Microsoft Excel Objects. My sheet name is Sheet1

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim i As Range
  Dim Cel As Range
  
  Set i = Intersect(Target, Range("C:D"))
  If Not i Is Nothing Then
    Cancel = True
    Set Cel = Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
    Cel.Value = i.Value
  End If
  
End Sub

 

[Darren_workforce]

Please try this:

Book2
	A	B	C	D
1	Names		Blake	Janice
2			Jeff	Frank
3			Herald	Melissa
4			Jonsey	Sarah
Sheet1

Put this code in the SHEET level area in Microsoft Excel Objects. My sheet name is Sheet1

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim i As Range
  Dim Cel As Range
 
  Set i = Intersect(Target, Range("C:D"))
  If Not i Is Nothing Then
    Cancel = True
    Set Cel = Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
    Cel.Value = i.Value
  End If
 
End Sub

Added into the Worksheet, saved and exited. However, clicking on the cells with names does not carry the names over into A.

 

[Jeffrey Mahoney]

Did you double click?

 

[Jeffrey Mahoney]

The unfortunate part of VBA events is that just navigating through a cell using your keyboard arrows is the same as clicking a cell with a mouse.

 

[Darren_workforce]

Did you double click?

Sorry I didn't clarify. Yes I tried double clicking.

 

[Jeffrey Mahoney]

It worked for me so I have to ask. Did the code get pasted in the sheet you need this action?

 

[Jeffrey Mahoney]

. . . And you have enabled macros?

 

[Darren_workforce]

. . . And you have enabled macros?

Yes macros are enabled. I have another macro setup already. Sheet 1 (Sheet1) is the worksheet. I threw some of the department names into C and D. Periodically, when clicking names, the active cell does appear to be moving further and further down Column A but no names are populating. It's just activating the cell.

 

[Darren_workforce]

Oh wow. So I had data further down in Column A ending at A43. I just happened to scroll down and all the names I clicked on appeared down there. So it is working...but not starting at the cell I anticipated it would.