Experts Help

[p45cal]

There are 5 lines added to the userform code-module below, all to do with the variable BlockedListBoxClick. Search for comments 'change to find where those lines are.

Dim BlockedListBoxClick As Boolean 'change

Private Sub cmddelete_Click()
    Dim x As Long
    Dim y As Long
    x = Sheets("Worksheet").Range("A" & Rows.Count).End(xlUp).Row
    For y = 8 To x
        If Sheets("Worksheet").Cells(y, 1).Value = txtsearch.Text Then
            Rows(y).Delete        
        End If
    Next y        
    ' Clear Boxes
    Me.txtsearch.Value = ""
    Me.cmbwf.Value = ""
    Me.txtwtg.Value = ""
    Me.txtwindspeed.Value = ""
    Me.txtalarmcode.Value = ""
    Me.txtalarmdes.Value = ""
    Me.txtstoptime.Value = ""
    Me.txtstarttime.Value = ""
    Me.cmballocation.Value = ""
    Me.cmbattend.Value = ""
    Me.ComboBox1.Value = ""    
    MsgBox "Data has been deleted", vbInformation
End Sub

Private Sub cmdexit_Click()
    If MsgBox("Do you want to exit this form?", vbQuestion + vbYesNo, "Confirmation") = vbYes Then
         Unload Me
    End If
End Sub

Private Sub cmdreset_Click()
    Unload Me
    UserForm1.Show
End Sub

Private Sub cmdsave_Click()
    Dim sh As Worksheet
    Dim lr As Long
    Dim dataRange As Range
    Dim timeDiff As Variant    
    ' Set worksheet and find the last row
    Set sh = ThisWorkbook.Sheets("Worksheet")
    lr = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row    
    ' Define the range containing the existing data
    Set dataRange = sh.Range("A8:L" & lr)    
    ' Insert a new row at the top of the table
    sh.Rows("8:8").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow    
    ' VALIDATION    
    If Me.txtwtg.Value = "" Then
        MsgBox "Please enter the WTG No.", vbCritical
        Exit Sub
    End If    
    If IsNumeric(Me.txtwindspeed) = False Then
        MsgBox "Please enter the wind speed", vbCritical
        Exit Sub
    End If    
    If IsNumeric(Me.txtalarmcode) = False Then
        MsgBox "Please enter the alarm code", vbCritical
        Exit Sub
    End If    
    ' Add Data in Excel Sheet
    ' Calculate time difference only if starttime is not empty
    If Me.txtstarttime.Value <> "" Then
        ' Calculate time difference
        timeDiff = DateDiff("n", Me.txtstoptime.Value, Me.txtstarttime.Value) ' Returns difference in minutes        
        ' Convert minutes to hours and minutes format
        Dim hours As Long
        Dim minutes As Long        
        hours = Abs(timeDiff \ 60) ' Get hours
        minutes = Abs(timeDiff Mod 60) ' Get remaining minutes        
        ' Format the time difference as hh:mm
        Dim formattedTimeDiff As String
        formattedTimeDiff = Format(hours, "00") & ":" & Format(minutes, "00")
    Else
        formattedTimeDiff = "" ' If starttime is blank, leave the new column blank
    End If
    Dim userInput As Variant
    userInput = txtstoptime
    Dim userInput1 As Variant
    userInput1 = txtstarttime    
    ' Convert the user input to a date using CDate function
    Dim dateVariable As Date
    dateVariable = CDate(Me.txtstoptime.Value)
    Me.txtstoptime.Value = dateVariable
    ' Check if txtstarttime is not empty
    If Me.txtstarttime.Value <> "" Then
        Dim dateVariable1 As Date
        dateVariable1 = CDate(Me.txtstarttime.Value)
        ' Update the value of txtstoptime with formatted date and time
        Me.txtstoptime.Value = dateVariable
        Me.txtstarttime.Value = dateVariable1
    End If    
    With sh
        ' Copy format from the row below the inserted row
        dataRange.Rows(2).Copy
        .Rows(8).PasteSpecial Paste:=xlPasteFormats        
        ' Copy values from the userform to the inserted row
        .Cells(8, "A").Value = Me.cmbwf.Value
        .Cells(8, "B").Value = Me.txtwtg.Value
        .Cells(8, "C").Value = Me.txtwindspeed.Value
        .Cells(8, "D").Value = Me.txtalarmcode.Value
        .Cells(8, "E").Value = Me.txtalarmdes.Value
        .Cells(8, "F").Value = dateVariable
        .Cells(8, "G").Value = dateVariable1
        .Cells(8, "H").Value = formattedTimeDiff
        .Cells(8, "I").Value = Me.ComboBox1.Value
        .Cells(8, "J").Value = Me.cmballocation.Value
        .Cells(8, "K").Value = Me.cmbattend.Value        
    End With
    ' Clear Boxes
    Me.cmbwf.Value = ""
    Me.txtwtg.Value = ""
    Me.txtwindspeed.Value = ""
    Me.txtalarmcode.Value = ""
    Me.txtalarmdes.Value = ""
    Me.txtstoptime.Value = ""
    Me.txtstarttime.Value = ""
    Me.txtdowntime.Value = ""
    Me.cmballocation.Value = ""
    Me.cmbattend.Value = ""
    Me.txtnotes.Value = ""
    Me.ComboBox1.Value = ""    
    Call Refresh_data    
    MsgBox "Data has been added to the worksheet", vbInformation
    Call Refresh_data
End Sub

Sub Refresh_data()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Worksheet")
    Dim lr As Long
    lr = Sheets("Worksheet").Range("A" & Rows.Count).End(xlUp).Row    
    If lr = 7 Then lr = 8
    With Me.ListBox
        .ColumnCount = 10
        .ColumnHeads = True
        .ColumnWidths = "90, 30,30,30,100,80,80,80,90,100"
        .RowSource = "Worksheet! A8:J" & lr
    End With
End Sub

Private Sub cmdupdate_Click()
    ' Set worksheet and find the last row
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Worksheet")    
    ' Determine the last row in column A
    Dim lr As Long
    lr = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row    
    ' Convert the value in TextBox1 to a numeric data type
    Dim rowToMatch As Long
    If Not IsNumeric(Me.TextBox1.Value) Or Me.TextBox1.Value = "" Then
        MsgBox "Please enter a valid row number.", vbExclamation
        Exit Sub
    End If
    rowToMatch = CLng(Me.TextBox1.Value)   
    ' Check if the row number is within the valid range
    If rowToMatch < 1 Or rowToMatch > lr Then
        MsgBox "Row number is out of range.", vbExclamation
        Exit Sub
    End If    
    ' If necessary, adjust the row number to match Excel's row numbering
    If rowToMatch > 1 Then
        rowToMatch = rowToMatch + 7 ' Adjust for header rows
    End If    
    ' Validate mandatory fields
    If Me.txtwtg.Value = "" Then
        MsgBox "Please enter the WTG No.", vbCritical
        Exit Sub
    End If    
    If Not IsNumeric(Me.txtwindspeed.Value) Then
        MsgBox "Please enter a valid wind speed.", vbCritical
        Exit Sub
    End If    
    If Not IsNumeric(Me.txtalarmcode.Value) Then
        MsgBox "Please enter a valid alarm code.", vbCritical
        Exit Sub
    End If    
    ' Calculate time difference if necessary
    Dim formattedTimeDiff As String
    If Me.txtstarttime.Value <> "" Then
        Dim timeDiff As Long
        timeDiff = DateDiff("n", Me.txtstarttime.Value, Me.txtstoptime.Value)
        Dim hours As Long
        Dim minutes As Long
        hours = Abs(timeDiff \ 60)
        minutes = Abs(timeDiff Mod 60)
        formattedTimeDiff = Format(hours, "00") & ":" & Format(minutes, "00")
    Else
        formattedTimeDiff = ""
    End If
    BlockedListBoxClick = True 'change    
    With sh        
        ' Copy values from the userform to the inserted row
        .Cells(rowToMatch, "A").Value = Me.cmbwf.Value
        .Cells(rowToMatch, "B").Value = Me.txtwtg.Value
        .Cells(rowToMatch, "C").Value = Me.txtwindspeed.Value
        .Cells(rowToMatch, "D").Value = Me.txtalarmcode.Value
        .Cells(rowToMatch, "E").Value = Me.txtalarmdes.Value
        .Cells(rowToMatch, "F").Value = dateVariable
        .Cells(rowToMatch, "G").Value = dateVariable1
        .Cells(rowToMatch, "H").Value = formattedTimeDiff
        .Cells(rowToMatch, "I").Value = Me.ComboBox1.Value
        .Cells(rowToMatch, "J").Value = Me.cmballocation.Value
        .Cells(rowToMatch, "K").Value = Me.cmbattend.Value        
    End With
    BlockedListBoxClick = False 'change
    ' Display a message indicating successful update
    MsgBox "Row updated successfully.", vbInformation    
    ' Clear form fields
    ClearFormFields
End Sub
  
Private Sub ClearFormFields()
    ' Clear form fields
    Me.cmbwf.Value = ""
    Me.txtwtg.Value = ""
    Me.txtwindspeed.Value = ""
    Me.txtalarmcode.Value = ""
    Me.txtalarmdes.Value = ""
    Me.txtstoptime.Value = ""
    Me.txtstarttime.Value = ""
    Me.txtdowntime.Value = ""
    Me.cmballocation.Value = ""
    Me.cmbattend.Value = ""
    Me.txtnotes.Value = ""
    Me.ComboBox1.Value = ""    
    ' Optionally, add additional form fields to clear
End Sub

Private Sub txtstoptime_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    ' Check if the user pressed Ctrl + ;
    If KeyCode = 186 And (Shift And 2) Then
         ' Input today's date and current time into the text box
         Me.txtstoptime.Value = Format(Now, "dd/mm/yyyy hh:mm").       
    End If
End Sub

Private Sub txtstarttime_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    ' Check if the user pressed Ctrl + ;
    If KeyCode = 186 And (Shift And 2) Then
         ' Input today's date and current time into the text box
         Me.txtstarttime.Value = Format(Now, "dd/mm/yyyy hh:mm")
    End If
End Sub

Private Sub UserForm_Activate()
    cmbwf.List = Array("North Brown Wind Farm", "The Bluff Wind Farm", "Hallet Wind Farm", "Hallet Hill Wind Farm", "Oaklands Hill Wind Farm", _
    "Snowtown Wind Farm", "Clements Gap Wind Farm")
    cmballocation.List = Array("Manufacturer", "Owner")
    cmbattend.List = Array("Nandit", "Dhruv", "Pragnesh", "Janak", "Other")
    ComboBox1.List = Array("Reset by Suzlon Monitoring Centre", "Reset by Site tech", "Reset by India Team", "Breakdown", "Repetitive Alarm-Technician has to attend", "")
    Call Refresh_data
End Sub

Private Sub cmdsearch_Click()
    Dim searchValue As String
    Dim foundRange As Range
    Dim resultRow As Long
    Dim ws As Worksheet
    Dim lr As Long
    Dim i As Long    
    ' Clear the list box
    Do While Me.ListBox.ListCount > 0
        Me.ListBox.RemoveItem 0
    Loop    
    ' Get the search value from the text box
    searchValue = Me.txtsearch.Value    
    ' Check if the search value is empty
    If searchValue = "" Then
        MsgBox "Please enter a search value.", vbExclamation
        Exit Sub
    End If    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Worksheet")    
    ' Find the last row with data
    lr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row    
    ' Loop through the rows to find matches
    For i = 8 To lr
        If ws.Cells(i, 1).Value = searchValue Then
            ' Add the matching row number to the list box
            Me.ListBox.AddItem i - 7 ' Subtract 7 to get the relative row number from 1 instead of 8
            ' Store the matching row number for further use
            resultRow = i
        End If
    Next i    
    ' If no matches found, display a message
    If Me.ListBox.ListCount = 0 Then
        MsgBox "No matching records found.", vbInformation
        Exit Sub
    End If    
    ' Display the details of the first matching record
    Me.cmbwf.Value = ws.Cells(resultRow, 1).Value
    Me.txtwtg.Value = ws.Cells(resultRow, 2).Value
    Me.txtwindspeed.Value = ws.Cells(resultRow, 3).Value
    Me.txtalarmcode.Value = ws.Cells(resultRow, 4).Value
    Me.txtstoptime.Value = ws.Cells(resultRow, 5).Value
    Me.txtstarttime.Value = ws.Cells(resultRow, 6).Value
    Me.ComboBox1.Value = ws.Cells(resultRow, 7).Value
    Me.cmballocation.Value = ws.Cells(resultRow, 8).Value
    Me.cmbattend.Value = ws.Cells(resultRow, 9).Value    
    ' Optionally, you can loop through all matching records and add their details to the list box
    ' For i = 8 To lr
    '     If ws.Cells(i, 1).Value = searchValue Then
    '         Me.ListBox.AddItem ws.Cells(i, 1).Value & " - " & ws.Cells(i, 2).Value ' Add more columns if needed
    '     End If
    ' Next i
End Sub

Private Sub txtalarmcode_AfterUpdate()
    Dim ws As Worksheet
    Dim lookupValue As String
    Dim resultValue As Variant
    Dim lastRow As Long
    Dim i As Long    
    ' Set the reference to the "alarmcode" sheet
    Set ws = ThisWorkbook.Sheets("alarmcode")    
    ' Find the last row of data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row    
    ' Get the value from txtalarmcode
    lookupValue = Me.txtalarmcode.Value    
    ' Loop through the rows of the "alarmcode" sheet to find the corresponding value
    For i = 2 To lastRow
        If ws.Cells(i, 1).Value = lookupValue Then
            ' If a match is found, assign the corresponding values to txtalarmdes and txtnotes
            Me.txtalarmdes.Value = ws.Cells(i, 2).Value
            Me.txtnotes.Value = ws.Cells(i, 3).Value
            Exit Sub
        End If
    Next i    
    ' If no corresponding value is found, clear txtalarmdes and txtnotes
    Me.txtalarmdes.Value = ""
    Me.txtnotes.Value = ""
    Call Refresh_data
End Sub

' ListBox Click Event

Private Sub ListBox_Click()
    If Not BlockedListBoxClick Then  ' change
         Dim selectedRow As Long
         selectedRow = Me.ListBox.ListIndex + 8 ' Adjust for header rows        
         ' Populate form fields with data from the selected row
         Me.TextBox1.Value = selectedRow - 7
         Me.cmbwf.Value = ThisWorkbook.Sheets("Worksheet").Cells(selectedRow, 1).Value
         Me.txtwtg.Value = ThisWorkbook.Sheets("Worksheet").Cells(selectedRow, 2).Value
         Me.txtwindspeed.Value = ThisWorkbook.Sheets("Worksheet").Cells(selectedRow, 3).Value
         Me.txtalarmcode.Value = ThisWorkbook.Sheets("Worksheet").Cells(selectedRow, 4).Value
         Me.txtalarmdes.Value = ThisWorkbook.Sheets("Worksheet").Cells(selectedRow, 5).Value
         Me.txtstoptime.Value = ThisWorkbook.Sheets("Worksheet").Cells(selectedRow, 6).Value
         Me.txtstarttime.Value = ThisWorkbook.Sheets("Worksheet").Cells(selectedRow, 7).Value
         Me.ComboBox1.Value = ThisWorkbook.Sheets("Worksheet").Cells(selectedRow, 9).Value
         Me.cmballocation.Value = ThisWorkbook.Sheets("Worksheet").Cells(selectedRow, 10).Value
         Me.cmbattend.Value = ThisWorkbook.Sheets("Worksheet").Cells(selectedRow, 11).Value
    End If. ' change
    ' Populate other form fields similarly
    ' ...
End Sub