Consulting

Results 1 to 4 of 4

Thread: Experts Help

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,970
    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
    Last edited by Aussiebear; 05-14-2025 at 08:09 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •