Re: Check box how to change true / false to other possibilities
I now have a finished (albet non-efficent code). I now need some help utilizing the data I input into excel to create a secondary form. THis form will look down the Leadman column (column 16) for the first blank cell. It will then populate the form with all of the data I just input so the leadman can review, documenet a root cause and fix, etc. in new columns 17 through 21 then sign off in column 16. Thank you in advance for your help
Private Sub cmbsubmit_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DynoRepairData")
'find first empty row in database
Start: iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'check for a part number
If Trim(Me.txtjo.Value) = "" Then
Me.txtjo.SetFocus
MsgBox "Please enter a Job Number"
Exit Sub
End If
'copy the data to the database use protect and unprotect lines,
' with your password if worksheet is protected
With ws
' .Unprotect Password:="password"
.Cells(iRow, 1).Value = Me.txtjo.Value
.Cells(iRow, 2).Value = Me.txtdte.Value
.Cells(iRow, 3).Value = Me.txtmdl.Value
.Cells(iRow, 4).Value = Me.txtsrl.Value
.Cells(iRow, 5).Value = Me.txttchn.Value
.Cells(iRow, 6).Value = Me.txtep.Value
.Cells(iRow, 7).Value = Me.txthtvlt.Value
.Cells(iRow, 8).Value = Me.txtwt.Value
.Cells(iRow, 13).Value = Me.txtRprcmnt.Value
If OptionButtondynoyes = True Then .Cells(iRow, 14).Value = "Yes"
If OptionButtondynono = True Then .Cells(iRow, 14).Value = "No"
.Cells(iRow, 15).Value = Me.txtdynotechinitials.Value
' this next bit will either verify a box isnt checked and move on to the next or add information to database
If Me.Checkfuel.Value = False Then
Else
.Cells(iRow, 9).Value = "Leaks"
.Cells(iRow, 10).Value = "Fuel"
.Cells(iRow, 11).Value = Me.txtfueltime.Value
Me.Checkfuel.Value = False
Me.txtfueltime.Value = ""
GoTo Start
End If
If Me.CheckOil.Value = False Then
Else
.Cells(iRow, 9).Value = "Leaks"
.Cells(iRow, 10).Value = "Oil"
.Cells(iRow, 11).Value = Me.txtoiltime.Value
Me.CheckOil.Value = False
Me.txtoiltime.Value = ""
GoTo Start
End If
If Me.CheckCoolant.Value = False Then
Else
.Cells(iRow, 9).Value = "Leaks"
.Cells(iRow, 10).Value = "Coolant"
.Cells(iRow, 11).Value = Me.txtcooltime.Value
Me.CheckCoolant.Value = False
Me.txtcooltime.Value = ""
GoTo Start
End If
If Me.CheckCoolloopwtr.Value = False Then
Else
.Cells(iRow, 9).Value = "Leaks"
.Cells(iRow, 10).Value = "Cooling Loop Water"
.Cells(iRow, 11).Value = Me.txtcoolloopwattime.Value
Me.CheckCoolloopwtr.Value = False
Me.txtcoolloopwattime.Value = ""
GoTo Start
End If
If Me.CheckPowerview.Value = False Then
Else
.Cells(iRow, 9).Value = "Panel Issue"
.Cells(iRow, 10).Value = "Powerview"
.Cells(iRow, 11).Value = Me.txtpwrvwtime.Value
Me.CheckPowerview.Value = False
Me.txtpwrvwtime.Value = ""
GoTo Start
End If
If Me.CheckPMCI.Value = False Then
Else
.Cells(iRow, 9).Value = "Panel Issue"
.Cells(iRow, 10).Value = "PMCI"
.Cells(iRow, 11).Value = Me.txtPMCItime.Value
Me.CheckPMCI.Value = False
Me.txtPMCItime.Value = ""
GoTo Start
End If
If Me.CheckWireHarness.Value = False Then
Else
.Cells(iRow, 9).Value = "Wiring Harness"
.Cells(iRow, 10).Value = "Wiring Harness"
.Cells(iRow, 11).Value = Me.txtwrhrnstime.Value
Me.CheckWireHarness.Value = False
Me.txtwrhrnstime.Value = ""
GoTo Start
End If
If Me.CheckTach.Value = False Then
Else
.Cells(iRow, 9).Value = "Tachometer"
.Cells(iRow, 10).Value = "Tachometer"
.Cells(iRow, 11).Value = Me.txttachtime.Value
Me.CheckTach.Value = False
Me.txttachtime.Value = ""
GoTo Start
End If
If Me.CheckMECAB.Value = False Then
Else
.Cells(iRow, 9).Value = "MECAB issue"
.Cells(iRow, 10).Value = "MECAB issue"
.Cells(iRow, 11).Value = Me.txtMECABtime.Value
If OptionButtonYes = True Then .Cells(iRow, 12).Value = "Yes"
If OptionButtonNo = True Then .Cells(iRow, 12).Value = "No"
Me.CheckMECAB.Value = False
Me.txtMECABtime.Value = ""
GoTo Start
End If
If Me.CheckHE.Value = False Then
Else
.Cells(iRow, 9).Value = "Heat Exchanger Issue"
.Cells(iRow, 10).Value = "Heat Exchanger Issue"
.Cells(iRow, 11).Value = Me.txtHEtime.Value
Me.CheckHE.Value = False
Me.txtHEtime.Value = ""
GoTo Start
End If
If Me.CheckLeaking.Value = False Then
Else
.Cells(iRow, 9).Value = "Leaking"
.Cells(iRow, 10).Value = "Leaking"
.Cells(iRow, 11).Value = Me.txtlktime.Value
Me.CheckLeaking.Value = False
Me.txtlktime.Value = ""
GoTo Start
End If
If Me.CheckFuelSetGovSet.Value = False Then
Else
.Cells(iRow, 9).Value = "Fuel Setting / Governor Spring"
.Cells(iRow, 10).Value = "Fuel Setting / Governor Spring"
.Cells(iRow, 11).Value = Me.txtfsgstime.Value
Me.CheckFuelSetGovSet.Value = False
Me.txtfsgstime.Value = ""
GoTo Start
End If
If Me.CheckMagPick.Value = False Then
Else
.Cells(iRow, 9).Value = "Mag Pickup"
.Cells(iRow, 10).Value = "Mag Pickup"
.Cells(iRow, 11).Value = Me.txtmagpktime.Value
Me.CheckMagPick.Value = False
Me.txtmagpktime.Value = ""
GoTo Start
End If
If Me.CheckOilGauge.Value = False Then
Else
.Cells(iRow, 9).Value = "Oil Gauge"
.Cells(iRow, 10).Value = "Oil Gauge"
.Cells(iRow, 11).Value = Me.txtoilgagetime.Value
Me.CheckOilGauge.Value = False
Me.txtoilgagetime.Value = ""
GoTo Start
End If
If Me.CheckBattvoltgauge.Value = False Then
Else
.Cells(iRow, 9).Value = "Battery Voltage Gauge"
.Cells(iRow, 10).Value = "Battery Voltage Gauge"
.Cells(iRow, 11).Value = Me.txtbatvoltgagetime.Value
Me.CheckBattvoltgauge.Value = False
Me.txtbatvoltgagetime.Value = ""
GoTo Start
End If
If Me.CheckCAC.Value = False Then
Else
.Cells(iRow, 9).Value = "Charged Air Cooler"
.Cells(iRow, 10).Value = "Charged Air Cooler"
.Cells(iRow, 11).Value = Me.txtCACtime.Value
Me.CheckCAC.Value = False
Me.txtCACtime.Value = ""
GoTo Start
End If
If Me.CheckEngineStart.Value = False Then
Else
.Cells(iRow, 9).Value = "Engine Start Issue"
.Cells(iRow, 10).Value = "Engine Start Issue"
.Cells(iRow, 11).Value = Me.txtenginestarttime.Value
Me.CheckEngineStart.Value = False
Me.txtenginestarttime.Value = ""
GoTo Start
End If
If Me.CheckEngineStop.Value = False Then
Else
.Cells(iRow, 9).Value = "Engine Stop Issue"
.Cells(iRow, 10).Value = "Engine Stop Issue"
.Cells(iRow, 11).Value = Me.txtenginestoptime.Value
Me.CheckEngineStop.Value = False
Me.txtenginestoptime.Value = ""
GoTo Start
End If
If Me.CheckEnginePerf.Value = False Then
Else
.Cells(iRow, 9).Value = "Engine Performance"
.Cells(iRow, 10).Value = Me.txtengineperformdescript.Value
.Cells(iRow, 11).Value = Me.txtengineperformtime.Value
Me.CheckEnginePerf.Value = False
Me.txtengineperformtime.Value = ""
Me.txtengineperformdescript.Value = ""
GoTo Start
End If
If Me.CheckAltnr.Value = False Then
Else
.Cells(iRow, 9).Value = "Alternator"
.Cells(iRow, 10).Value = "Alternator"
.Cells(iRow, 11).Value = Me.txtaltrtime.Value
Me.CheckAltnr.Value = False
Me.txtaltrtime.Value = ""
GoTo Start
End If
If Me.CheckCoolloopsole.Value = False Then
Else
.Cells(iRow, 9).Value = "Cooling Loop Solenoid"
.Cells(iRow, 10).Value = "Cooling Loop Solenoid"
.Cells(iRow, 11).Value = Me.txtcoolloopsoletime.Value
Me.CheckCoolloopsole.Value = False
Me.txtcoolloopsoletime.Value = ""
GoTo Start
End If
If Me.CheckFuelsole.Value = False Then
Else
.Cells(iRow, 9).Value = "Fuel Solenoid"
.Cells(iRow, 10).Value = "Fuel Solenoid"
.Cells(iRow, 11).Value = Me.txtfuelsoletime.Value
Me.CheckFuelsole.Value = False
Me.txtfuelsoletime.Value = ""
GoTo Start
End If
If Me.CheckHeater.Value = False Then
Else
.Cells(iRow, 9).Value = "Heater"
.Cells(iRow, 10).Value = "Heater"
.Cells(iRow, 11).Value = Me.txtheatertime.Value
Me.CheckHeater.Value = False
Me.txtheatertime.Value = ""
GoTo Start
End If
If Me.CheckFlywheel.Value = False Then
Else
.Cells(iRow, 9).Value = "Flywheel"
.Cells(iRow, 10).Value = "Flywheel"
.Cells(iRow, 11).Value = Me.txtflywheeltime.Value
Me.CheckFlywheel.Value = False
Me.txtflywheeltime.Value = ""
GoTo Start
End If
If Me.CheckWrongcomp.Value = False Then
Else
.Cells(iRow, 9).Value = "Wrong Component used in assembly"
.Cells(iRow, 10).Value = "Wrong Component used in assembly"
.Cells(iRow, 11).Value = Me.txtwrongcomptime.Value
Me.CheckWrongcomp.Value = False
Me.txtwrongcomptime.Value = ""
GoTo Start
End If
If Me.CheckOther.Value = False Then
Else
.Cells(iRow, 9).Value = "Tachometer"
.Cells(iRow, 10).Value = Me.txtotherdescpt.Value
.Cells(iRow, 11).Value = Me.txtothertime.Value
Me.CheckOther.Value = False
Me.txtothertime.Value = ""
GoTo Start
End If
.Cells(iRow, 1).Value = ""
.Cells(iRow, 2).Value = ""
.Cells(iRow, 3).Value = ""
.Cells(iRow, 4).Value = ""
.Cells(iRow, 5).Value = ""
.Cells(iRow, 6).Value = ""
.Cells(iRow, 7).Value = ""
.Cells(iRow, 8).Value = ""
.Cells(iRow, 13).Value = ""
.Cells(iRow, 14).Value = ""
.Cells(iRow, 15).Value = ""
.Cells(iRow, 16).Value = ""
' .Protect Password:="password"
End With
Me.txtjo.Value = ""
Me.txtdte.Value = ""
Me.txtmdl.Value = ""
Me.txtsrl.Value = ""
Me.txttchn.Value = ""
Me.txtep.Value = ""
Me.txthtvlt.Value = ""
Me.txtwt.Value = ""
Me.txtRprcmnt.Value = ""
Me.OptionButtondynoyes.Value = ""
Me.txtdynotechinitials.Value = ""
Me.txtleadmaninitials.Value = ""
Me.txtjo.SetFocus
End Sub
Private Sub Form_Load()
ComboBoxMECAByesno.AddItem [,Yes]
ComboBoxMECAByesno.AddItem [,No]
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Close Form button!"
End If
End Sub