Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
data populated multiple times through excel form
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
data populated multiple times through excel form
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 "sam" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
data populated multiple times through excel form
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" skrev i meddelelsen ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
data populated multiple times through excel form
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. "ker_01" wrote: 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 "sam" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
data populated multiple times through excel form
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. "Per Jessen" wrote: 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" skrev i meddelelsen ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
data populated multiple times through excel form
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 "sam" wrote: 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. "ker_01" wrote: 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 "sam" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to print a form multiple times on one page | Excel Discussion (Misc queries) | |||
Printing Single Worksheet Multiple Times with Different Data | New Users to Excel | |||
Inserting a single row to separate data multiple times | Excel Discussion (Misc queries) | |||
2 column List Box populated with data not from Excel sheet? | Excel Programming | |||
Cost times amount (if populated) | Excel Worksheet Functions |