user input recorded twice on excel sheet

[sam]

Hello People!

One issue I am having with my code is, On my excel form, I have text boxes,
radio buttons, and check boxes. And I have marked some fields as mandatory,
i.e. If users clicks submit button without filling out these mandatory
fields, they will see a popup msg box asking them to input data in these
fields.
The popup msg box works great when user forgets to input a data in some
mandatory field, BUT the data in excel sheet is populated once the user
clicks submit button(without filling up a mandatory field).
Once I click the OK button on the msg box and input the data in the
mandatory field and click submit, the data is again populated in the excel
sheet in a new row.

I hope I made it clear.

Thanks a LOT in advance.

 

[Bob Bridges]

I've never used forms in Excel, aside from the occasional command button.
But surely if clicking the Submit button causes data from the form to be
written to the worksheet even if mandatory fields are not filled in, it's
because you wrote the button_click code to do it that way?

I mean, if you look at the Submit_Click code, it must say to take the data
and copy it to the worksheet?

Maybe you'd better show us the part of the code from the button_click
procedure that displays the message AND EXITS THE SUB rather than going on to
fill in the data.

 

[JLGWhiz]

I suspect that you are running a loop and that when the user triggers the
message box by clicking Submit with empty fields, that there is no code to
reset the loop to the previous iteration once the message box is cleared.
That means that the VBA will look at the next cell if you have your cells
identified with a variable in the loop.

If this is the case, it is an easy fix, but I would need to see the relevant
code to offer a solution.

 

[Libby]

Hi Sam,

It sounds like you need an "Exit Sub" after the msgbox advising that a
mandatory field isn't filled in. This should prevent it going on to
populate.

The other way to do it would be to disable the Submit button unless the
mandatory fields were filled.
In this case you should start with the button enabled property = false. Then
you need a Sub that checks if the fields are filled and changes this property
to true if they are and false if they're not. This Sub would need to be
called at each change event for the all fields.

 

[sam]

Hey Libby, I already have an Exit Sub after each msgbox code, such as.

'check for Loan Amount
If Trim(Me.LoanAmt.Value) = "" Then
Me.LoanAmt.SetFocus
MsgBox "Please enter a Loan Amount"
Exit Sub
End If

This should work rite?

 

[sam]

Hi Bob, Thanks for the reply. I am still having an issue with this.

here is a part of my code.

Pivate Sub Submit_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'check for Officer Name
If Trim(Me.OfficerNm.Value) = "" Then
Me.OfficerNm.SetFocus
MsgBox "Please enter your Name"
Exit Sub
End If

'check for Amount Name
If Trim(Me.Amt.Value) = "" Then
Me.Amt.SetFocus
MsgBox "Please enter a Amount"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.OfficerNm.Value
ws.Cells(iRow, 2).Value = Me.CostCntr.Value
ws.Cells(iRow, 3).Value = Me.AcctNo.Value
ws.Cells(iRow, 4).Value = Me.OffcPhNo.Value
ws.Cells(iRow, 8).Value = Me.Amt.Value

'Checkbox validation
Dim ctl As Control
Dim mytext As String
Dim ctrl As Control
Dim SumValue As Integer

'initialise variables
SumValue = 0
mytext = ""

'check that a box is ticked
For Each ctrl In Frame4.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
SumValue = SumValue + ctrl.Value
End If
Next
If SumValue = 0 Then 'no checkboxes ticked
MsgBox "Please select value(s) for Values Requested"
Exit Sub
End If

'combine captions
For Each ctrl In Frame4.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
If ctrl.Value = True Then
Select Case mytext
Case ""
mytext = ctrl.Caption
Case Else
mytext = mytext & ", " & ctrl.Caption
End Select
End If
End If
Next
'populate whatever cell you want.
ws.Cells(iRow, 20).Value = mytext

‘Fee Reimbursed Option button: Yes
If FeeYes.Value = True Then
ws.Cells(iRow, 17).Value = "Yes"
End If

‘Fee Reimbursed Option button: No
If FeeNo.Value = True Then
ws.Cells(iRow, 17).Value = "No"
End If

Thanks in Advance

 

[sam]

Hi Libby, I am still having issues with populating data multiple times once
the mandatory field msg box is triggered. Here is a part of my code.

Hi Bob, Thanks for the reply. I am still having an issue with this.

here is a part of my code.

Pivate Sub Submit_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'check for Officer Name
If Trim(Me.OfficerNm.Value) = "" Then
Me.OfficerNm.SetFocus
MsgBox "Please enter your Name"
Exit Sub
End If

'check for Amount Name
If Trim(Me.Amt.Value) = "" Then
Me.Amt.SetFocus
MsgBox "Please enter a Amount"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.OfficerNm.Value
ws.Cells(iRow, 2).Value = Me.CostCntr.Value
ws.Cells(iRow, 3).Value = Me.AcctNo.Value
ws.Cells(iRow, 4).Value = Me.OffcPhNo.Value
ws.Cells(iRow, 8).Value = Me.Amt.Value

'Checkbox validation
Dim ctl As Control
Dim mytext As String
Dim ctrl As Control
Dim SumValue As Integer

'initialise variables
SumValue = 0
mytext = ""

'check that a box is ticked
For Each ctrl In Frame4.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
SumValue = SumValue + ctrl.Value
End If
Next
If SumValue = 0 Then 'no checkboxes ticked
MsgBox "Please select value(s) for Values Requested"
Exit Sub
End If

'combine captions
For Each ctrl In Frame4.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
If ctrl.Value = True Then
Select Case mytext
Case ""
mytext = ctrl.Caption
Case Else
mytext = mytext & ", " & ctrl.Caption
End Select
End If
End If
Next
'populate whatever cell you want.
ws.Cells(iRow, 20).Value = mytext

‘Fee Reimbursed Option button: Yes
If FeeYes.Value = True Then
ws.Cells(iRow, 17).Value = "Yes"
End If

‘Fee Reimbursed Option button: No
If FeeNo.Value = True Then
ws.Cells(iRow, 17).Value = "No"
End If

Thanks in Advance

 

[Libby]

Hi Sam,

I think you need to move the 'Copy to Database' code to after you've
confirmed that everything that needs to be inputted has been.
Try the following:

Pivate Sub Submit_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'check for Officer Name
If Trim(Me.OfficerNm.Value) = "" Then
Me.OfficerNm.SetFocus
MsgBox "Please enter your Name"
Exit Sub
End If

'check for Amount Name
If Trim(Me.Amt.Value) = "" Then
Me.Amt.SetFocus
MsgBox "Please enter a Amount"
Exit Sub
End If


'Checkbox validation
Dim ctl As Control
Dim mytext As String
Dim ctrl As Control
Dim SumValue As Integer

'initialise variables
SumValue = 0
mytext = ""

'check that a box is ticked
For Each ctrl In Frame4.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
SumValue = SumValue + ctrl.Value
End If
Next
If SumValue = 0 Then 'no checkboxes ticked
MsgBox "Please select value(s) for Values Requested"
Exit Sub
End If

'combine captions
For Each ctrl In Frame4.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
If ctrl.Value = True Then
Select Case mytext
Case ""
mytext = ctrl.Caption
Case Else
mytext = mytext & ", " & ctrl.Caption
End Select
End If
End If
Next


‘Fee Reimbursed Option button: Yes
If FeeYes.Value = True Then
ws.Cells(iRow, 17).Value = "Yes"
End If

‘Fee Reimbursed Option button: No
If FeeNo.Value = True Then
ws.Cells(iRow, 17).Value = "No"
End If

‘populate cells
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.OfficerNm.Value
ws.Cells(iRow, 2).Value = Me.CostCntr.Value
ws.Cells(iRow, 3).Value = Me.AcctNo.Value
ws.Cells(iRow, 4).Value = Me.OffcPhNo.Value
ws.Cells(iRow, 8).Value = Me.Amt.Value
ws.Cells(iRow, 20).Value = mytext
end sub


PS this doesn't actually check that either of the option button is selected,
but I'm guessing that you've set the value property of one of them to true as
default.