CODEWITHSUNDEEP
excelinputcell
thebrycecarter
thebrycecarter

Reputation: 3

Excel - Change the value of a cell from another cell, but changed cell keeps value when input cell is cleared

I'm basically trying to make an input cell. What I want is when I change the value of C2, it changes the value of C1. But when I erase C2 I want C1 to keep the value.

So if I make C2 = "Adam", C1 = "Adam", but then I change C2 = "" and C1 is still "Adam".

Can anyone help me out with this? I've been looking online for ages and can't find anything to help me find a solution.

Upvotes: 0

Views: 1381

Answers (2)

Andy G
Andy G

Reputation: 19367

You can do this on the Worksheet_Change event,

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 2 And Target.Column = 3 Then    'C2
        If Target.Value <> "" Then
            Range("C1").Value = Target.Value
        End If
    End If
End Sub

A formula =C2, or some variation, won't work because it cannot remember a value that has since been deleted.

I suppose you could kind-of fake a formula like the following, but I'm definitely not recommending it, especially in combination with code. I'm just posting it out of interest.

Private Sub Worksheet_Change(ByVal Target As Range)
    Static old As String

    If Target.Row = 2 And Target.Column = 3 Then    'C2
        If Target.Value <> "" Then
            Range("C1").Formula = "=IF(N(""""),"""","""")&C2"
            old = Target.Value
        Else
            Range("C1").Formula = "=IF(N(""" & old & """),"""","""")& """ & old & """"
        End If

    End If
End Sub

It is pointless because you still need code anyway.

I suppose it is even possible for the code to just write to a named range/value that the formula then refers to:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value <> "" Then
        ActiveWorkbook.Names.Add Name:="stored", RefersToR1C1:="=""" & Target.Value & """"
    End If
End Sub

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96763

We can do this with an Event Macro. Place the following code in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim C1 As Range, C2 As Range
    Set C1 = Range("C1")
    Set C2 = Range("C2")

    If Intersect(Target, C2) Is Nothing Then Exit Sub
    If C2.Value = "" Then Exit Sub

    Application.EnableEvents = False
        C2.Copy C1
    Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

  1. right-click the tab name near the bottom of the Excel window
  2. select View Code - this brings up a VBE window
  3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE windows as above
  2. clear the code out
  3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Upvotes: 0

Related Questions