VBA to replace text between brackets

[Giggs1991]

​In sheet1, the value of cell A1 is [ABCD] including the brackets. Whent he code executes, the value inside the bracket should change to the contacts of the selected cell in sheet2. For example, If I select Cell A2 in sheet2, the contents on A1 in sheet1 should change from [ABCD] to [contents of A2 in sheet2]. If I select Cell A5 in seet2, the contents on A1 in sheet1 should change from [ABCD] to [contents of A5 in sheet2].

 

[mikerickson]

Try putting this in the ThisWorkbook code module

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim keyCell As Range
    Set keyCell = Me.Sheets("Sheet1").Range("A1")
    If Target.Cells.Count = 1 Then
        If Target.Address <> keyCell.Address Or Sh.Name <> keyCell.Parent.Name Then
            Application.EnableEvents = False
            keyCell.Value = "[" & Target.Text & "]"
            Application.EnableEvents = True
        End If
    End If
End Sub

 

[Giggs1991]

Thank you for the quick reply. For some reason the code does not execute.

 

[Giggs1991]

Oh. Sorry, it actually worked now . I had pasted the code in the modules section earlier

 

[Phuoc]

Try this code in Sheet2

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim a As Variant
    a = "[" & Target.Value & "]"
    Sheets("Sheet1").Range("A1") = a
End Sub

 

[Giggs1991]

Thank you for the above solution. However, I am also looking for a solution to another similar issue.

For example, I have the following text in range from A1 to A10 in sheet1 : what[abc]isthis.

If I select only the range from A3 to A5, and run the macro, it should replace only the contents between the square brackets for range A3 to A5 with the contents of selected range in sheet2. This should happen as a macro and not as an event. Also, this macro should not replace the contents of cell A1 , A6,A7,A8,A9,A10 since they have not been selected.

 

[Giggs1991]

Thanks Phuoc. I just asked 1 more question below for a similar issue below :

For example, I have the following text in range from A1 to A10 in sheet1 : what[abc]isthis.

If I select only the range from A3 to A5, and run the macro, it should replace only the contents between the square brackets for range A3 to A5 with the contents of selected range in sheet2. This should happen as a macro and not as an event. Also, this macro should not replace the contents of cell A1 , A6,A7,A8,A9,A10 since they have not been selected.

 

[Rick Rothstein]

For example, I have the following text in range from A1 to A10 in sheet1 : what[abc]isthis.

If I select only the range from A3 to A5, and run the macro, it should replace only the contents between the square brackets for range A3 to A5 with the contents of selected range in sheet2. This should happen as a macro and not as an event. Also, this macro should not replace the contents of cell A1 , A6,A7,A8,A9,A10 since they have not been selected.

Give this macro a try...

Sub ReplaceSelectCellsWithSheet2Selection()
  Dim Sht2Content As String
  Application.ScreenUpdating = False
  Sheets("Sheet2").Select
  Sht2Content = ActiveCell.Value
  Sheets("Sheet1").Select
  Selection.Replace "[*]", "[" & Sht2Content & "}", xlPart, , , , False, False
  Application.ScreenUpdating = True
End Sub

 

[Giggs1991]

Thanks Rick. That's exactly what I was looking for!!!