SEARCH ENGINE AND DELETE COMMAND

[krishnaa_kumarr88]

Its not working either .

1. Dates are not displayed correctly on the userform . That is in sheet1, i am getting in dd/mm/yy format and in the EDIT userform i am getting in mm/dd/yy format.
But i would like to get dd/mm/yy format in the EDIT userform aswell.

[Rudi]

The controls in question are DatePicker controls.
Many of these custom controls have their own set of properties.
If you click on the control, check the properties window for additional options and see if it presents you with any date formats that can be adjusted directly for the control.

[krishnaa_kumarr88]

i tried that as well Rudi. Its not working. That is in sheet1, i am getting in dd/mm/yy format and in the EDIT userform i am getting in mm/dd/yy format.

[HansV]

In the callEDITFORM procedure in Module1, try changing the lines

Code: Select all

        EDITFORM.Week11.Value = Cells(Target.Row, 2)
        EDITFORM.Week22.Value = Cells(Target.Row, 3)

to

Code: Select all

        EDITFORM.Week11.Value = Format(Cells(Target.Row, 2), "dd/mm/yy")
        EDITFORM.Week22.Value = Format(Cells(Target.Row, 3), "dd/mm/yy")

[HansV]

Or if you want to preserve the times:

Code: Select all

        EDITFORM.Week11.Value = Format(Cells(Target.Row, 2), "dd/mm/yy hh:nn:ss")
        EDITFORM.Week22.Value = Format(Cells(Target.Row, 3), "dd/mm/yy hh:nn:ss")

[krishnaa_kumarr88]

brilliant Hans . Its working.

Thanks

[krishnaa_kumarr88]

Still i got problem Hans :(
everthing is working fine except the listbox date format.
Ex: In spreadsheet i am getting dd/mm/yy
in EDIT Userform list box mm/dd/yy
In EDIT userform Week Commencing and Week ending dd/mm/yy

[HansV]

Here is modified code for the EDITFORM:

Code: Select all

Option Explicit

Private blnStop As Boolean

Private Sub Clear_Click()
    Me.ComboBox11.Value = ""
    Me.Week11.Value = ""
    Me.Week22.Value = ""
    Me.PGNumber.Value = ""
    Me.Machine.Value = ""
    Me.TPM.Value = ""
    Me.Result2.Value = ""
    Me.Result1.Value = ""
    Me.Comments.Value = ""
    Cells(ActiveCell.Row, 7).Value = ""
    Cells(ActiveCell.Row, 6).Value = ""
    Cells(ActiveCell.Row, 9).Value = ""
    Cells(ActiveCell.Row, 10).Value = ""
End Sub

Private Sub ComboBox1_Change()
    Select Case ComboBox1.Value
        Case "AIRBUS"
            Machine.RowSource = "ADDNEW!D7: D100"
        Case "GCU"
            Machine.RowSource = "ADDNEW!G7:G100"
        Case "FALCON"
            Machine.RowSource = "ADDNEW!I7:I100"
        Case "AC"
            Machine.RowSource = "ADDNEW!K7:K100"
        Case "DC"
            Machine.RowSource = "ADDNEW!M7:M100"
        Case "MRO"
            Machine.RowSource = "ADDNEW!O7:O100"
    End Select
End Sub

Private Sub CommandButton1_Click()
    Cells(ActiveCell.Row, 1).Value = ComboBox11.Value
    Cells(ActiveCell.Row, 2).Value = CDate(Week11.Value)
    Cells(ActiveCell.Row, 3).Value = CDate(Week22.Value)
    Cells(ActiveCell.Row, 4).Value = PGNumber.Value
    Cells(ActiveCell.Row, 5).Value = Machine.Value
    Cells(ActiveCell.Row, 6).Value = TPM.Value
    If Result2.Value = True Then
        Cells(ActiveCell.Row, 7).Value = "REQUIRES ATTENTION"
        Cells(ActiveCell.Row, 8).Value = ""
    End If
    If Result1.Value = True Then
        Cells(ActiveCell.Row, 8).Value = "OK"
        Cells(ActiveCell.Row, 7).Value = ""
        Cells(ActiveCell.Row, 11) = Now
    End If
    Cells(ActiveCell.Row, 9).Value = Comments.Value
    Me.Hide
End Sub

Private Sub CommandButton2_Click()
    Me.Hide
End Sub

Private Sub DELETE_Click()
    If Me.lbxDetails.ListIndex > -1 Then
        Worksheets("TPM FORM").Cells(Me.lbxDetails, 1).EntireRow.DELETE
        Me.lbxDetails.RemoveItem Me.lbxDetails.ListIndex
    End If
End Sub

Private Sub lbxDetails_Click()
    Dim r As Long
    blnStop = True
    r = Me.lbxDetails
    Me.ComboBox11.Value = Cells(r, 1)
    Me.Week11.Value = Format(Cells(r, 2), "dd/mm/yy hh:nn")
    Me.Week22.Value = Format(Cells(r, 3), "dd/mm/yy hh:nn")
    Me.PGNumber.Value = Cells(r, 4)
    Me.Machine.Value = Cells(r, 5)
    Me.TPM.Value = Cells(r, 6)
    If Cells(r, 7) <> "" Then Me.Result2.Value = True
    If Cells(r, 8) <> "" Then Me.Result1.Value = True
    Me.Comments.Value = Cells(r, 9)
    blnStop = False
End Sub

Private Sub Machine_Change()
    Select Case Machine.Value
        Case "SELECT ALL"
            PGNumber.Value = "SELECT ALL"
            TPM.Value = "SELECT ALL"
        Case "CTX500B TURNING MACHINE 1"
            PGNumber.Value = "PG 0034"
            TPM.Value = "TPM 0034"
        Case "HAAS(VF4)"
            PGNumber.Value = "PG 0035"
            TPM.Value = "TPM 0035"
        Case "CTX 500B TURNING MACHINE 2"
            PGNumber.Value = "PG 0038"
            TPM.Value = "TPM 0038"
        Case "HF500(SIGMA)"
            PGNumber.Value = "PG 0039"
            TPM.Value = "TPM 0039"
        Case "HAAS(VF4 BCE)"
            PGNumber.Value = "PG 0040"
            TPM.Value = "TPM 0040"
        Case "JONES & SHIPMEN (1096 CNC)"
            PGNumber.Value = "PG 0238"
            TPM.Value = "TPM 0238"
        Case "EFD INDUCTION LIMITED"
            PGNumber.Value = "PG 0242"
            TPM.Value = "TPM 0242"
        Case "TECHNICA(5100-810)"
            PGNumber.Value = "PG 0243"
            TPM.Value = "TPM 0243"
        Case "JUNG BERLIN D220"
            PGNumber.Value = "PG 0244"
            TPM.Value = "TPM 0244"
        Case "JONES & SHIPMEN (1074)"
            PGNumber.Value = "PG 0247"
            TPM.Value = "TPM 0247"
        Case "BALANCING MACHINE"
            PGNumber.Value = "PG 0248"
            TPM.Value = "TPM 0248"
        Case "ROTARY 1 SCHENCK BALANCER"
            PGNumber.Value = "PG 0249"
            TPM.Value = "TPM 0249"
        Case "ROTOR BALANCE ASSEMBLY(SCHENCK)"
            PGNumber.Value = "PG 0250"
            TPM.Value = "TPM 0250"
        Case "STUDER1 S33"
            PGNumber.Value = "PG 0525"
            TPM.Value = "TPM 0525"
        Case "POLLARD"
            PGNumber.Value = "PG 0584"
            TPM.Value = "TPM 0584"
        Case "STUDER2 S33"
            PGNumber.Value = "PG 0972"
            TPM.Value = "TPM 0972"
        Case "PETROFERM"
            PGNumber.Value = "PG 0995"
            TPM.Value = "TPM 0995"
        Case "PCB DRILLING MACHINE"
            PGNumber.Value = "NOT APPLICABLE"
            TPM.Value = "NOT APPLICABLE"
        Case "POP RIVETER"
            PGNumber.Value = "NOT APPLICABLE"
            TPM.Value = "NOT APPLICABLE"
        Case "SQUEEZE RIVETER"
            PGNumber.Value = "NOT APPLICABLE"
            TPM.Value = "NOT APPLICABLE"
        Case "PILLAR DRILL"
            PGNumber.Value = "NOT APPLICABLE"
            TPM.Value = "NOT APPLICABLE"
        Case "GRINDER"
            PGNumber.Value = "NOT APPLICABLE"
            TPM.Value = "NOT APPLICABLE"
        Case "LINISHER"
            PGNumber.Value = "NOT APPLICABLE"
            TPM.Value = "NOT APPLICABLE"
        Case "CATE TEST INTERFACE(CATE 3)"
            PGNumber.Value = "NOT APPLICABLE"
            TPM.Value = "NOT APPLICABLE"
        Case "CATE- ONLY INTERFACE FOR A380"
            PGNumber.Value = "NOT APPLICABLE"
            TPM.Value = "NOT APPLICABLE"
        Case "VACUUM PUMP (BELL JAR)"
            PGNumber.Value = "NOT APPLICABLE"
            TPM.Value = "NOT APPLICABLE"
        Case "CLOSED LOOP TEST RIG"
            PGNumber.Value = "PG 0081"
            TPM.Value = "NOT APPLICABLE"
        Case "CLEANING TANK"
            PGNumber.Value = "PG 0496"
            TPM.Value = "NOT APPLICABLE"
        Case "CATE RIG (CONFIGURABLE AUTOMATIC TEST EQUIPMENT 1)"
            PGNumber.Value = "PG 0618"
            TPM.Value = "NOT APPLICABLE"
        Case "CATE RIG (CONFIGURABLE AUTOMATIC TEST EQUIPMENT 2)"
            PGNumber.Value = "PG 0619"
            TPM.Value = "NOT APPLICABLE"
        Case "LABORATORY OVEN(CONTROL SYSTEMS GCU TEST)"
            PGNumber.Value = "PG 0620"
            TPM.Value = "NOT APPLICABLE"
        Case "WASHER 1"
            PGNumber.Value = "PG 0621"
            TPM.Value = "NOT APPLICABLE"
        Case "MIDATA TEST RIG"
            PGNumber.Value = "PG 0624"
            TPM.Value = "NOT APPLICABLE"
        Case "ESS CHAMBER 1"
            PGNumber.Value = "PG 0633"
            TPM.Value = "NOT APPLICABLE"
        Case "ESS CHAMBER 2"
            PGNumber.Value = "PG 0635"
            TPM.Value = "NOT APPLICABLE"
        Case "CATE RIG-GCU (CONFIGURABLE AUTOMATIC TEST EQUIPMENT)"
            PGNumber.Value = "PG 0637"
            TPM.Value = "TPM 0637"
        Case "UMATE 1"
            PGNumber.Value = "PG 0642"
            TPM.Value = "NOT APPLICABLE"
        Case "UMATE 2(a)"
            PGNumber.Value = "PG 0643"
            TPM.Value = "NOT APPLICABLE"
        Case "UMATE 2(b)"
            PGNumber.Value = "PG 0644"
            TPM.Value = "NOT APPLICABLE"
        Case "OVEN 1"
            PGNumber.Value = "PG 0645"
            TPM.Value = "NOT APPLICABLE"
        Case "OVEN 2"
            PGNumber.Value = "PG 0646"
            TPM.Value = "NOT APPLICABLE"
        Case "WASHER 2"
            PGNumber.Value = "PG 0647"
            TPM.Value = "NOT APPLICABLE"
        Case "DIP TANK ISOLATOR"
            PGNumber.Value = "PG 0648"
            TPM.Value = "NOT APPLICABLE"
        Case "DIPPING M/C HUMISEAL VARNISH"
            PGNumber.Value = "PG 0649"
            TPM.Value = "NOT APPLICABLE"
        Case "GRAVO GRAPH"
            PGNumber.Value = "PG 0650"
            TPM.Value = "NOT APPLICABLE"
        Case "LABORATORY OVEN"
            PGNumber.Value = "PG 0662"
            TPM.Value = "NOT APPLICABLE"
        Case "CATE RIG 5 - CONFIGURABLE AUTOMATIC TEST EQUIPMENT"
            PGNumber.Value = "PG 0664"
            TPM.Value = "TPM 0672"
        Case "GCU VIBRATION TEST RIG -LDS(AIRBUS AND GLOBAL)"
            PGNumber.Value = "PG 0718"
            TPM.Value = "NOT APPLICABLE"
        Case "POWER AMPLIFIER"
            PGNumber.Value = "PG 0719"
            TPM.Value = "NOT APPLICABLE"
        Case "EXTERNAL POWER FOR A380"
            PGNumber.Value = "PG 0981"
            TPM.Value = "NOT APPLICABLE"
        Case "CATE RIG (CONFIGURABLE AUTOMATIC TEST EQUIPMENT)"
            PGNumber.Value = "PG 0982"
            TPM.Value = "TPM 0083"
        Case "EXTERNAL POWER SUPPLY FOR A380/A400M"
            PGNumber.Value = "PG 0983"
            TPM.Value = "NOT APPLICABLE"
        Case "POWER SUPPLY OUTPUTS"
            PGNumber.Value = "PG 0989"
            TPM.Value = "NOT APPLICABLE"
        Case "BELL BUR-IN RIG"
            PGNumber.Value = "PG 0990"
            TPM.Value = "NOT APPLICABLE"
        Case "VOLTAGE DROP TESTER"
            PGNumber.Value = "PG 0995"
            TPM.Value = "NOT APPLICABLE"
        Case "3 PHASE POWER SUPPLY FOR A380 CATE"
            PGNumber.Value = "PG 0997"
            TPM.Value = "NOT APPLICABLE"
    End Select
End Sub

Private Sub Result1_Click()
    If Result1.Value = True Then
        Result2.Value = False
    End If
End Sub

Private Sub Result2_Click()
    If Result2.Value = True Then
        Result1.Value = False
    End If
End Sub

Private Sub UserForm_Initialize()
    Me.Top = Application.Top + 25
    Me.Left = Application.Left + 25
End Sub

Private Sub Week11_Change()
    Dim r As Range, rAll As Range, rList As Range
    Dim sTerm As String, sTerm2 As String
    Dim irow As Integer, icol As Integer

    If blnStop Then Exit Sub
    sTerm = Me.Week11
    Me.lbxDetails.Clear

    With Sheets("TPM FORM")
        Set rList = .Cells.Find("WEEK COMMENCING")
        If Not rList Is Nothing Then
            irow = rList.Row + 1
            icol = rList.Column
            Set rAll = .Range(.Cells(irow, icol), .Cells(irow, icol).End(xlDown))
            For Each r In rAll
                If InStr(r, sTerm) Then
                    With Me.lbxDetails
                        .AddItem r 'Week Commence Date
                        .List(.ListCount - 1, 1) = Format(r.Offset(0, 1), "dd/mm/yy hh:nn:ss") 'Week End Date
                        .List(.ListCount - 1, 2) = r.Offset(0, 2) 'Machine Name
                        .List(.ListCount - 1, 3) = r.Offset(0, 3) 'PG Number
                        .List(.ListCount - 1, 4) = r.Offset(0, 4) 'TPM Number
                        .List(.ListCount - 1, 5) = r.Offset(0, 5) 'Result
                        .List(.ListCount - 1, 6) = r.Offset(0, 6) 'Comments
                    End With
                End If
            Next r
        End If
    End With
End Sub

Private Sub Week111_Change()
    Dim wsh As Worksheet
    Dim r As Long
    Dim m As Long
    Dim n As Long
    Me.lbxDetails.Clear
    Set wsh = Worksheets("TPM FORM")
    m = wsh.Cells(wsh.Rows.Count, 5).End(xlUp).Row
    For r = 24 To m
        If UCase(wsh.Cells(r, 5).Value) Like UCase(Me.Week111) & "*" Then
            Me.lbxDetails.AddItem r
            Me.lbxDetails.List(n, 1) = Format(Cells(r, 2).Value, "dd/mm/yy hh:nn")
            Me.lbxDetails.List(n, 2) = Format(Cells(r, 3).Value, "dd/mm/yy hh:nn")
            Me.lbxDetails.List(n, 3) = Cells(r, 4).Value
            Me.lbxDetails.List(n, 4) = Cells(r, 5).Value
            Me.lbxDetails.List(n, 5) = Cells(r, 6).Value
            n = n + 1
        End If
    Next r
End Sub

[krishnaa_kumarr88]

brilliant. thanks

[krishnaa_kumarr88]

Hi Rudi and Hans,

You helped me before for the search engine issue. I have following scenario ,
I created a userform with multiple options. I got stuck in few places. Please help me if you know.

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) click on MAINTENANCE command button.
2.It open MAINTENANCE FORM. I have problem with the red highlighed area in the MAINTENANCE FORM in sheet 5. That is , once i select MODULE as either Airbus,Falcon,etc i would like to populate current list of BREAKDOWN Numbers in the list box for the respective module (NOTE: BREAKDOWN Number for the respective module will be in sheet 1 named as TPM FORM and all red rows in sheet 1 will have breakdown number).
3. Once the BREAKDOWN Number have been selected from the MAINTENACNCE Form i would like to pull up the corresponding information like MAchine , Fault code, TPM for the corresponding breakdown number in the MAINTENANCE FORM and i would like to store the information in sheet 5 .

Please see the attachment,

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

thanks,
Does any one have idea of how to do it ?
thanks

[HansV]

To populate the breakdown numbers list box:

Code: Select all

Private Sub ComboBox1_Change()
    Const FirstRow = 24
    Const ModuleCol = 1 ' A
    Const BreakCol = 11 ' K
    Dim wsh As Worksheet
    Dim r As Long
    Dim LastRow As Long
    Dim n As Long
    Dim c As Long
    Me.lbxDetails.Clear
    If Me.ComboBox1.ListIndex >= 0 Then
        Set wsh = Worksheets("TPM FORM")
        LastRow = wsh.Cells(wsh.Rows.Count, BreakCol).End(xlUp).Row
        For r = FirstRow To LastRow
            If wsh.Cells(r, ModuleCol).Value = Me.ComboBox1 Then
                Me.lbxDetails.AddItem r
                n = n + 1
                Me.lbxDetails.Column(1, n) = wsh.Cells(r, BreakCol).Value
            End If
        Next r
    End If
End Sub

(I don't understand why the breakdown numbers list box has 10 columns)

[krishnaa_kumarr88]

Thanks Hans,

I am getting error as
"COULD NOT SET THE COLUMN PROPERTY. INVALID PROPERTY ARRANY INDERX " . when i click Debug its showing error in
Me.lbxDetails.Column(1, n) = wsh.Cells(r, BreakCol).Value

I Think there is some problem with my list box program. I deleted my list box program. Can you please look into that,
Please see the attachment . Thanks a lot

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

ANSWER FOR (I don't understand why the breakdown numbers list box has 10 columns) IS ,
in my attachment , under the MAINTENANCE form ,the highlighted red labels should automatically generate information depending upon the breakdown number. and rest of the labels (without color) will be used by the maintenane as the input form for Maintenacne department.

thanks a lot
http://www.filedropper.com/finaltpm1_1" onclick="window.open(this.href);return false;

[HansV]

To pull up data when lbxDetails is clicked:

Code: Select all

Private Sub lbxDetails_Click()
    Dim wsh As Worksheet
    Dim r As Long
    Set wsh = Worksheets("TPM FORM")
    r = Me.lbxDetails
    Me.ComboBox2.Value = wsh.Cells(r, 5).Value
    Me.PGNumber.Value = wsh.Cells(r, 4).Value
    Me.TPM.Value = wsh.Cells(r, 6).Value
    Me.ComboBox3.Value = wsh.Cells(r, 14).Value
End Sub

[HansV]

Change the ComboBox1_Change procedure to

Code: Select all

Private Sub ComboBox1_Change()
    Const FirstRow = 24
    Const ModuleCol = 1 ' A
    Const BreakCol = 11 ' K
    Dim wsh As Worksheet
    Dim r As Long
    Dim LastRow As Long
    Dim n As Long
    Dim c As Long
    Me.lbxDetails.Clear
    If Me.ComboBox1.ListIndex >= 0 Then
        Set wsh = Worksheets("TPM FORM")
        LastRow = wsh.Cells(wsh.Rows.Count, BreakCol).End(xlUp).Row
        For r = FirstRow To LastRow
            If wsh.Cells(r, ModuleCol).Value = Me.ComboBox1 And wsh.Cells(r, BreakCol).Value <> "" Then
                Me.lbxDetails.AddItem r
                n = n + 1
                Me.lbxDetails.Column(1, n - 1) = wsh.Cells(r, BreakCol).Value
            End If
        Next r
    End If
End Sub

(This is still based on your previous file, I haven't looked at the new one yet)

[krishnaa_kumarr88]

Brilliant work Hans.

Thanks a lot

[HansV]

Finally, to store the data in the MAINTENANCE sheet:

Code: Select all

Private Sub CommandButton1_Click()
    Dim wsh As Worksheet
    Dim r As Long
    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, 6).Value = Me.ComboBox5.Value
    wsh.Cells(r, 7).Value = Me.TextBox2.Value
End Sub

[HansV]

I forgot to mention: the code behind the Clear button wasn't quite right. This should work better:

Code: Select all

Private Sub Clear_Click()
    'Empty Boxes
    Me.ComboBox2.Value = ""
    Me.PGNumber.Value = ""
    Me.TPM.Value = ""
    Me.ComboBox3.Value = ""
    Me.ComboBox4.Value = ""
    Me.ComboBox5.Value = ""
    Me.TextBox2.Value = ""
    Me.Result1.Value = False
End Sub

[krishnaa_kumarr88]

Thanks a lot Hans.

Thanks a lot for your time and good work.

[krishnaa_kumarr88]

Hi Rudi and Hans,

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

You helped me before for the search engine issue. I have following scenario ,
I created a userform with multiple options. I got stuck in few places. Please help me if you know.

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) click on MAINTENANCE command button.
2.It open MAINTENANCE FORM. I have problem with the red highlighed area in the MAINTENANCE FORM in sheet 5. That is, if the user click on the ISSUE STILL OPEN check box , i would like to pull up the ACTION TAKEN information alone from MAINTENACNE FORM and i would like to add ACTION TAKEN information in the ACTION TAKEN column in sheet1 under the name TPM FORM and i would like to add another coloumn in the pivot table for ACTION TAKEN IN sheet2. if the user click ISSUE STILL OPEN checkbox , i would not like to store the information in the MAINTENANCE SHEET. i would like to store all the information like MODULE, BREAKDOWN NUMBER, MACHINE NAME, ETC on the MAINTENANCE SHEET only if the user click on CLOSE THE ISSUE under the MAINTENANCE FORM.

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

HOPE YOU CAN HELP ME,
THANKS A LOT AGAIN

[HansV]

I don't see a red highlighted area on the MAINTENANCE form, and the ISSUE STILL OPEN check box is disabled, so I cannot click it.