(Solved) – Returning checkbox data to a different excel field than original data was received from?

(solved)-–-returning-checkbox-data-to-a-different-excel-field-than-original-data-was-received-from?

I have checkboxes in my userform which receive their ‘status’ (checked or unchecked) based on fields in my spreadsheet. These checkboxes represent reimbursement items that the employee is eligible for. When the employee completes the userform, it tells them that they are eligible for these items, however, I want the employee to be able to remove the checkmark from a checkbox to signal that they did not have that expense.

Once they submit the userform, these checkbox values should go to my spreadsheet as they were marked/unmarked in the userform. However, when testing the userform and I unmark a checkbox, the values going to my spreadsheet are what were previously calculated rather than what I have noted. I am including the coding that I am using to transfer these checkbox values to my spreadsheet. It is probably important to note that the values coming into these checkboxes are from completely different excel fields than the location that the final ‘status’ of the checkboxes should be going into the spreadsheet. Thus the reason that I am so confused.

I have included the entire code regarding the transfer of my userform to my excel spreadsheet. There is a lot there that I am sure no one cares to see but I wanted to show it all as I feel that more info is likely better than not enough. The lines moving my checkboxes into my spreadsheet are noted in the ‘Begin Data Move Into Database’ section as chkMorning, chkMidday, and chkEvening.

Any help would be greatly appreciated.

Public Sub cmdSubmit_Click()
'When submit button is clicked

Dim TargetRow As Integer
Dim TravelTargetRow As Integer
Dim OtherTargetRow As Integer

TargetRow = Sheets("Codes").Range("D43").value   1
txtTravelDate = format(txtTravelDate, "mm/dd/yyyy")
txtDepartTime = format(txtDepartTime, "hh:mm am/pm")
txtArrivalTime = format(txtArrivalTime, "hh:mm am/pm")
OtherTargetRow = Sheets("Codes").Range("D44").value   1
txtOtherDate = format(txtOtherDate, "mm/dd/yyyy")

'Error messages presented when submit is selected
If txtTravelDate.value = "" Then
    MsgBox "You must enter Date of Travel", vbCritical
    Exit Sub
End If
If txtDepartTime.value = "" Then
    MsgBox "You must enter Actual Departure Time.  If this was overnight travel, and you traveled the previous day to your destination, enter 12:01 AM", vbCritical
    Exit Sub
End If
If txtArrivalTime.value = "" Then
    MsgBox "You must enter Actual Arrival Time.  If this is an overnight trip and you will not return home today, please enter 12:00 PM", vbCritical
    Exit Sub
End If
If cmbOVNRTN.value = "" Then
    MsgBox "You must select if these expense reimbursements are for Overnight travel or same day Return", vbCritical
    Exit Sub
End If
If Sheets("Travel Expense Voucher").Range("D5").value = 1 And txtProjectNumber.value = "" Then
    MsgBox "You must provide a valid Project Number", vbCritical
    Exit Sub
End If
If cmbInOutState.value = "" Then
    MsgBox "You must select if this travel was 'In-State' or 'Out-of-State'", vbCritical
    Exit Sub
End If
If txtMilesTraveled.value = "" Then
    MsgBox "You must enter total miles traveled to be eligible for any reimbursement amounts", vbCritical
    Exit Sub
End If
If cmbMileageRates.value <> "" And cmbTravelMode.value = "" Then
    MsgBox "You must select Mode of Travel to be eligible for mileage reimbursement."
    Exit Sub
End If
If txtTrvlDetails.value = "" Then
    MsgBox "You must provide Travel Details/Description and Business Purpose for this trip", vbCritical
    Exit Sub
End If

'''BEGIN DATA MOVE INTO DATABASE'''
Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 0).value = txtTravelDate 'travel date
Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 3).value = cmbOVNRTN 'overnight or return
Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 10).value = txtProjectNumber 'project number
Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 9).value = cmbInOutState 'in-state or out-of-state
Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 8).value = cmbTravelMode 'travel mode
Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 12).value = txtMilesTraveled 'mileage
Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 11).value = cmbMileageRates 'mileage rate
Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 14).value = CDec(txtLodging) 'lodging
Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 34).value = chkMorning 'morning meal
Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 35).value = chkMidday 'midday meal
Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 36).value = chkEvening 'evening meal
Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 4).value = txtTrvlDetails 'travel details/business purpose
Range("Q14:S14").Select
    Selection.Copy
    Range("Q15:S15").Select
    ActiveSheet.Paste
    ActiveSheet.Paste
Application.CutCopyMode = False
'''END DATA MOVE INTO DATABASE'''

  MsgBox "Entry for " & txtTravelDate & " is complete.  Select the 'Add' button to add entry for another date.", 0, "Complete"

'Error messages presented on other expenses section when submit is selected
If txtOtherDate.value = "" And txtOtherDollarAmt.value <> "0.00" Then
    MsgBox "You must enter date expense was incurred", vbCritical
    Exit Sub
End If
If Sheets("Travel Expense Voucher").Range("D5").value = 1 And txtOtherProjectNum.value = "" And txtOtherDollarAmt.value <> "0.00" Then
    MsgBox "You must provide a valid Project Number", vbCritical
    Exit Sub
End If
If txtOtherExpenseDescription.value = "" And txtOtherDollarAmt.value <> "0.00" Then
    MsgBox "You must provide Expense Description/Business Purpose for this expense", vbCritical
    Exit Sub
End If
If cmbOtherExpenseCode.value = "" And txtOtherDate.value <> "" Then
    MsgBox "You must select an expense code from the drop-down menu", vbCritical
    Exit Sub
End If

'''BEGIN DATA MOVE INTO OTHER EXPENSES DATABASE'''
Sheets("Travel Expense Voucher").Range("OtherExpensesData_Start").Offset(OtherTargetRow, 0).value = txtOtherDate 'other expense date
Sheets("Travel Expense Voucher").Range("OtherExpensesData_Start").Offset(OtherTargetRow, 1).value = txtOtherProjectNum 'Other Project Number
Sheets("Travel Expense Voucher").Range("OtherExpensesData_Start").Offset(OtherTargetRow, 2).value = txtOtherExpenseDescription 'Other Expense Description
Sheets("Travel Expense Voucher").Range("OtherExpensesData_Start").Offset(OtherTargetRow, 8).value = cmbOtherExpenseCode 'Other Expense Code
Sheets("Travel Expense Voucher").Range("OtherExpensesData_Start").Offset(OtherTargetRow, 9).value = CDec(txtOtherDollarAmt) 'Other Expense Dollar Amount
'''END DATA MOVE INTO DATABASE'''

Unload frmUserTravel

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *