date in listbox

[Gilbert Aponte]

I have a listbox which I want to display the day of the
week. It already displays the sheet info, date, but the
day of the week column is exactly like the date column.
The cell i reference within the spreadsheet is a different
cell all together. Here is a sample of the code I'm using:

im SheetData() As String
Set OriginalSheet = ActiveSheet
ShtCnt = ActiveWorkbook.Sheets.Count
ReDim SheetData(1 To ShtCnt, 1 To 4)
ShtNum = 1
For Each Sht In ActiveWorkbook.Sheets
If Sht.Name = ActiveSheet.Name Then _
ListPos = ShtNum - 1
SheetData(ShtNum, 1) = Sht.Name
Select Case TypeName(Sht)
Case "Worksheet"
SheetData(ShtNum, 2) = "Sheet"
End Select
' Display the date
If Sht.Range("B5") Then
SheetData(ShtNum, 3) = Sht.Range("B5")
Else
SheetData(ShtNum, 3) = " "
End If
' Display the day of the week
If Sht.Range("E25") Then
SheetData(ShtNum, 4) = Sht.Range("E25")
'If Sht.Visible Then
'SheetData(ShtNum, 4) = "Yes"
Else
SheetData(ShtNum, 4) = " . "
'SheetData(ShtNum, 4) = "No"
End If
ShtNum = ShtNum + 1
Next Sht
With ListBox1
.ColumnWidths = "75 pt;40 pt;60 pt;50 pt"
.List = SheetData
.ListIndex = ListPos
End With
End Sub

The cell E25 within the spreadsheet is formatted: Custom -
dddd

 

[Bill Li]

Hi Gilbert,

If the Range("E25") is a date, you'd better change

SheetData(ShtNum, 4) = Sht.Range("E25")

to

SheetData(ShtNum, 4) =format(Sht.Range("E25"),"dddd")

Best Regards

Bill