xainthowell
New Member
- Joined
- Mar 3, 2023
- Messages
- 24
- Office Version
- 2013
- Platform
- Windows
I'm trying to change the forecolor of the txtFVSC but unfortunately it didn't change at all when I double click the record in the listbox and populate it in the textboxes does anyone has the idea? Thanks
this is the code when doubleclick the listbox:
VBA Code:
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 = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 5), "dd-mmm-yy")
Me.txtMSMC.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 6), "dd-mmm-yy")
Me.txtFVSC.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 7), "dd-mmm-yy")
' Set the default text color for the textbox
txtFVSC.ForeColor = vbBlack
' Calculate the number of days between the value in the textbox and the current date
Dim daysSinceFVSC As Integer
daysSinceFVSC = DateDiff("d", txtFVSC.Value, Date)
' Check if the value in the textbox is less than or equal to the current date
If txtFVSC.Value <= Date Then
txtFVSC.ForeColor = vbRed
' Check if the value in the textbox is between current date - 30 and current date
ElseIf txtFVSC.Value >= Date - 30 Then
txtFVSC.ForeColor = vbOrange
' Check if the value in the textbox is more than a month expired
ElseIf daysSinceFVSC > 30 Then
txtFVSC.ForeColor = vbBlue ' Change the color to blue for example
End If
Me.txtCFVGL.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 8), "dd-mmm-yy")
Me.txtTMC.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 9), "dd-mmm-yy")
Me.txtSSL.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 10), "dd-mmm-yy")
Me.txtGRB.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 11), "dd-mmm-yy")
Me.txtNCaptain.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 12)
Me.txtCLicense.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 13)
Me.txtCLExDate.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 14), "dd-mmm-yy")
Me.txtCSIBNo.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 15)
Me.txtCSIBExDate.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 16), "dd-mmm-yy")
Me.txtNMechanic.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 17)
Me.txtMLicense.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 18)
Me.txtMLExDate.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 19), "dd-mmm-yy")
Me.txtMSIBNo.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 20)
Me.txtMSIBExDate.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 21), "dd-mmm-yy")
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
Whole Excel VBA code:
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 = ""
Me.txtSearch.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 = ""
Me.txtSearch.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 = ""
Me.txtSearch.Value = ""
'-------------------------------------------------------------------------------------------'
Call Refresh_Data
End Sub
Private Sub cmdSearch_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim last_row As Long
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
Dim i As Long
If Application.WorksheetFunction.CountIf(sh.Range("B2"), Me.txtSearch.Text) = 0 Then
MsgBox "No match found!!!", vbOKOnly + vbInformation
Me.txtSearch.Value = ""
Call Refresh_Data
Exit Sub
End If
For i = 2 To last_row
If sh.Cells(i, "B").Value = Me.txtSearch.Text Then
txtVName = sh.Cells(i, "B").Value
End Sub
Private Sub cmdUpdate_Click()
If Me.txtInviID.Value = "" Then
MsgBox "Please select a 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 = ""
Me.txtSearch.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 = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 5), "dd-mmm-yy")
Me.txtMSMC.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 6), "dd-mmm-yy")
Me.txtFVSC.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 7), "dd-mmm-yy")
' Set the default text color for the textbox
txtFVSC.ForeColor = vbBlack
' Calculate the number of days between the value in the textbox and the current date
Dim daysSinceFVSC As Integer
daysSinceFVSC = DateDiff("d", txtFVSC.Value, Date)
' Check if the value in the textbox is less than or equal to the current date
If txtFVSC.Value <= Date Then
txtFVSC.ForeColor = vbRed
' Check if the value in the textbox is between current date - 30 and current date
ElseIf txtFVSC.Value >= Date - 30 Then
txtFVSC.ForeColor = vbOrange
' Check if the value in the textbox is more than a month expired
ElseIf daysSinceFVSC > 30 Then
txtFVSC.ForeColor = vbBlue ' Change the color to blue for example
End If
Me.txtCFVGL.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 8), "dd-mmm-yy")
Me.txtTMC.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 9), "dd-mmm-yy")
Me.txtSSL.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 10), "dd-mmm-yy")
Me.txtGRB.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 11), "dd-mmm-yy")
Me.txtNCaptain.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 12)
Me.txtCLicense.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 13)
Me.txtCLExDate.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 14), "dd-mmm-yy")
Me.txtCSIBNo.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 15)
Me.txtCSIBExDate.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 16), "dd-mmm-yy")
Me.txtNMechanic.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 17)
Me.txtMLicense.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 18)
Me.txtMLExDate.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 19), "dd-mmm-yy")
Me.txtMSIBNo.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 20)
Me.txtMSIBExDate.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 21), "dd-mmm-yy")
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