Populate cell values by clicking

[Vikkii]

Hi, I want to populate a cell when I click another cell
Example: I have a list of data in Column A,
if I click any of the cells in Column A the value in that cell should populate to B1 or some cell which we want

 

[Bosquedeguate]

something like this?

Book1
	A	B
1	123	123
2	456
3	789
4	ABC
5	DEF
6	GHI
7
Sheet1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Column = 1 Then
    Range("B1") = Target.Value  'Change "B1" for to the cell you want to receive the value
  End If
End Sub

 

[Jaafar Tribak]

The Worksheet_SelectionChange event fires when clicking a cell but unfortunately, it also fires when selecting cells with the keyboard navigation keys. This is not acceptable. Fortunately, checking the state of the keys beforehand with a little call to the GetAsyncKeyState api function solves this problem.


Code in the worksheet module:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
#Else
    Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
#End If

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim i, nrow As Integer
    Dim vKeysArray As Variant, vKey As Variant
 
    vKeysArray = Array( _
        vbKeyReturn, _
        vbKeyTab, _
        vbKeyDown, _
        vbKeyUp, _
        vbKeyLeft, _
        vbKeyRight, _
        vbKeyHome, _
        vbKeyPageUp, _
        vbKeyPageDown, _
        vbKeyEnd _
    )

    For Each vKey In vKeysArray
        If GetAsyncKeyState(vKey) Then Exit Sub
    Next vKey
    If Target.Column = 1& Then
        Target.Offset(, 1&) = Target
    End If
    
End Sub

 

[Bosquedeguate]

something like this?

Book1
	A	B
1	123	123
2	456
3	789
4	ABC
5	DEF
6	GHI
7
Sheet1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Column = 1 Then
    Range("B1") = Target.Value  'Change "B1" for to the cell you want to receive the value
  End If
End Sub

Hi, I want to populate a cell when I click another cell
Example: I have a list of data in Column A,
if I click any of the cells in Column A the value in that cell should populate to B1 or some cell which we want

Could a Data Validation List in your target cell (the cell that is updated) be a solution?

 

[Vikkii]

something like this?

Book1
	A	B
1	123	123
2	456
3	789
4	ABC
5	DEF
6	GHI
7
Sheet1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Column = 1 Then
    Range("B1") = Target.Value  'Change "B1" for to the cell you want to receive the value
  End If
End Sub

Example:

Here I did click "David" and you can see David in D2. Likewise I want to populate
If we click some cell, it should populate into the target cell (d2)

 

[My Aswer Is This]

You said:
if I click any of the cells in Column A the value in that cell should populate to B1 or some cell which we want

And how do you plan to tell the script the cell which you want?

 

[Vikkii]

You said:
if I click any of the cells in Column A the value in that cell should populate to B1 or some cell which we want

And how do you plan to tell the script the cell which you want?

Sorry for the confusion, please take it as I want to populate to B1

Here I did click "David" and you can see David in D2. Likewise, I want to populate
If we click some cell, it should populate into the target cell (d2)

 

[Peter_SSs]

It still seems confusing to me.

... please take it as I want to populate to B1

Here I did click "David" and you can see David in D2. Likewise, I want to populate
If we click some cell, it should populate into the target cell (d2)

 

[My Aswer Is This]

So if you click on "Alpha" which is in A1
You want Alpha entered into B1
Is that correct?

 

[My Aswer Is This]

I agree with The Worksheet_SelectionChange event fires when clicking a cell but unfortunately, it also fires when selecting cells with the keyboard.

So, I suggest using double click:
Double click any cell in column "A" and the script runs use a script like this:
This way you choose the range to enter the value in:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 1 Then
On Error GoTo M
Dim ans As String
ans = InputBox("Enter range to post value to", "Enter Range", "G5")
Range(ans).Value = Target.Value
End If
Exit Sub
M:
MsgBox "You Entered a Improper range" & vbNewLine & "You entered " & ans
End Sub