If I do a search and a record is found "checkbox1 on userform should match the record, checked if a yes, unchecked if a no in column V. Only Known issue at this point is the check box. When I do a " search", By any combination of these three Items ,customer, CSO # or Job # and if it finds a match it fills the form in with the remainder of the matching data, Including part number, description, who Tacked it together, welded and what if any defects were found in the part. If we are going to continue here, this is where I am. So not sure if I should continue with this thread or Start a new one Since it is no longer a Object variable or block variable problem. OK I finally got everything working again except the checkbox, at least to where it was was a couple days ago. Loop Until FoundCell.Address = FirstAddress Set FoundCell = sh.Columns(1).FindNext(FoundCell) MsgBox "Duplicate Entry", 48, "Duplicate" IsDuplicate = CBool(UCase(.Offset(, 1).Value) = UCase(Arr(2)) And _ Set FoundCell = sh.Columns(1).Find(Search, LookIn:=xlValues, lookat:=xlWhole) 'checks values in textboxes for new records are not duplicated "PartDescription", "PartNumber", "Quantity", _įunction IsDuplicate(ByVal Form As Object, ByVal sh As Object, ByVal Arr As Variant) As Booleanĭim Search As String, FirstAddress As String WSData.Cells(RecordRow, i).Value = IIf(.Value, "Yes", "No")įormControls = Array("Customer", "CSONumber", "JobNumber", _ Value = CBool(LCase(WSData.Cells(RecordRow, i).Value) = "yes") If Action = xlAddRecord Then RecordRow = WorksheetFunction.CountA(WSData.Range("A:A")) Sub AddGetRecord(ByVal Action As XLRecordActionType) MsgBox "Record " & msg & " To Worksheet", 64, "Record " & msg Msg = IIf(Action = xlUpdateRecord, "Updated", "Added") If IsDuplicate(Me, WSData, RecordExists) Then Exit SubĪnswer = MsgBox("Are you sure you want to update?", vbYesNo + vbQuestion, "Update Record") MsgBox "Please Enter " & Choose(i, "Customer", "CSO Number", "Job Number"), 48, "Entry Required" Sub AddUpdateRecord(ByVal Action As XLRecordActionType)ĭim ControlsArr As Variant, RecordExists(1 To 3) As Variant 'Me.AddButton.Enabled = Not Me.CMDUpdate.Enabled State = Len(Me.Controls(ControlsArr(i)).Text) > 0 Me.CBNext.Enabled = CBool(RecordCount > 1 And Index 0) Index = IIf(Direction = xlPrevious, Index - 1, IIf(Direction = xlNext, Index + xlNext, xlFirst)) Sub EnableNavigationButtons(ByVal Direction As XlSearchDirection) Me.AddButton.Enabled = Not Me.CMDUpdate.Enabled MsgBox "Search term not found", 48, "Not Found" Set FilterRange = įilterCount = FilterRange.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 Set Fnd = WSData.Range("A2:A" & ).SpecialCells(xlVisible)(1) If Len(.Text) > 0 Then WSData.Range("A1").AutoFilter i.
If WSData.AutoFilterMode Then WSData.AutoFilterMode = False ' ( *************THIS IS WHERE IT HANGS UP**************** ) Set Fnd = WSData.Columns(1).Find(Me.Customer.Text, after:=Fnd, LookIn:=xlValues, lookat:=xlWhole) Set Fnd = WSData.Columns(1).FindPrevious(Fnd)