0

I have what can be seen as a questionnaire with up to 60 questions (all yes / no). If someone answers Yes, for example, in cell B1 I know questions in cells B6, B9, B22, and B50 will be NO. To be clearer, if your answer to "Are you male?" is Yes, then I know all the questions designed for women will be No.

I can't physically enter a formula into a cell as it will be deleted (overwritten) when someone answers the question. So I need a formula in a separate cell which I'll probably hide.

How can I do that?

2 Answers 2

0

You could do it with VBA. Paste the below code into the sheet that has the cells you are wanting to change, and then customize it for your specific need.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
Range("B1").Value = Range("A1").Value
End If

If Target.Address = "$A$2" Then
Range("B2").Value = Range("A2").Value
End If

End Sub

Source

2
  • I don't understand what you're trying to do... What's in $A$1 and $A$2 from my examples? I'm a newbie to VBA so don't know all the variables.
    – Edwin
    Apr 3, 2016 at 1:06
  • The Target.Address is the cell it watches for changes, so $A$1 would be B1. Range("B1").Value is the cell you want to change so B6, B9, B22, B50, etc. You can put the text you want it to change to in where Range("A1").Value is.
    – Fercstar
    Apr 3, 2016 at 2:11
0

This assumes that we start with the worksheet unprotected and all cells un-locked. Place the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim B1 As Range, OtherBees As Range
    Set B1 = Range("B1")
    Set OtherBees = Range("B6,B9,B22,B50")

    If Intersect(Target, B1) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect
    Cells.Locked = False
    Application.EnableEvents = False
        If UCase(B1.Value) = "YES" Then
            OtherBees.Value = "NO"
            OtherBees.Locked = True
            ActiveSheet.Protect
        Else
            ActiveSheet.Unprotect
            OtherBees.Locked = False
            OtherBees.ClearContents
        End If
    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!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .