How to fill list box with weekdays

[Oshikawa]

Hello,

I want to create a form that will have a listbox. The listbox will be filled
with a month worth of dates however only Saturdays and Sunday (not any other
day of the week).

Any ideas out there.

 

[Mike H]

Hi,

I suspect there are a lot of ideas but you need to clarify what you want:--

Your message header:-
How to fill list box with weekdays

Your message body:-
The listbox will be filled
with a month worth of dates however only Saturdays and Sunday (not any other
day of the week).

Which is it?

Mike

 

[Oshikawa]

Hi Mike,

I am fairly new here. Just to give you an idea it will be Something like:

Private Sub Form_Initialize()

Dim Weekdte As date

Dim DateCheck As Date

Dim i as integer




With Me

With Listbox1


For i= 1 to 30

If Format (Weekdte, "dddd mmmm yyyy") = "Saturday" or "Sunday" then

.listboxItem.Add Format (Weekdte, "dddd mmmm yyyy")
End If
Next
End With
End With


End Sub

I think this is what I have written at work can't access from home.When I
run it in work it generates an error message.

Many thanks for your help.

 

[Rick Rothstein]

What Mike meant was your subject line contradicted your posting's
description... Saturday and Sunday are *not* weekdays.

I have a couple of questions though. How is the month (to do this for)
determined? The end of your message makes me wonder... do you have working
code back at your office, but that same code isn't working at your home?
What versions of Excel do you have at both locations?

 

[oshikawa]

Hi Rick,

I see, looking back at it it is true that it might be confusing, I do
apologise.

To answer the first question: the month will be the current month (from the
current day).

The version at work only works when I remove the If...then but that does not
give me the result I want. I have not tested the program at home I am just
replying from home as we are not allowed Internet in work (for security
reason). At work I use Office 2003.

Thank you very much for your time and help. It is much appreciated.

 

[Rick Rothstein]

Try this code...

Private Sub UserForm_Initialize()
Dim X As Long
For X = 1 To Day(DateSerial(Year(Now), Month(Now) + 1, 0))
If Weekday(DateSerial(Year(Now), Month(Now), X), vbMonday) > 5 Then
ListBox1.AddItem DateSerial(Year(Now), Month(Now), X)
End If
Next
End Sub

 

[oshikawa]

Hi Rick,

Thank you very much I have added a few modifications and it works a treat:

Private Sub UserForm_Initialize()
Dim X As Long
Dim CarParkDate As Date

CarParkDate = Date
For X = 1 To 28

With UserForm1
If Weekday(CarParkDate, vbMonday) > 5 Then


.ListBox1.AddItem Format(CarParkDate, "dddd dd mmmm yyyy")

End If
End With
CarParkDate = CarParkDate + 1
Next
End Sub


Thanks again