Sorry hans,
http://www.filedropper.com/rudi_1" onclick="window.open(this.href);return false;
SEARCH ENGINE AND DELETE COMMAND
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
-
- Administrator
- Posts: 78532
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SEARCH ENGINE AND DELETE COMMAND
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
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
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: SEARCH ENGINE AND DELETE COMMAND
thanks a lot .... its working good
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: SEARCH ENGINE AND DELETE COMMAND
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;
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;
-
- Administrator
- Posts: 78532
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SEARCH ENGINE AND DELETE COMMAND
Change the code for the OK button:
You probably want to change the code for the Clear button too:
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
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
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: SEARCH ENGINE AND DELETE COMMAND
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
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
-
- Administrator
- Posts: 78532
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SEARCH ENGINE AND DELETE COMMAND
You could replace
with
Code: Select all
Cells(r, 7).Value = ""
Cells(r, 6).Value = ""
Cells(r, 9).Value = ""
Cells(r, 10).Value = ""
Code: Select all
Application.EnableEvents = False
Cells(r, 1).EntireRow.Delete
Application.EnableEvents = True
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: SEARCH ENGINE AND DELETE COMMAND
brilliant hans... Thanks a lot