locked
exit sub and form display RRS feed

  • Question

  • hello,

    When I click on the OK button of my form, I call a procedure that checks a text box value against a list (here's below the relevant exact). If the name already exists in the list, I would like the code to stop, but I would like the form to stay opened. I am using either Exit sub or GoTo (as displayed below). At the moment, the form closes.

    Any Input welcome.

    Thanks

     

    For Each Cell In Var1

    If LCase(Cell.Value) = LCase(name) Then

    MsgBox "This name already exists, please change the name"

    Exit Sub

    End If

    Next Cell

    End If

     

    For Each Cell In Var1

    If LCase(Cell.Value) = LCase(name) Then GoTo WrongName

    Next Cell

     More code-----

    WrongName:

         MsgBox "This name already exists, please change the name"

    Tuesday, January 31, 2012 3:24 PM

Answers

  • You will have to tell the On Click event procedure of the command button that it shouldn't close the form. One way to do this is to change the procedure that checks the text box to a function that returns a True/False value:

     

    Function CheckTextBox() As Boolean
        ...
        ...
        For Each Cell In Var1
            If LCase(Cell.Value) = LCase(name) Then
                 MsgBox "This name already exists, please change the name"
                Exit Sub
            End If
        Next Cell
        CheckTextBox = True
    End Sub
    

    The default return value will be False, but if the name already exists, it will display a message box and return True. Use like this:

     

    Private Sub cmdOK_Click()
        If CheckTextBox = False Then
            Unload Me
        End If
    End Sub
    


    Regards, Hans Vogelaar
    Tuesday, January 31, 2012 3:42 PM

All replies

  • You will have to tell the On Click event procedure of the command button that it shouldn't close the form. One way to do this is to change the procedure that checks the text box to a function that returns a True/False value:

     

    Function CheckTextBox() As Boolean
        ...
        ...
        For Each Cell In Var1
            If LCase(Cell.Value) = LCase(name) Then
                 MsgBox "This name already exists, please change the name"
                Exit Sub
            End If
        Next Cell
        CheckTextBox = True
    End Sub
    

    The default return value will be False, but if the name already exists, it will display a message box and return True. Use like this:

     

    Private Sub cmdOK_Click()
        If CheckTextBox = False Then
            Unload Me
        End If
    End Sub
    


    Regards, Hans Vogelaar
    Tuesday, January 31, 2012 3:42 PM
  • Thanks Hans, this is very helpful
    Tuesday, January 31, 2012 3:50 PM