Hi all,
I have a userform in which a listbox resides. The user has to select an item on the listbox, fill in the userform and then the data entered will be populated against the item selected. The columns that are pulled into my listbox are columns 2,4,7,8. They are all text apart from column 8, which is the date. In the spreadsheet that the listbox pulls from, the date is in UK format. However it displays as US format inside the listbox which is very confusing for the user as we live in the UK and are used to dd/mm/yyyy! I need to be able to change it to the UK format!
The code to the userform is below (the listbox population bit is at the end):
Option Explicit
Private Sub cboTracker_Change()
End Sub
Private Sub cmdClear_Click()
Dim ctl As MSForms.Control
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub
Private Sub cmdExit_Click()
Unload Me
End Sub
Private Sub cmdOK_Click()
Dim ws As Worksheet
Dim iRow As Long
Dim RowCOunt As Long
If Not IsDate(Me.txtdtDateFollow.Value) Then
MsgBox "The Date booked box must contain a date.", vbExclamation, "Post Appointment Entry Form Error"
Me.txtdtDateFollow.SetFocus
Exit Sub
End If
If Me.cboTracker.Value = "" Then
MsgBox "Please enter the initials of the person whom you would like to track this follow up.", vbExclamation, "Post Appointment Entry Form Error"
Me.cboTracker.SetFocus
Exit Sub
End If
If Me.txtFollowUp.Value = "" Then
MsgBox "Please enter the follow up actions that are required.", vbExclamation, "Post Appointment Entry Form Error"
Me.txtFollowUp.SetFocus
Exit Sub
End If
If Me.txtPostContent.Value = "" Then
MsgBox "Please enter in the content of the appointment. This should contain what the appointment was about/its purpose, what was demoed and how the appointment was received.", vbExclamation, "Post Appointment Entry Form Error"
Me.txtPostContent.SetFocus
Exit Sub
End If
If lstAppointment.ListIndex = -1 Then 'checked but nothing selected in listbox
MsgBox "You must select the appointment you just had.", , "Missing Info" 'select a mode since you checked the box
lstAppointment.SetFocus
Exit Sub
End If
Set ws = Worksheets("Sheet1")
iRow = lstAppointment.List(lstAppointment.ListIndex, 4)
RowCOunt = Worksheets("Sheet1").Range("J3").CurrentRegion.Rows.Count
With Worksheets("Sheet1").Range("J3")
ws.Cells(iRow, 10).Value = Me.txtPostContent.Value
ws.Cells(iRow, 11).Value = Me.txtFollowUp.Value
ws.Cells(iRow, 12).Value = DateValue(Me.txtdtDateFollow.Value)
ws.Cells(iRow, 13).Value = Me.cboTracker.Value
End With
Unload Me
End Sub
Private Sub txtdtDateFollow_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Me.txtdtDateFollow.Value = Format(Me.txtdtDateFollow, "dd/mm/yyyy")
End Sub
Private Sub lstAppointment_Click()
End Sub
Private Sub txtFollowUp_Change()
End Sub
Private Sub txtPostContent_Change()
End Sub
Private Sub UserForm_Initialize()
Dim rng As Range, r As Range
Dim LastRow As Long
Dim I As Long
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
("H3:H1302"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
With Worksheets("Sheet1")
LastRow = .Range("H" & Rows.Count).End(xlUp).Row
.Range("A4:N" & LastRow).AutoFilter Field:=8
Set rng = .Range(.Cells(4, 1), .Cells(LastRow, 1))
Set rng = rng.SpecialCells(xlCellTypeVisible)
ReDim rtab(0 To rng.Count - 1, 1 To 5)
For Each r In rng
rtab(I, 1) = r.Offset(0, 1)
rtab(I, 2) = r.Offset(0, 3)
rtab(I, 3) = r.Offset(0, 6)
rtab(I, 4) = r.Offset(0, 7)
rtab(I, 5) = r.Row
I = I + 1
Next
End With
With lstAppointment
.ColumnCount = 5
.ColumnHeads = False
.ColumnWidths = "70 pt;100pt;20pt;30pt;0 pt"
.List = rtab
End With
End Sub
I have tried adding this in as the end of the code after the listbox is populated, but it is not working...any other ideas would be greatly appreciated!
For I = 0 To lstAppointment.ListCount-1
lstAppointment.List(I, 7) = Format(DateValue(lstAppointment.List(I,7)), "dd/mm/yyyy")
Next I
Thanks!
Bookmarks