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?

Thread: Experts Help

Thread Tools

Display

Threaded View

Experts Help

Tags for this Thread

Posting Permissions