It didn't change the forecolor of the textbox

X

xainthowell

New Member
Joined
Mar 3, 2023
Messages
24
Office Version
  1. 2013
Platform
  1. Windows
display form.png



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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
vbOrange is not a VBA color constant. Change it with vbGreen (perhaps try 33023 for something close to Orange). Then it should work.
See Color Constants for more info.

Suggestion: Always use Option Explicit as the first line in a module to make sure to catch this kind of issue. (Also make sure to check the Tools->Options->Require Variable Declaration option to make this default)
 
Upvote 0
You could also use rgbOrange, rather than vbOrange
 
Upvote 0
Solution
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

X
  • Solved
Displayed numeric date instead of the entered format date
Replies
6
Views
336
hicksi
hicksi
X
  • Solved
When update the record the entries are saved in the columnheaders ^^
Replies
2
Views
292
xainthowell
X
SemperFi
  • Solved
VBA Form TextBox needs formatting
Replies
1
Views
336
SemperFi
SemperFi
P
  • Question
Putting data into ListBox1 from UserForm6 to Userform1 listbox
Replies
5
Views
275
dmt32
D
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
326
pure vito
P

Forum statistics

Threads
1,214,925
Messages
6,122,303
Members
449,078
Latest member
nonnakkong

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top