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