Hello all, I am using excel 2007 and has just created a user form
I have a command button to add data which works fine with the code below
What I need help on please is how to search, edit and delete records using the user form, could someone help with some code or point me in the right direction
Private Sub CmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
Sheets(2).Activate
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
With ws
.Cells(iRow, 1).Value = Me.TxtStatus.Value
.Cells(iRow, 2).Value = Me.TxtTagNumber.Value
.Cells(iRow, 3).Value = Me.TxtphotoNumber.Value
.Cells(iRow, 4).Value = Me.TxtAsset1.Value
.Cells(iRow, 5).Value = Me.TxtAsset2.Value
.Cells(iRow, 6).Value = Me.TxtFactory.Value
.Cells(iRow, 7).Value = Me.TxtProductionArea.Value
.Cells(iRow, 8).Value = Me.TxtLocationDescription.Value
.Cells(iRow, 9).Value = Me.TxtDescription.Value
.Cells(iRow, 10).Value = Me.TxtManufacture.Value
.Cells(iRow, 11).Value = Me.TxtType.Value
.Cells(iRow, 12).Value = Me.TxtModel.Value
.Cells(iRow, 13).Value = Me.TxtSerialNumber.Value
.Cells(iRow, 14).Value = Me.TxtKWRating.Value
.Cells(iRow, 15).Value = Me.TxtOutputspeed.Value
.Cells(iRow, 16).Value = Me.TxtMotorType.Value
.Cells(iRow, 17).Value = Me.TxtVoltage.Value
.Cells(iRow, 18).Value = Me.TxtMotorSpeed.Value
.Cells(iRow, 19).Value = Me.TxtGearboxRatio.Value
.Cells(iRow, 20).Value = Me.TxtLubricantType.Value
.Cells(iRow, 21).Value = Me.TxtNotes.Value
.Cells(iRow, 22).Value = Me.TxtSolutioninplace.Value
.Cells(iRow, 23).Value = Me.TxtStockitem.Value
.Cells(iRow, 24).Value = Me.TxtStockLocation.Value
.Cells(iRow, 25).Value = Me.TxtBrammerPriority.Value
.Cells(iRow, 26).Value = Me.TxtTPMCategory.Value
.Cells(iRow, 27).Value = Me.TxtImportantNotes.Value
.Cells(iRow, 28).Value = Me.TxtStandardised.Value
.Cells(iRow, 29).Value = Me.TxtShaftSizes.Value
.Cells(iRow, 30).Value = Me.TxtFactoryDesignate.Value
.Cells(iRow, 31).Value = Me.TxtTag2.Value
.Cells(iRow, 32).Value = Me.TxtCheckedBy.Value
.Cells(iRow, 33).Value = Me.TxtDateChecked.Value
End With
Me.TxtStatus.Value = ""
Me.TxtTagNumber.Value = ""
Me.TxtphotoNumber.Value = ""
Me.TxtAsset1.Value = ""
Me.TxtAsset2.Value = ""
Me.TxtFactory.Value = ""
Me.TxtProductionArea.Value = ""
Me.TxtLocationDescription.Value = ""
Me.TxtDescription.Value = ""
Me.TxtManufacture.Value = ""
Me.TxtType.Value = ""
Me.TxtModel.Value = ""
Me.TxtSerialNumber.Value = ""
Me.TxtKWRating.Value = ""
Me.TxtOutputspeed.Value = ""
Me.TxtMotorType.Value = ""
Me.TxtVoltage.Value = ""
Me.TxtMotorSpeed.Value = ""
Me.TxtGearboxRatio.Value = ""
Me.TxtLubricantType.Value = ""
Me.TxtNotes.Value = ""
Me.TxtSolutioninplace.Value = ""
Me.TxtStockitem.Value = ""
Me.TxtStockLocation.Value = ""
Me.TxtBrammerPriority.Value = ""
Me.TxtTPMCategory.Value = ""
Me.TxtImportantNotes.Value = ""
Me.TxtStandardised.Value = ""
Me.TxtShaftSizes.Value = ""
Me.TxtFactoryDesignate.Value = ""
Me.TxtTag2.Value = ""
Me.TxtCheckedBy.Value = ""
Me.TxtDateChecked.Value = ""
End Sub
I have a command button to add data which works fine with the code below
What I need help on please is how to search, edit and delete records using the user form, could someone help with some code or point me in the right direction
Private Sub CmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
Sheets(2).Activate
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
With ws
.Cells(iRow, 1).Value = Me.TxtStatus.Value
.Cells(iRow, 2).Value = Me.TxtTagNumber.Value
.Cells(iRow, 3).Value = Me.TxtphotoNumber.Value
.Cells(iRow, 4).Value = Me.TxtAsset1.Value
.Cells(iRow, 5).Value = Me.TxtAsset2.Value
.Cells(iRow, 6).Value = Me.TxtFactory.Value
.Cells(iRow, 7).Value = Me.TxtProductionArea.Value
.Cells(iRow, 8).Value = Me.TxtLocationDescription.Value
.Cells(iRow, 9).Value = Me.TxtDescription.Value
.Cells(iRow, 10).Value = Me.TxtManufacture.Value
.Cells(iRow, 11).Value = Me.TxtType.Value
.Cells(iRow, 12).Value = Me.TxtModel.Value
.Cells(iRow, 13).Value = Me.TxtSerialNumber.Value
.Cells(iRow, 14).Value = Me.TxtKWRating.Value
.Cells(iRow, 15).Value = Me.TxtOutputspeed.Value
.Cells(iRow, 16).Value = Me.TxtMotorType.Value
.Cells(iRow, 17).Value = Me.TxtVoltage.Value
.Cells(iRow, 18).Value = Me.TxtMotorSpeed.Value
.Cells(iRow, 19).Value = Me.TxtGearboxRatio.Value
.Cells(iRow, 20).Value = Me.TxtLubricantType.Value
.Cells(iRow, 21).Value = Me.TxtNotes.Value
.Cells(iRow, 22).Value = Me.TxtSolutioninplace.Value
.Cells(iRow, 23).Value = Me.TxtStockitem.Value
.Cells(iRow, 24).Value = Me.TxtStockLocation.Value
.Cells(iRow, 25).Value = Me.TxtBrammerPriority.Value
.Cells(iRow, 26).Value = Me.TxtTPMCategory.Value
.Cells(iRow, 27).Value = Me.TxtImportantNotes.Value
.Cells(iRow, 28).Value = Me.TxtStandardised.Value
.Cells(iRow, 29).Value = Me.TxtShaftSizes.Value
.Cells(iRow, 30).Value = Me.TxtFactoryDesignate.Value
.Cells(iRow, 31).Value = Me.TxtTag2.Value
.Cells(iRow, 32).Value = Me.TxtCheckedBy.Value
.Cells(iRow, 33).Value = Me.TxtDateChecked.Value
End With
Me.TxtStatus.Value = ""
Me.TxtTagNumber.Value = ""
Me.TxtphotoNumber.Value = ""
Me.TxtAsset1.Value = ""
Me.TxtAsset2.Value = ""
Me.TxtFactory.Value = ""
Me.TxtProductionArea.Value = ""
Me.TxtLocationDescription.Value = ""
Me.TxtDescription.Value = ""
Me.TxtManufacture.Value = ""
Me.TxtType.Value = ""
Me.TxtModel.Value = ""
Me.TxtSerialNumber.Value = ""
Me.TxtKWRating.Value = ""
Me.TxtOutputspeed.Value = ""
Me.TxtMotorType.Value = ""
Me.TxtVoltage.Value = ""
Me.TxtMotorSpeed.Value = ""
Me.TxtGearboxRatio.Value = ""
Me.TxtLubricantType.Value = ""
Me.TxtNotes.Value = ""
Me.TxtSolutioninplace.Value = ""
Me.TxtStockitem.Value = ""
Me.TxtStockLocation.Value = ""
Me.TxtBrammerPriority.Value = ""
Me.TxtTPMCategory.Value = ""
Me.TxtImportantNotes.Value = ""
Me.TxtStandardised.Value = ""
Me.TxtShaftSizes.Value = ""
Me.TxtFactoryDesignate.Value = ""
Me.TxtTag2.Value = ""
Me.TxtCheckedBy.Value = ""
Me.TxtDateChecked.Value = ""
End Sub