CODEWITHSUNDEEP
vbaexcel
JR27
JR27

Reputation: 3

Userform is not unloading once command button is pressed

Having a little trouble with my userform, its not unloading once i hit the command button the data is inputted to the sheet but the userform is not refreshed and the data stays in the text boxes. It was working fine until i put the data validation in, but i cant remove the data validation because it is key to have it in there, any suggestions on what i need to be looking for? 

Private Sub CommandButton1_Click()
Dim emptyRow As Long

If Not IsNumeric(TextBox1.Value) Then
MsgBox ("Sorry, you need to provide a valid order number")
TextBox1.SetFocus
Exit Sub
End If

If TextBox3.Value = "" Then
MsgBox ("Sorry, you need to provide a weight")
TextBox3.SetFocus
Exit Sub
End If

If TextBox4.Value = "" Then
MsgBox ("Sorry, you need to provide a country code")
TextBox4.SetFocus
Exit Sub
End If

If TextBox2.Value = "" Then
MsgBox ("Sorry, you need to provide a country")
TextBox2.SetFocus
Exit Sub
End If

If ComboBox1.Value = "" Then
MsgBox ("Sorry, you need to provide a service")
ComboBox1.SetFocus
Exit Sub
End If

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
If ComboBox1 = "EU" Then
Cells(emptyRow, 1).Value = TextBox1.Value
Cells(emptyRow, 4).Value = UCase(TextBox4.Value)
Cells(emptyRow, 5).Value = UCase(TextBox2.Value)
Cells(emptyRow, 2).Value = TextBox3.Value
Cells(emptyRow, 6).Value = ComboBox1.Value
Else
If ComboBox1 = "ROW" Then
Cells(emptyRow, 1).Value = TextBox1.Value
Cells(emptyRow, 4).Value = UCase(TextBox4.Value)
Cells(emptyRow, 5).Value = UCase(TextBox2.Value)
Cells(emptyRow, 3).Value = TextBox3.Value
Cells(emptyRow, 6).Value = ComboBox1.Value
Else

End Sub

Cells(emptyRow, 7).Value = Date

Unload Me
Application.ActiveWorkbook.Save
ParcelDataEntry.Show
End If
End Sub

Upvotes: 0

Views: 178

Answers (1)

ChipsLetten
ChipsLetten

Reputation: 2953

There are a couple problems with the code you posted.

After the If ComboBox1 = "ROW" Then ... Else block of code you've got an End Sub but no End If. You definitely need to add the End If and I suspect you should remove the End Sub.

You've got three lines of code inside the last If ... End If block which I suspect you want to run whenever the user has properly completed the controls on the form. These should be moved outside of the If ... End If blocks.

You are repeating chunks of code which test if the user has entered a value into a control. These should be extracted into a function. You are also repeating lines of code that write to the worksheet. These can be moved outside of the If ... End If block but leaving the different lines inside.

I think this is how you want your code to look:

Private Sub CommandButton1_Click()
Dim emptyRow As Long

    If Not IsNumeric(TextBox1.Value) Then
        MsgBox ("Sorry, you need to provide a valid order number")
        TextBox1.SetFocus
        Exit Sub
    End If

    If Not UserEnteredAValue(TextBox3, "weight") Then
        Exit Sub
    End If

    If Not UserEnteredAValue(TextBox4, "country code") Then
        Exit Sub
    End If

    If Not UserEnteredAValue(TextBox2, "country") Then
        Exit Sub
    End If

    If Not UserEnteredAValue(ComboBox1, "service") Then
        Exit Sub
    End If

    'Determine emptyRow
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

    'Transfer information
    ' These cells always get the same value
    Cells(emptyRow, 1).Value = TextBox1.Value
    Cells(emptyRow, 4).Value = UCase(TextBox4.Value)
    Cells(emptyRow, 5).Value = UCase(TextBox2.Value)
    Cells(emptyRow, 6).Value = ComboBox1.Value
    Cells(emptyRow, 7).Value = Date

    If ComboBox1 = "EU" Then
        Cells(emptyRow, 2).Value = TextBox3.Value
    Else
        If ComboBox1 = "ROW" Then
            Cells(emptyRow, 3).Value = TextBox3.Value
        Else

        End If

    End If

    Unload Me
    Application.ActiveWorkbook.Save
    ParcelDataEntry.Show

End Sub

Private Function UserEnteredAValue(ByRef theControl As Control, ByRef theDescription As String) As Boolean

Dim result As Boolean

    If theControl.Value <> "" Then
        result = True
    Else
        MsgBox ("Sorry, you need to provide a " & theDescription)
        theControl.SetFocus
        result = False
    End If

    UserEnteredAValue = result

End Function

PS when you use the Cellsor the Range method you should always qualify it with the workbook and worksheet reference, e.g. Workbooks("book_name.xlsm").Worksheets("sheet_name").Cells(emptyRow, 1) so that you know exactly workbook & worksheet you are using.

Upvotes: 1

Related Questions