njadvani
04-06-2024, 05:16 PM
https://1drv.ms/u/s!AmbrmHjyJCEpg1FHnvDTQQr-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?
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?