Permit macro-configurated cell to be overwritten manually

[kanadaaa]

Hi, I'd like help with the following code:

Public TargetCell As Range

Private Sub ComboBox1_LostFocus()
    TargetCell = ComboBox1.Value
    ComboBox1.Visible = False
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Set TargetCell = Target
        With ComboBox1
        .Height = Target.Height
        .Width = Target.Width
        .Top = Target.Top
        .Left = Target.Offset(0, 1).Left
        .Font.Size = 14
        .List = Range("C1:C10").Value
        .ListRows = 10
        .Visible = True
        .Value = ""
        .Activate
        .DropDown
        End With
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Cells(1, 1) = "2" Then
        Cells(1, 5) = Sheets("Sheet2").Cells(2, 2).Value
    End If
End Sub

I have the numbers 1-10 as the values for Sheets(1).Range("C1:C10"), and this code works in the way that when A1 is double-clicked, the combobox shows up, so I can choose a value from those numbers.
Then, I would like E1 to be filled automatically with the value in Sheets(2).Range("B2").Value if "2" is selected, and the code works well.
But it doesn't allow me to replace the inserted value in E1 with a new value by manual input, as long as A1 on Sheet1 is filled with "2". (When E1 is clicked out after edit, it still shows the value in Sheets(2).Range("A1").
How could I make it possible to overwrite this value? Thank you.

 

[juddaaaa]

This is your problem...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Cells(1, 1) = "2" Then
        Cells(1, 5) = Sheets("Sheet2").Cells(2, 2).Value
    End If
End Sub

Everytime you edit any cell on the sheet, this will run and overwrite E1 again as long as A1 = "2"