ActiveX Controls
 

Hi all,
everytime I place an ActiveX Control on my spreadsheet my calender
function ceases to work. The calender appears as soon as I click on the tool
at insertion point on the Developers Tab, and then the calendar will not
disappear unless I highlight it and cut it out. Afterwhich the function does
not work until I close the workbook and then re-open it.

I need to place a List box in cell D12 so that I can use the code supplied
most kindly by Mike H
My Calendar code is as follows useing the add in Calendar Control 12

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count 1 Then
Exit Sub
End If
If Not Application.Intersect(Range("D7,F16"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True

' Select Today's date in the Calendar
Calendar1.Value = Date

ElseIf Calendar1.Visible Then Calendar1.Visible = False
end if

If Sheet1.Range("D7") < Sheet1.Range("F16") Or _
Sheet1.Range("F16") Sheet1.Range("D7") Then
MsgBox "The Contact Date cannot be before the Date Arrived at
the RSU"
Range("D7,F16").Value = ""
Range("D7").Select
End If
end sub

Mike's Code is as follows:

Remember I don't have a list box on the spreadsheet. So the following code
produces an error because there is No ListBox. If poss I need to combine the
two routines so that the generated List box will only appear when the user
selects cell D12

ListBox1.Clear
MyPath = "C:\"
MyName = Dir$(MyPath & "*.xls") 'only looks for xl files
With ListBox1
Do While MyName < ""
MyName = Left(MyName, Len(MyName) - 4) 'removes .XLS from end of
name
..AddItem MyName
MyName = Dir
Loop
End With
End If
End If

Any help would be kindly appreciated
Lee