Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
user input recorded twice on excel sheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
user input recorded twice on excel sheet
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. --- "sam" wrote: On my excel form, I have text boxes, radio buttons, and check boxes. And I have marked some fields as mandatory; 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
user input recorded twice on excel sheet
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. "sam" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
user input recorded twice on excel sheet
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" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
user input recorded twice on excel sheet
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? "Libby" wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
user input recorded twice on excel sheet
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 "Bob Bridges" wrote: 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. --- "sam" wrote: On my excel form, I have text boxes, radio buttons, and check boxes. And I have marked some fields as mandatory; 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
user input recorded twice on excel sheet
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" wrote: 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" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
user input recorded twice on excel sheet
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. "sam" wrote: 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" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving data in one excel column to another sheet based on user input | Excel Discussion (Misc queries) | |||
VBA to SQL DB with user input and results to sheet | Excel Programming | |||
User input to name a sheet? | Excel Programming | |||
User Input - sheet and ranges | Excel Programming | |||
Help with Macro. -- User input for sheet name | Excel Programming |