VBA code to show userform when a specific value is selected

[sn152]

Hi All,

I have a sheet in which in range ("C2") I have applied data validation list which has got 2 elements in it. If I choose the first element "Exchange" in cell C2, the value in cell C3 will change to Team 1. And if I choose second element "AD" in cell C2, I want to show a userform with 2 options ("Team2", "Team3") to choose from. Once I choose the option, it will be updated in cell C3 and the userform has to be closed. I tried doing this with the below code. But the problem is I am unable to close the userform. Wherever I click on the page the userform is showing again and again.

Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("Sheet1").Range("C2") = "Exchange" Then
    Sheets("Cover").Range("C3") = "Team1"
End If
If Sheets("Sheet1").Range("C2") = "AD" Then
UserForm1.Show
Unload Userform1
End If
End Sub

Code for Userform

Private Sub OptionButton1_Click()
Sheets("Cover").Range("C6").Value = "AZ2"
Unload UserForm1
Exit Sub
End Sub

Private Sub OptionButton2_Click()
Sheets("Cover").Range("C6").Value = "AZ3"
Unload UserForm1
Exit Sub
End Sub

Please help me here. Thanks in advance!

 

[AscotMD]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$C$2" Then Exit Sub
    If Sheets("Sheet1").Range("C2") = "Exchange" Then
        Sheets("Cover").Range("C3") = "Team1"
    End If
    If Sheets("Sheet1").Range("C2") = "AD" Then UserForm1.Show
End Sub

Private Sub OptionButton1_Click()
With Sheets("Cover")
.Range("C6").Value = "AZ2"
.Range("C3").Value = "Team 2"
End With
Unload UserForm1
End Sub

Private Sub OptionButton2_Click()
With Sheets("Cover")
.Range("C6").Value = "AZ3"
.Range("C3").Value = "Team 3"
End With
Unload UserForm1
End Sub