SEARCH ENGINE AND DELETE COMMAND

[krishnaa_kumarr88]

Sorry hans,

http://www.filedropper.com/rudi_1" onclick="window.open(this.href);return false;

[HansV]

You can expand the data source range of the pivot table on Sheet2 to column Q to include the Action Taken field. But it's not clear how you would like to display Action Taken in the pivot table: as a Row Labels field, as a Column Labels field, as a Report Filter field, or ...?

I think the action of the OK button should be

Code: Select all

Private Sub CommandButton1_Click()
    Dim wsh As Worksheet
    Dim r As Long
    If Me.Result1 Then
        Set wsh = Worksheets("TPM FORM")
        r = Me.lbxDetails
        wsh.Cells(r, 17).Value = Me.ComboBox4.Value
    ElseIf Me.CheckBox1 Then
        Set wsh = Worksheets("MAINTENANCE")
        r = wsh.Cells(wsh.Rows.Count, 1).End(xlUp).Row + 1
        wsh.Cells(r, 1).Value = Me.ComboBox1.Value
        wsh.Cells(r, 2).Value = Me.lbxDetails.Value
        wsh.Cells(r, 3).Value = Me.ComboBox2.Value
        wsh.Cells(r, 4).Value = Me.ComboBox3.Value
        wsh.Cells(r, 5).Value = Me.ComboBox4.Value
        wsh.Cells(r, 7).Value = Me.TextBox2.Value
    End If
End Sub

[krishnaa_kumarr88]

thanks a lot .... its working good

[krishnaa_kumarr88]

Hi Rudi and Hans,

You helped me to create a search engine. Please see the first post in this article. You helped me to create a search box. Everything is working fine. But i got some issue in that,


If you see the attachment, and once you open the attachment it will automatically direct you to the TPM form. Please close TPM form.
1. In the Sheet 1(I named as TPM FORM) double click on any row .
2.It open EDIT FORM. Using SEARCH BY MACHINE NAME , you can search the different machine. It pull up in list box. If you click any row in the listbox it gives u the corresponding information in the rest of the form .
Issue is, if i edit anything by searching under MACHINE NAME, only the row which i double clicked is edited but not the concerned row in the form is edited.
Do you have any idea?
thanks
http://www.filedropper.com/rudi_2" onclick="window.open(this.href);return false;

[HansV]

Change the code for the OK button:

Code: Select all

Private Sub CommandButton1_Click()
    Dim r As Long
    If Me.lbxDetails.ListIndex > -1 Then
        r = Me.lbxDetails
    Else
        r = ActiveCell.Row
    End If
    Cells(r, 1).Value = ComboBox11.Value
    Cells(r, 2).Value = Week11.Value
    Cells(r, 3).Value = Week22.Value
    Cells(r, 4).Value = PGNumber.Value
    Cells(r, 5).Value = Machine.Value
    Cells(r, 6).Value = TPM.Value
        If Result2.Value = True Then
        Cells(r, 7).Value = "REQUIRES ATTENTION"
        Cells(r, 8).Value = ""
        Cells(r, 9).Value = ""
    End If
    If Result1.Value = True Then
        Cells(r, 8).Value = "OK"
        Cells(r, 7).Value = ""
        Cells(r, 9).Value = ""
        Cells(r, 12) = Now
        Cells(r, 13) = ""
    End If
    If Result3.Value = True Then
    Cells(r, 9).Value = "NOT USED"
      Cells(r, 7).Value = ""
        Cells(r, 8) = ""
    End If
    
    Cells(r, 10).Value = Comments.Value
    Me.Hide
     MsgBox "TPM FORM HAS BEEN UPDATED"
End Sub

You probably want to change the code for the Clear button too:

Code: Select all

Private Sub Clear_Click()
    Dim r As Long
    If Me.lbxDetails.ListIndex > -1 Then
        r = Me.lbxDetails
    Else
        r = ActiveCell.Row
    End If
    Me.ComboBox11.Value = ""
    Me.Week11.Value = ""
    Me.Week22.Value = ""
    Me.PGNumber.Value = ""
    Me.Machine.Value = ""
    Me.TPM.Value = ""
    Me.Result2.Value = ""
    Me.Result1.Value = ""
    Result3.Value = ""
    Me.Comments.Value = ""
    Cells(r, 7).Value = ""
    Cells(r, 6).Value = ""
    Cells(r, 9).Value = ""
    Cells(r, 10).Value = ""
End Sub

[krishnaa_kumarr88]

Thanks a lot. Code is working great. Is there is any option available for clear and bring the row up.

Say example, once the clear button is clicked it delete the complete row in the excel and leaving it blank.
Say example if i click on row 22 and click clear, it clear the row 22 but row 22 is empty when compare to filled row in row 21 and row 23. is it possible to bring the next filled row up to the empty row automatically?


Private Sub Clear_Click()
Dim r As Long
If Me.lbxDetails.ListIndex > -1 Then
r = Me.lbxDetails
Else
r = ActiveCell.Row
End If
Me.ComboBox11.Value = ""
Me.Week11.Value = ""
Me.Week22.Value = ""
Me.PGNumber.Value = ""
Me.Machine.Value = ""
Me.TPM.Value = ""
Me.Result2.Value = ""
Me.Result1.Value = ""
Result3.Value = ""
Me.Comments.Value = ""
Cells(r, 7).Value = ""
Cells(r, 6).Value = ""
Cells(r, 9).Value = ""
Cells(r, 10).Value = ""
End Sub

[HansV]

You could replace

Code: Select all

    Cells(r, 7).Value = ""
    Cells(r, 6).Value = ""
    Cells(r, 9).Value = ""
    Cells(r, 10).Value = ""

with

Code: Select all

    Application.EnableEvents = False
    Cells(r, 1).EntireRow.Delete
    Application.EnableEvents = True

[krishnaa_kumarr88]

brilliant hans... Thanks a lot