Experts Help

**njadvani** · 04-06-2024, 05:16 PM

https://1drv.ms/u/s!AmbrmHjyJCEpg1FH...-4slr?e=X45PGII have developed a userform, for record taking and it inserts new line on top of table everytime new "case" is formed.
These data are then displayed on listbox underneath the userform.
I am trying to EDIT the data entered in table earlier.
So far, I have worked out that if I CLICK on particular data from listbox, then userform gets populated with relevant fields of data, along with small box at bottom which indicates what row that data contains from masterlist.
Now, if I edit the data in userform and press UPDATE button, it should enter the NEW data at the same place of masterdata list in sheet.
So, ideally my steps were to delete that data from selectedrow and enter new data in selected row.
It keeps on deleting the data but unable to enter the new data when pressed update.

Please help.
Here's the code for
Listbox click event

' ListBox Click Event
Private Sub ListBox_Click()
    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
    ' Populate other form fields similarly
    ' ...
End Sub

Code for Save() button

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

Code for UPDATE BUTTON

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
    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
    ' 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

.

Your valuable 2 cents will be appreciated. Is there a problem with my logic or its just coding bit?

**p45cal** · 04-07-2024, 05:54 PM

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

**njadvani** · 04-10-2024, 06:11 AM

p45cal

Thank you for the edit. There are however 2 issues with it.
1. When updating 1st row on table. It updates on 1st row on sheet. But this just happens to 1st row on table.
2. When updating time. The stop time and start time doesn't get populated on actual cells. They go empty on sheet.

I not only acknowledge it but highly appreciate your help! How do you even figure out that easily? Amazed really.

**p45cal** · 04-10-2024, 07:34 AM

Looking at this quickly…

Originally Posted by njadvani

1. When updating 1st row on table. It updates on 1st row on sheet. But this just happens to 1st row on table.

try changing If rowToMatch > 1 Then to If rowToMatch >= 1 Then

Originally Posted by njadvani

2. When updating time. The stop time and start time doesn't get populated on actual cells. They go empty on sheet.

In the cmdupdate_Click sub, the two lines which do that are:

  .Cells(rowToMatch, "F").Value = dateVariable
  .Cells(rowToMatch, "G").Value = dateVariable1

however, the variables dateVariable and dateVariable1 are both empty at that point (unlike the similar lines in the cmdsave_Click sub) so you need to make sure they contain the right data.

Thread: Experts Help

Thread Tools

Display

Experts Help

Tags for this Thread

Posting Permissions