User Form - Issue (VBA)

[Robby19]

Hello!

So in response to a previous question, I went ahead and made a User Form. However, the skills that I am lacking make this one thing quite difficult.

I created the form and used the Date to display an error if the text box associated with the Date was not entered in a specific format. The problem that I am having is that even though I got the error message to pop-up, the User Form still adds the data to the spreadsheet. Thus when then user clicks "Ok" and fixes the date, now there are 2 entries in the spreadsheet vice the one.

Is there a way to not allow the user to "save" the user form and add the data to the spreadsheet if the date is formatted incorrectly?

For example;
[TABLE="width: 500"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Date[/TD]
[TD]Ref #[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]abc[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]01/01/2018[/TD]
[TD]123[/TD]
[/TR]
</tbody>[/TABLE]

Private Sub cmdbtnSave_Click()
Dim emptyRow As Long
'Make Sheet3 active
 Sheet3.Activate
'Determine emptyRow
 emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1
'Transfer information
 Cells(emptyRow, 2).Value = combAccount.Value
 Cells(emptyRow, 3).Value = txtDate.Value
 Cells(emptyRow, 4).Value = txtRef.Value
 Cells(emptyRow, 5).Value = txtPayee.Value
If optbtnYes.Value = True Then
     Cells(emptyRow, 6).Value = "Yes"
Else
     Cells(emptyRow, 6).Value = "No"
End If
 Cells(emptyRow, 7).Value = combCategory.Value
 Cells(emptyRow, 8).Value = txtMemo.Value
If combStatus.Value = "Reconciled" Then
     Cells(emptyRow, 9).Value = "R"
Else
    If combStatus.Value = "Cleared" Then
        Cells(emptyRow, 9).Value = "C"
Else
     Cells(emptyRow, 9).Value = "V"
End If
End If

If btnIncome.Value = False Then
     Cells(emptyRow, 10).Value = txtIncome
Else
     Cells(emptyRow, 11).Value = txtIncome
End If

If Not IsDate(Me.txtDate.Text) Then
    MsgBox "The Data entered is not a Date" & Chr(10) & "Please Try again"
End If
    
End Sub

 

[=ODIN=]

I think moving the check earlier in the code and then adding a command to exit the macro if the msgbox is displayed will fix the issue.

Private Sub cmdbtnSave_Click()
[COLOR=#0000cd]If Not IsDate(Me.txtDate.Text) Then[/COLOR]
[COLOR=#0000cd]    MsgBox "The Data entered is not a Date" & Chr(10) & "Please Try again"[/COLOR]
[COLOR=#0000cd]	exit sub[/COLOR]
[COLOR=#0000cd]
[/COLOR]
[COLOR=#0000cd]End If[/COLOR]


Dim emptyRow As Long
'Make Sheet3 active
 Sheet3.Activate
'Determine emptyRow
 emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1
'Transfer information
 Cells(emptyRow, 2).Value = combAccount.Value
 Cells(emptyRow, 3).Value = txtDate.Value
 Cells(emptyRow, 4).Value = txtRef.Value
 Cells(emptyRow, 5).Value = txtPayee.Value
If optbtnYes.Value = True Then
     Cells(emptyRow, 6).Value = "Yes"
Else
     Cells(emptyRow, 6).Value = "No"
End If
 Cells(emptyRow, 7).Value = combCategory.Value
 Cells(emptyRow, 8).Value = txtMemo.Value
If combStatus.Value = "Reconciled" Then
     Cells(emptyRow, 9).Value = "R"
Else
    If combStatus.Value = "Cleared" Then
        Cells(emptyRow, 9).Value = "C"
Else
     Cells(emptyRow, 9).Value = "V"
End If
End If


If btnIncome.Value = False Then
     Cells(emptyRow, 10).Value = txtIncome
Else
     Cells(emptyRow, 11).Value = txtIncome
End If




    
End Sub

 

[Robby19]

I think moving the check earlier in the code and then adding a command to exit the macro if the msgbox is displayed will fix the issue.

Appreciate the response! How would I handle the command to exit the macro if the msgbox displayed?

 

[=ODIN=]

I already added it. The blue part above is where I moved the check with the msgbox to the top of your code, and then added the line "Exit Sub"

 

[Robby19]

Well would you look at that. You sir are a winner!

 

[Robby19]

One other question. Would this be the most expedient way to convert the text to a Currency? This is of course selecting the whole row and re-converting it. I am not sure if/how there is a way to grab the most recently added data from the user form and just convert that.

[J:K].Select
With Selection
    .NumberFormat = "$#,##0.00_)"
    .Value = .Value
End With

 

[=ODIN=]

If you are only running that every time the user enters data, I would just remove the below code because it does nothing but cause your workbook to recalculate and waste time.

.Value = .Value

 

[Robby19]

With that removed, the new values no longer calculate to the what they need to be. They remain as text.

 

[dmt32]

To convert a string number of the currency data type from your forms Textbox, try using the built-in type conversion function CCur to coerce the String to a number of the Currency data type and see if this does what you want

Example

 Cells(emptyrow, IIf(btnIncome.Value, 11, 10)).Value = CCur(txtIncome.Value)

Dave

 

[Robby19]

 Cells(emptyrow, IIf(btnIncome.Value, 11, 10)).Value = CCur(txtIncome.Value)

Works beautifully!