I found that the "RefEdit.dll" was looking in the Office 14 folder and mine is in Office 12, which I have corrected but I still can't get the spreadsheet to react once data has been entered via the User Form. When I look at the code the partial line (near the end) "ReDim Ray(1" is shown in a grey background. Is something still missing?
Private Sub ComboBox1_Change()
With ComboBox1
.Value = Format(.Value, "dd/mm/yyyy")
End With
If Me.ComboBox1.Value <> "" Then
ComboBox2.Enabled = True
End If
End Sub
Private Sub ComboBox2_Change()
Dim Dts As Date
Dim Txt As String
Dim c As Integer
Dim P As Integer
Dim Dtx As String
If Not ComboBox2.ListIndex = 0 Then
For Dts = ComboBox1 To ComboBox2
c = c + 1
If Weekday(Dts, vbMonday) > 5 Then
Txt = Txt & Dts & Chr(10)
P = P + 1
If P < 3 Then
If P = 1 Then
Dtx = WeekdayName(Weekday(Dts, vbMonday), True, vbMonday)
Else
Dtx = Dtx & " and " & WeekdayName(Weekday(Dts, vbMonday), True, vbMonday)
End If
End If
End If
Next Dts
If P > 0 And c > P Then
MsgBox "Your selection includes one or more weekend days" _
& Chr(10) & "which will not be included in the summary" & _
Chr(10) & Chr(10) & "Weekend Dates" & Chr(10) & Txt, _
vbInformation + vbOKOnly, "Head's Up..."
ElseIf P = c Then
MsgBox "You have selected a " & Dtx & " which will not be included in the summary", vbInformation + vbOKOnly, "Head's Up..."
End If
End If
Call submitEnabled
End Sub
Private Sub CommandButton1_Click()
Dim Rng As Range, Dn As Range
Dim sDt As Date
Dim eDt As Date
Dim Ac As Integer
Dim col As Integer
Set Rng = Range(Range("B5"), Range("B" & Rows.Count).End(xlUp))
sDt = ComboBox1
eDt = ComboBox2
Select Case True
Case Is = holidayButton1: col = 43
Case Is = sickLeaveButton3: col = 53
Case Is = otherOptionButton4: col = 37
End Select
For Each Dn In Rng
If Dn = ComboBox1 Then
For Ac = 1 To 366
If Weekday(Cells(4, Ac + 2), vbMonday) < 6 Then
If Cells(4, Ac + 2) >= sDt And Cells(4, Ac + 2) <= eDt Then
Dn.Offset(, Ac).Interior.ColorIndex = col
End If
End If
Next Ac
End If
Next Dn
Unload Me
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub absenceButtonsEnabled()
If nameBox1.Value <> "" Then
holidayButton1.Enabled = True
sickLeaveButton3.Enabled = True
otherOptionButton4.Enabled = True
End If
End Sub
Private Sub sickLeaveButton3_afterUpdate()
If Me.sickLeaveButton3 = True Then
ComboBox1.Enabled = True
End If
End Sub
Private Sub holidayButton1_afterUpdate()
If Me.holidayButton1 = True Then
ComboBox1.Enabled = True
End If
End Sub
Private Sub submitEnabled()
If ComboBox1.Value <> "" And ComboBox2.Value <> "" Then
CommandButton1.Enabled = True
End If
End Sub
Private Sub ComboBox1_afterUpate()
If Me.ComboBox1.Value <> "" Then
ComboBox2.Enabled = True
End If
End Sub
Private Sub nameBox1_Change()
Call absenceButtonsEnabled
End Sub
Private Sub UserForm_Initialize()
Dim Dys As Integer
Dim n As Integer
Dim Dt As Date
Dys = DateValue("1/1/" & Year(Now)) - DateValue("1/1/" & (Year(Now) - 1))
ReDim Ray(1 To Dys)
Dt = "1/1/" & Year(Now)
For n = 1 To Dys
Ray
= IIf(n = 1, Dt, DateAdd("d", 1, Dt))
Dt = Ray
Next n
Me.ComboBox1.List = Application.Transpose(Ray)
Me.ComboBox2.List = Application.Transpose(Ray)
nameBox1.List = Worksheets("sheet1").Range("B6:B20").Value
End Sub
Many thanks,
Mel
