xainthowell
New Member
- Joined
- Mar 3, 2023
- Messages
- 24
- Office Version
- 2013
- Platform
- Windows
when doubleclick the record in the listbox the date displayed is in numeric like in the picture below anyone knows why? sorry for a newbie question. Thanks
VBA Code:
Private Sub cmdClear_Click()
Me.txtVName.Value = ""
Me.txtGT.Value = ""
Me.txtNT.Value = ""
Me.txtCO.Value = ""
Me.txtCVR.Value = ""
Me.txtMSMC.Value = ""
Me.txtFVSC.Value = ""
Me.txtCFVGL.Value = ""
Me.txtTMC.Value = ""
Me.txtSSL.Value = ""
Me.txtGRB.Value = ""
Me.txtNCaptain.Value = ""
Me.txtCLicense.Value = ""
Me.txtCLExDate.Value = ""
Me.txtCSIBNo.Value = ""
Me.txtCSIBExDate.Value = ""
Me.txtNMechanic.Value = ""
Me.txtMLicense.Value = ""
Me.txtMLExDate.Value = ""
Me.txtMSIBNo.Value = ""
Me.txtMSIBExDate.Value = ""
Me.txtHPort.Value = ""
Me.txtOwner.Value = ""
Me.txtContact.Value = ""
Call Refresh_Data
End Sub
Private Sub cmdDelete_Click()
If Me.txtInviID.Value = "" Then
MsgBox "Please select the record to Delete"
Exit Sub
End If
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim Selected_Row As Long
Selected_Row = Application.WorksheetFunction.Match(CLng(Me.txtInviID.Value), sh.Range("A:A"), 0)
' Selected row will be deleted---------------------------------------------------------'
sh.Range("A" & Selected_Row).EntireRow.Delete
' After delete we must empty all the input fields -------------------------------------'
Me.txtVName.Value = ""
Me.txtGT.Value = ""
Me.txtNT.Value = ""
Me.txtCO.Value = ""
Me.txtCVR.Value = ""
Me.txtMSMC.Value = ""
Me.txtFVSC.Value = ""
Me.txtCFVGL.Value = ""
Me.txtTMC.Value = ""
Me.txtSSL.Value = ""
Me.txtGRB.Value = ""
Me.txtNCaptain.Value = ""
Me.txtCLicense.Value = ""
Me.txtCLExDate.Value = ""
Me.txtCSIBNo.Value = ""
Me.txtCSIBExDate.Value = ""
Me.txtNMechanic.Value = ""
Me.txtMLicense.Value = ""
Me.txtMLExDate.Value = ""
Me.txtMSIBNo.Value = ""
Me.txtMSIBExDate.Value = ""
Me.txtHPort.Value = ""
Me.txtOwner.Value = ""
Me.txtContact.Value = ""
End Sub
Private Sub cmdSave_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim last_row As Long
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
'Validate if the textbox is empty ---------------------------------------------------------'
If Me.txtVName.Value = "" Then
MsgBox "Please Enter Name of Fishing Vessel", vbCritical
Exit Sub
End If
If Me.txtGT.Value = "" Then
MsgBox "Please Enter Gross Tonnage", vbCritical
Exit Sub
End If
If Me.txtNT.Value = "" Then
MsgBox "Please Enter Net Tonnage", vbCritical
Exit Sub
End If
If Me.txtCO.Value = "" Then
MsgBox "Please Enter Certificate of Ownership", vbCritical
Exit Sub
End If
If Me.txtCVR.Value = "" Then
MsgBox "Please Enter Certificate of Vessel Registry", vbCritical
Exit Sub
End If
If Me.txtMSMC.Value = "" Then
MsgBox "Please Enter Minimum Safe Manning Certificate", vbCritical
Exit Sub
End If
If Me.txtFVSC.Value = "" Then
MsgBox "Please Enter Fishing Vessel Safety Certificate", vbCritical
Exit Sub
End If
If Me.txtCFVGL.Value = "" Then
MsgBox "Please Enter Commercial Fishing Vessel Gear License", vbCritical
Exit Sub
End If
If Me.txtTMC.Value = "" Then
MsgBox "Please Enter Tonnage Measurement Certificate", vbCritical
Exit Sub
End If
If Me.txtSSL.Value = "" Then
MsgBox "Please Enter Ship Station License", vbCritical
Exit Sub
End If
If Me.txtGRB.Value = "" Then
MsgBox "Please Enter Garbage Record Book", vbCritical
Exit Sub
End If
If Me.txtNCaptain.Value = "" Then
MsgBox "Please Enter Captain's Name", vbCritical
Exit Sub
End If
If Me.txtCLicense.Value = "" Then
MsgBox "Please Enter Captain's License", vbCritical
Exit Sub
End If
If Me.txtCLExDate.Value = "" Then
MsgBox "Please Enter Captain's License Expiration Date", vbCritical
Exit Sub
End If
If Me.txtCSIBNo.Value = "" Then
MsgBox "Please Enter Captain's SIB No.", vbCritical
Exit Sub
End If
If Me.txtCSIBExDate.Value = "" Then
MsgBox "Please Enter Captain's SIB Expiration Date", vbCritical
Exit Sub
End If
If Me.txtNMechanic.Value = "" Then
MsgBox "Please Enter Mechanic's Name", vbCritical
Exit Sub
End If
If Me.txtMLicense.Value = "" Then
MsgBox "Please Enter Mechanic's License", vbCritical
Exit Sub
End If
If Me.txtMLExDate.Value = "" Then
MsgBox "Please Enter Mechanic's License Expiration Date", vbCritical
Exit Sub
End If
If Me.txtMSIBNo.Value = "" Then
MsgBox "Please Enter Mechanic's SIB No.", vbCritical
Exit Sub
End If
If Me.txtMSIBExDate.Value = "" Then
MsgBox "Please Enter Mechanic's SIB Expiration Date", vbCritical
Exit Sub
End If
If Me.txtHPort.Value = "" Then
MsgBox "Please Enter the Home Port", vbCritical
Exit Sub
End If
If Me.txtOwner.Value = "" Then
MsgBox "Please Enter the Owner", vbCritical
Exit Sub
End If
If Me.txtContact.Value = "" Then
MsgBox "Please Enter Contact Number", vbCritical
Exit Sub
End If
' Add the input value in the column of the Data sheet ----------------------------'
sh.Range("A" & last_row + 1).Value = "=Row()-1"
sh.Range("B" & last_row + 1).Value = Me.txtVName.Value
sh.Range("C" & last_row + 1).Value = Me.txtGT.Value
sh.Range("D" & last_row + 1).Value = Me.txtNT.Value
sh.Range("E" & last_row + 1).Value = Me.txtCO.Value
sh.Range("F" & last_row + 1).Value = Me.txtCVR.Value
sh.Range("G" & last_row + 1).Value = Me.txtMSMC.Value
sh.Range("H" & last_row + 1).Value = Me.txtFVSC.Value
sh.Range("I" & last_row + 1).Value = Me.txtCFVGL.Value
sh.Range("J" & last_row + 1).Value = Me.txtTMC.Value
sh.Range("K" & last_row + 1).Value = Me.txtSSL.Value
sh.Range("L" & last_row + 1).Value = Me.txtGRB.Value
sh.Range("M" & last_row + 1).Value = Me.txtNCaptain.Value
sh.Range("N" & last_row + 1).Value = Me.txtCLicense.Value
sh.Range("O" & last_row + 1).Value = Me.txtCLExDate.Value
sh.Range("P" & last_row + 1).Value = Me.txtCSIBNo.Value
sh.Range("Q" & last_row + 1).Value = Me.txtCSIBExDate.Value
sh.Range("R" & last_row + 1).Value = Me.txtNMechanic.Value
sh.Range("S" & last_row + 1).Value = Me.txtMLicense.Value
sh.Range("T" & last_row + 1).Value = Me.txtMLExDate.Value
sh.Range("U" & last_row + 1).Value = Me.txtMSIBNo.Value
sh.Range("V" & last_row + 1).Value = Me.txtMSIBExDate.Value
sh.Range("W" & last_row + 1).Value = Me.txtHPort.Value
sh.Range("X" & last_row + 1).Value = Me.txtOwner.Value
sh.Range("Y" & last_row + 1).Value = Me.txtContact.Value
ThisWorkbook.Save
MsgBox "Data Saved", vbInformation
'Input the value and Record the current Date filed ------------------------------------------'
sh.Range("Z" & last_row + 1).Value = Now
'Clear Data after saving -------------------------------------------------------------------'
Me.txtVName.Value = ""
Me.txtGT.Value = ""
Me.txtNT.Value = ""
Me.txtCO.Value = ""
Me.txtCVR.Value = ""
Me.txtMSMC.Value = ""
Me.txtFVSC.Value = ""
Me.txtCFVGL.Value = ""
Me.txtTMC.Value = ""
Me.txtSSL.Value = ""
Me.txtGRB.Value = ""
Me.txtNCaptain.Value = ""
Me.txtCLicense.Value = ""
Me.txtCLExDate.Value = ""
Me.txtCSIBNo.Value = ""
Me.txtCSIBExDate.Value = ""
Me.txtNMechanic.Value = ""
Me.txtMLicense.Value = ""
Me.txtMLExDate.Value = ""
Me.txtMSIBNo.Value = ""
Me.txtMSIBExDate.Value = ""
Me.txtHPort.Value = ""
Me.txtOwner.Value = ""
Me.txtContact.Value = ""
'-------------------------------------------------------------------------------------------'
Call Refresh_Data
End Sub
Private Sub cmdUpdate_Click()
If Me.txtInviID.Value = "" Then
MsgBox "Please select the record to Update"
Exit Sub
End If
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim Selected_Row As Long
Selected_Row = Application.WorksheetFunction.Match(CLng(Me.txtInviID.Value), sh.Range("A:A"), 0)
'Validate if the textbox is empty ---------------------------------------------------------'
If Me.txtVName.Value = "" Then
MsgBox "Please Enter Name of Fishing Vessel", vbCritical
Exit Sub
End If
If Me.txtGT.Value = "" Then
MsgBox "Please Enter Gross Tonnage", vbCritical
Exit Sub
End If
If Me.txtNT.Value = "" Then
MsgBox "Please Enter Net Tonnage", vbCritical
Exit Sub
End If
If Me.txtCO.Value = "" Then
MsgBox "Please Enter Certificate of Ownership", vbCritical
Exit Sub
End If
If Me.txtCVR.Value = "" Then
MsgBox "Please Enter Certificate of Vessel Registry", vbCritical
Exit Sub
End If
If Me.txtMSMC.Value = "" Then
MsgBox "Please Enter Minimum Safe Manning Certificate", vbCritical
Exit Sub
End If
If Me.txtFVSC.Value = "" Then
MsgBox "Please Enter Fishing Vessel Safety Certificate", vbCritical
Exit Sub
End If
If Me.txtCFVGL.Value = "" Then
MsgBox "Please Enter Commercial Fishing Vessel Gear License", vbCritical
Exit Sub
End If
If Me.txtTMC.Value = "" Then
MsgBox "Please Enter Tonnage Measurement Certificate", vbCritical
Exit Sub
End If
If Me.txtSSL.Value = "" Then
MsgBox "Please Enter Ship Station License", vbCritical
Exit Sub
End If
If Me.txtGRB.Value = "" Then
MsgBox "Please Enter Garbage Record Book", vbCritical
Exit Sub
End If
If Me.txtNCaptain.Value = "" Then
MsgBox "Please Enter Captain's Name", vbCritical
Exit Sub
End If
If Me.txtCLicense.Value = "" Then
MsgBox "Please Enter Captain's License", vbCritical
Exit Sub
End If
If Me.txtCLExDate.Value = "" Then
MsgBox "Please Enter Captain's License Expiration Date", vbCritical
Exit Sub
End If
If Me.txtCSIBNo.Value = "" Then
MsgBox "Please Enter Captain's SIB No.", vbCritical
Exit Sub
End If
If Me.txtCSIBExDate.Value = "" Then
MsgBox "Please Enter Captain's SIB Expiration Date", vbCritical
Exit Sub
End If
If Me.txtNMechanic.Value = "" Then
MsgBox "Please Enter Mechanic's Name", vbCritical
Exit Sub
End If
If Me.txtMLicense.Value = "" Then
MsgBox "Please Enter Mechanic's License", vbCritical
Exit Sub
End If
If Me.txtMLExDate.Value = "" Then
MsgBox "Please Enter Mechanic's License Expiration Date", vbCritical
Exit Sub
End If
If Me.txtMSIBNo.Value = "" Then
MsgBox "Please Enter Mechanic's SIB No.", vbCritical
Exit Sub
End If
If Me.txtMSIBExDate.Value = "" Then
MsgBox "Please Enter Mechanic's SIB Expiration Date", vbCritical
Exit Sub
End If
If Me.txtHPort.Value = "" Then
MsgBox "Please Enter the Home Port", vbCritical
Exit Sub
End If
If Me.txtOwner.Value = "" Then
MsgBox "Please Enter the Owner", vbCritical
Exit Sub
End If
If Me.txtContact.Value = "" Then
MsgBox "Please Enter Contact Number", vbCritical
Exit Sub
End If
' Add the input value in the column of the Data sheet ----------------------------'
sh.Range("A" & last_row + 1).Value = "=Row()-1"
sh.Range("B" & last_row + 1).Value = Me.txtVName.Value
sh.Range("C" & last_row + 1).Value = Me.txtGT.Value
sh.Range("D" & last_row + 1).Value = Me.txtNT.Value
sh.Range("E" & last_row + 1).Value = Me.txtCO.Value
sh.Range("F" & last_row + 1).Value = Me.txtCVR.Value
sh.Range("G" & last_row + 1).Value = Me.txtMSMC.Value
sh.Range("H" & last_row + 1).Value = Me.txtFVSC.Value
sh.Range("I" & last_row + 1).Value = Me.txtCFVGL.Value
sh.Range("J" & last_row + 1).Value = Me.txtTMC.Value
sh.Range("K" & last_row + 1).Value = Me.txtSSL.Value
sh.Range("L" & last_row + 1).Value = Me.txtGRB.Value
sh.Range("M" & last_row + 1).Value = Me.txtNCaptain.Value
sh.Range("N" & last_row + 1).Value = Me.txtCLicense.Value
sh.Range("O" & last_row + 1).Value = Me.txtCLExDate.Value
sh.Range("P" & last_row + 1).Value = Me.txtCSIBNo.Value
sh.Range("Q" & last_row + 1).Value = Me.txtCSIBExDate.Value
sh.Range("R" & last_row + 1).Value = Me.txtNMechanic.Value
sh.Range("S" & last_row + 1).Value = Me.txtMLicense.Value
sh.Range("T" & last_row + 1).Value = Me.txtMLExDate.Value
sh.Range("U" & last_row + 1).Value = Me.txtMSIBNo.Value
sh.Range("V" & last_row + 1).Value = Me.txtMSIBExDate.Value
sh.Range("W" & last_row + 1).Value = Me.txtHPort.Value
sh.Range("X" & last_row + 1).Value = Me.txtOwner.Value
sh.Range("Y" & last_row + 1).Value = Me.txtContact.Value
'Input the value and Record the current Date filed ------------------------------------------'
sh.Range("Z" & Selected_Row).Value = Now
'Clear Data after saving -------------------------------------------------------------------'
Me.txtVName.Value = ""
Me.txtGT.Value = ""
Me.txtNT.Value = ""
Me.txtCO.Value = ""
Me.txtCVR.Value = ""
Me.txtMSMC.Value = ""
Me.txtFVSC.Value = ""
Me.txtCFVGL.Value = ""
Me.txtTMC.Value = ""
Me.txtSSL.Value = ""
Me.txtGRB.Value = ""
Me.txtNCaptain.Value = ""
Me.txtCLicense.Value = ""
Me.txtCLExDate.Value = ""
Me.txtCSIBNo.Value = ""
Me.txtCSIBExDate.Value = ""
Me.txtNMechanic.Value = ""
Me.txtMLicense.Value = ""
Me.txtMLExDate.Value = ""
Me.txtMSIBNo.Value = ""
Me.txtMSIBExDate.Value = ""
Me.txtHPort.Value = ""
Me.txtOwner.Value = ""
Me.txtContact.Value = ""
Call Refresh_Data
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
' This will display all the input value of the textbox when you double click the record in the listbox '
Me.txtInviID.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0) ' Populate the textbox with ID number when double click the choosen record in the listbox'
Me.txtVName.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
Me.txtGT.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
Me.txtNT.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
Me.txtCO.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)
Me.txtCVR.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 5)
Me.txtMSMC.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 6)
Me.txtFVSC.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 7)
Me.txtCFVGL.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 8)
Me.txtTMC.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 9)
Me.txtSSL.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 10)
Me.txtGRB.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 11)
Me.txtNCaptain.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 12)
Me.txtCLicense.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 13)
Me.txtCLExDate.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 14)
Me.txtCSIBNo.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 15)
Me.txtCSIBExDate.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 16)
Me.txtNMechanic.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 17)
Me.txtMLicense.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 18)
Me.txtMLExDate.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 19)
Me.txtMSIBNo.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 20)
Me.txtMSIBExDate.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 21)
Me.txtHPort.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 22)
Me.txtOwner.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 23)
Me.txtContact.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 24)
Call Refresh_Data
End Sub
Private Sub UserForm_Activate()
Call Refresh_Data
End Sub
Sub Refresh_Data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim last_row As Long
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
With Me.ListBox1
.ColumnCount = 26 ' ID is column value is 0, and A to Z is column value 1 to 25 '
.ColumnWidths = "30,200,70,70,70,70,70,70,70,70,70,70,200,70,120,70,120,200,70,120,70,120,200,200,90,100"
If last_row = 1 Then
.RowSource = "Data!A2:Z2" & last_row
Else
.RowSource = "Data!A2:Z" & last_row
End If
End With
End Sub