Automatic or manual data entry

[Aspharagus]

Hi,

I am looking for a way to create a simple excel file with “automatic” and “manual” as options in a drop down menu on cell A1
When I chose automatic, the cell A2 should pick the value from B2 and with manual option, cell A2 should be blank.
Please help

 

[Peter_SSs]

Try this.

• Set up Data validation in A1 to be able to choose "automatic" or "manual". Post back if you need instructions to do that.
  
  
• Set up the following Worksheet_Change event code as follows

1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A1")) Is Nothing Then
    Application.EnableEvents = False
    If LCase(Range("A1").Value) = "automatic" Then
      Range("A2").Formula = "=B2"
    Else
      Range("A2").ClearContents
    End If
    Application.EnableEvents = True
  End If
End Sub