End a userform on commandbutton click VBA

[Sweedler]

Hi

So I have a Userform I am using to fill in data on a sheet. I have put in place extra Macros to keep me from leaving any spaces blank (see below):

Private Sub listedbox_Exit(ByVal cancel As MSForms.ReturnBoolean)

If Trim(listedbox.Value) = "" And Me.Visible Then
MsgBox "Listed or Unlisted"
cancel = True
investmentnew.listedbox.SetFocus
listedbox.BackColor = vbYellow
Else
listedbox.BackColor = vbWhite

End If
End Sub

The problem comes when I try to exit/cancel the user form. These safeguards don't let me exit the userform as there are still blanks.

Any suggestions for how I can skip these Macros when clicking cancel or a way to go around somehow. I have tried everything I can think of.

 

[DanteAmor]

Try this.
The UserForm_QueryClose event fires when you close the form, so you can change the value of the comingout variable to true.
If the comingout variable is true then it means that you are going to terminate the form.

Dim comingout As Boolean    'to the top of all the code

Private Sub listedbox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  If comingout Then End
  If Trim(listedbox.Value) = "" And Me.Visible Then
    MsgBox "Listed or Unlisted"
    Cancel = True
    investmentnew.listedbox.SetFocus
    listedbox.BackColor = vbYellow
  Else
    listedbox.BackColor = vbWhite
  End If
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  comingout = True
End Sub

 

[RoryA]

In my opinion you shouldn't use this approach - in my experience it will annoy your users. You should have a Submit/Save type button that performs any necessary checks before actually saving the data to the sheet (you could still use these events to highlight invalid controls if you like).