Userform Clear Controls

[Oprichnick]

Hy, I bought a Excel Vba book which has several code samples for userforms. But I think there is some flaw in the code I show you below

Private Sub EnterButton_Click()
    Dim NextRow As Long
        'Make sure Sheet1 is active
        Sheets("Folha1").Activate
        
        'Determine the next empty row
        NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1
        
        'Transfer the name
        Cells(NextRow, 1) = TextName.Text
        
        'Transfer the sex
        If OptionMale Then Cells(NextRow, 2) = "Male"
        If OptionFemale Then Cells(NextRow, 2) = "Female"
        If OptionUnknown Then Cells(NextRow, 2) = "Unknown"
        
        'Clear the controls for the next entry
        TextName.Text = ""
        OptionUnknown = True
        TextName.SetFocus
        
        'Make sure a name is entered
        If TextName.Text = "" Then
        MsgBox "You must enter a name."
        Exit Sub
        
End If
End Sub

When you enter values, the controls are cleared as it should, however the msgbox "You must enter a name" appears instantly as it not should. I understand where is the flaw, but I can't solve it. Kind Regards

 

[apo]

Hi..
Maybe something like..

If TextName.Text = "" Then       
 MsgBox "You must enter a name."
Elseif TextName.Text <> "" then
Cells(NextRow, 1) = TextName.Text
End if

 

[Jtufplata]

Hi,


maybe this:

Private Sub EnterButton_Click()
    Dim NextRow As Long
        'Make sure Sheet1 is active
        Sheets("Folha1").Activate
        
        'Determine the next empty row
        NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1
        
        
        'Transfer the name
        Cells(NextRow, 1) = TextName.Text
        If TextName.Text = "" Then
            GoTo flow_handler
        End If
        'Transfer the sex
        If OptionMale Then Cells(NextRow, 2) = "Male"
        If OptionFemale Then Cells(NextRow, 2) = "Female"
        If OptionUnknown Then Cells(NextRow, 2) = "Unknown"
        
        'Clear the controls for the next entry
        TextName.Text = ""
        OptionUnknown = True
        TextName.SetFocus
        
        'Make sure a name is entered
flow_handler:
        MsgBox "You must enter a name."
        Exit Sub
        
End Sub

 

[Oprichnick]

Hy, Sorry for the late response.... Apo/Jtufplata: I tried your code. Both still show up the msgbox right after I press the Ok button. The controls are cleared but the msgbox shows up immediatly.

 

[Jtufplata]

IN both versions they will always appear if rownum+1 is "", which apparently always is. Just remove the msgbox option, and change your code so no null entries will be created

 

[dmt32]

you need to test for a null string from your textbox before the rest of the code is executed.


see if this approach helps:

Private Sub EnterButton_Click()
    Dim NextRow As Long

    'Make sure a name is entered
    If TextName.Text = "" Then
        MsgBox "You must enter a name."
    Else
        'Make sure Sheet1 is active
        Sheets("Folha1").Activate
        'Determine the next empty row
        NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1
        'Transfer the name
        Cells(NextRow, 1) = TextName.Text
        'Transfer the gender
        If OptionMale Then Cells(NextRow, 2) = "Male"
        If OptionFemale Then Cells(NextRow, 2) = "Female"
        If OptionUnknown Then Cells(NextRow, 2) = "Unknown"
        'Clear the controls for the next entry
        TextName.Text = ""
        OptionUnknown = True
        TextName.SetFocus
    End If
End Sub

Dave

 

[Oprichnick]

Thank You Guys. DMT32: It works just as I wanted. Thankful to you.