I have created a user form as a basis for calculating
dates in a monthly timetable. The user selects a month
followed by the first day in the week the first working
day falls on from a couple of listboxes and then VBA does
the rest. This works great providing that each month I
start the timetable from WD1. Here's the tricky bit. I
would now like the timetable to begin using minus working
days. Because this might vary I have added another list
box to my form giving the user an option of between -1
and -10 to choose from. Can anyone help with some
syntax? I've copied my existing code below. Please note
I'm an Office 97 user so functions like WeekdayName are
not available to me. I'd gladly appreciate any help.
Many thanks
Jacqui


Private Sub cmdOK_Click()
Dim iMth As Date
Dim iDay As Integer
Dim iBizDay As Integer
Dim dDate As Date
Dim x As String


Application.ScreenUpdating = False
Worksheets("Data").Activate
iBizDay = 1
iDay = 1

'clear the previous data

If Not IsEmpty("d13") Then
x = Range("d13:f13").End(xlDown).Row
Range("d13:f" & x).Select
Selection.ClearContents
Else
Unload fmCalender

End If


'1 determine the month we are dealing with
sMonth = LstMonth.Value
Range("c13").Select
ActiveCell.Value = "01" & " " & sMonth & " " & 2003
dDate = CDate(ActiveCell.Value)

'2 get the current month so we know when to stop
'iMth = Month(ActiveCell.Value)
iMth = DateAdd("d", 45, dDate)

'3 start determining business days (for the month specified
Do Until dDate = iMth
'Do Until Month(dDate) = iMth + 1
If Weekday(dDate) < 7 And Weekday(dDate) 1
Then 'its a weekday
If Trim(Day(dDate)) < TxtDate1 And Trim(Day
(dDate)) < TxtDate2 Then
'need to write this one down
'WeekdayName function only available to
Office 2000 users
'ActiveCell.Offset.(iBizDay - 1, 8) =
WeekdayName(Weekday(dDate), True)
Select Case VBA.Weekday(dDate)
Case 2
ActiveCell.Offset(iBizDay - 1, 1)
= "Mon"
Case 3
ActiveCell.Offset(iBizDay - 1, 1)
= "Tue"
Case 4
ActiveCell.Offset(iBizDay - 1, 1)
= "Wed"
Case 5
ActiveCell.Offset(iBizDay - 1, 1)
= "Thu"
Case 6
ActiveCell.Offset(iBizDay - 1, 1)
= "Fri"
End Select

ActiveCell.Offset(iBizDay - 1, 2) = iBizDay
ActiveCell.Offset(iBizDay - 1, 3) = dDate
'and then increment
iBizDay = iBizDay + 1
dDate = DateAdd("d", 1, dDate)
Else
dDate = DateAdd("d", 1, dDate)
End If
Else
dDate = DateAdd("d", 1, dDate)
End If
Loop

Application.ScreenUpdating = True

End Sub