Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
date in listbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
date in listbox
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 -----Original Message----- 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox 2 takes the value of Listbox 1 | Excel Discussion (Misc queries) | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
Listbox B if LIstbox A equals | Excel Discussion (Misc queries) | |||
Date formatting in a multi column listbox | Excel Programming | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |