Rookie if/then, case select question

[Ligiea]

Ok, I want one of three macros I wrote named "equationset1", "equationset2", or "equationset3" to be run if the value of cell C7 is changed. The values of C7 could only be changed to "None", "Parallel", or "Perpendicular". Am I on the right track here?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$7" Then
Answer = Worksheets(1).Range("C7").Value
Select Case Answer
Case None
Call equationset1
Case Parallel
Call equationset2
Case Perpendicular
Call equationset3
End Select
End If
End Sub

 

[Norie]

The only problem I can see is that you aren't using quotes.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$C$7" Then Exit Sub

    Answer = Target.Value
    Select Case Answer
        Case "None"
            Call equationset1
        Case "Parallel"
            Call equationset2
        Case "Perpendicular"
            Call equationset3
    End Select

End Sub

 

[just_jon]

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">If</SPAN> Target.Address = "$C$7" <SPAN style="color:#00007F">Then</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Value
    <SPAN style="color:#00007F">Case</SPAN> None
        <SPAN style="color:#007F00">'...</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Unless there's a speciic reason not to do so, I usually disable/enable events/screenupdating for safety/convenience.

EDIT to rep cell ref w/ Target

 

[Ligiea]

Linked cell change

I was writing this Worksheet_Change module in the hopes that I could use the linked cell change from a combo box to trigger those three macros. When the combo box selection is changed, and the linked C7 cell changes, those macros aren't triggered. What did I do wrong, or does the Worksheet_Change command not see that type of change? How else can I get a combo box selection change to trigger macros?