Excel VBA: Stop userform from reopening

Question

I have two userforms in a worksheet that open when yes or no are respectively selected from a dropdown list in cell G5. Everytime a user continues to enter data somwhere else in the worksheet (in cells other than G5), the userform reopens/reappears though. Is there a way to ensure the userform is only opened when the value in G5 changes?

(Application.EnableEvents = True needs to be on as there are more userforms further down the sheet.)

Thanks in advance for any help!

Here's my code:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True
Set Target = Range("G5")
Application.EnableEvents = False

If Target = "No" Then

    Form1.Show

ElseIf Target = "Yes" Then

    From2.Show

End If

Application.EnableEvents = True

End Sub

I don't think Set Target = Range("G5") does what you think it does... you are not testing if the range is G5 but changing the cell referred by Target. — Vincent G

QHarr QHarr · Accepted Answer · 2018-07-30T11:55:48

Something like

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    If Target.Address = "$G$5" Then

        If Target = "No" Then

            Form1.Show

        ElseIf Target = "Yes" Then

            From2.Show

        End If

    End If

    Application.EnableEvents = True

End Sub

Excel VBA: Stop userform from reopening

1 Answers