My user form is not "Unloading" What Do I need to fix in my code?

[davidgeorge212]

I am trying to call up or initialize a user form from a worksheet that has a Yes, No drop down list in cell B7. If the user selects yes in B7, I want it to pull up this user form Wall_Collar_Location. I copied the code from somewhere and it seemingly works the way I want it, but, I can not figure out the unload part. The user form has 2 option buttons and one text box. I have tried using "Unload Me" in both the command button click code and just in the sheet code "Sheet 2" as well but the user form stays open unless you hard close it with the x button. I currently use this code in my worksheet to pull up the form but what do I need to change to unload it after I click the command button?

Dim ltr As String
Dim UForm As Object
Dim i As Integer
i = 0


ltr = Sheet2.Range("B7").Value


For Each UForm In VBA.UserForms
UForm.Hide
Unload VBA.UserForms(i)
i = i + 1
Next


Select Case ltr
Case "Yes"
Wall_Collar_Location.Show

End Select



End Sub

[davidgeorge212]

The code in my user form is as follows and it works as intended, It just does not close when I click the Command Button 1:
I have tried putting unload me on the 3rd line down below Range("8B").Value = TextBox1.Value, but when you click the command button, the user form just blinks and stays open so I took it back out.

Private Sub CommandButton1_Click()
Range("B8").Value = TextBox1.Value

End Sub


Private Sub OptionButton1_Click()
TextBox1.Enabled = True



End Sub

Private Sub OptionButton2_Click()
TextBox1.Enabled = True



End Sub

[ByteMarks]

Is the code in post #1 in a change event? If so, when you populate B8 with the command button it could be triggering that event and re-showing the form.

[davidgeorge212]

Is the code in post #1 in a change event? If so, when you populate B8 with the command button it could be triggering that event and re-showing the form.

This very well could be my case. It is in a change event, is there another type of event that i can use here that might work better? Ideally i would like the user form to open uppon selecting an answer from a drop down list. I guess if need be, i could use a command button and open it the more traditional way

[ByteMarks]

You could try using Application.EnableEvents = False to turn off the events before populating B8. Just be sure to enable it again after.