concat and display value within the same cell in 2 rows

[Tech Enthusiast]

Hello there - I am trying to write a VBA code to the attached file to display the concatenated value in the same cell when the user tabs out or hits enter within the cell after entering a value.

To be more clear I have some functions written in H2 and I2 cells that will display the value in the cells H2 and I2 when the user selects any of the cells from H4 to E29.

So now I have a requirement wherein the user can enter any value from A to Z in the cells from H5 to H29 and E5 to E29. The value can be A, B, C... thru Z. So after entering the value and tabbing out or entering I need the display in that cell to display the value entered in 1 row and then the display value from H2 in another row within the same cell.

Ex1: If the user enters A in H11 cell and then hit enter or tab out of the cell the value in that cell should be the entered value in 1 row and then the displayed value from H2 like
A
V-001-H

Ex2:
If the user enters B in I7 cell and then hit enter or tab out of the cell the value in that cell should be the entered value in 1 row and then the displayed value from I2 like
B
E-001-C

Please see the attached file and let me know how to do that.

 

[shrivallabha]

Welcome to Chandoo Org forums.

Maybe like this (I am not 100% sure that I understood your requirements correctly). Insert it in the worksheet where you have selectionchange code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("H5:I29"), Target) Is Nothing Then
    Application.EnableEvents = False
        Target.Value = Target.Value & vbCrLf & Cells(2, Target.Column).Value
    Application.EnableEvents = True
End If
End Sub

 

[Tech Enthusiast]

Welcome to Chandoo Org forums.

Maybe like this (I am not 100% sure that I understood your requirements correctly). Insert it in the worksheet where you have selectionchange code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("H5:I29"), Target) Is Nothing Then
    Application.EnableEvents = False
        Target.Value = Target.Value & vbCrLf & Cells(2, Target.Column).Value
    Application.EnableEvents = True
End If
End Sub

Thank you Shrivallabha that solved my current requirement. Appreciate your quick reply.