data populated multiple times through excel form

[sam]

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 issue is: If a user forgets to input data in a mandatory field, A msgbox
is displayed to remind user to populate. Once the user clicks ok on the
msgbox, then inputs the data in the mandatory field and clicks submit the
data in excel sheet is populated twice. One without the mandatory field(first
click on submit by user) and One with the mandatory field in a new row(second
click on submit button after user is reminded of the mandatory field)

I hope I made it clear.

Thanks a LOT in advance.

 

[ker_01]

Immediately after your warning msgbox line of code, add:
Exit Sub

This should be done within the loop that checks to verify that all mandatory
fields have data, e.g.

If mandatoryBoolean = False then
msgbox "you forgot to fill in all mandatory fields",,"Idiot"
Exit sub
endif

HTH
Keith

 

[Per Jessen]

Hi

As you don't post your code, I can only give a general guideline of what to
do:

Check if all mandator fields are filled
If data are missing, display message and Exit Sub,
else paste data to sheet.

Hopes this helps.
....
Per

 

[sam]

Hey Keith, thanks for the reply. I already have the Exit sub in the loop, But
it still populates it twice.

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]

Hey Per, I am still having the issue. 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.

 

[ker_01]

Based on your code, it looks like you do have exit sub in place for two items
(officer name and amount value). If either of those are missing, I'd expect
this to work properly.

However, it looks like you start writing values before you are done with
your checking; 'copy the data to the database' and the subsequent 5 rows
occur before where you check for checkbox ticking (and the corresponding exit
sub).

I would do all the checking before writing anything to the target worksheet;
try moving the 'copy the data to the database' and the subsequent 5 rows down
by where you do the final optionbutton code.

HTH,
Keith