Customisable drop down calender

[excel_savvy]

Hi, I am trying to create a cutomisable drop down calender.

The only option i have found so far is "microsoft date & time picker control 6.0 (sp4)" active-x control macro.

However so far I cannot seem to customise the dates it allows me to select.

I need it to only allow every second Wednesday to be selectable & if any other day is selected for a customisable error message to pop up.

If anyone can help with the code that needs to sit behind this it would be appreciated.

 

[jsotola]

that can be done with vba code


when you say every second wednesday, when is the first wednesday?

 

[jsotola]

here is a starting point

put a calendar control on your worksheet

put this in the worksheet code

click around the calendar control and have a look at the popups

i put in code to ignore click on the 10th day of any month

you probably want to use the BeforeUpdate event handler to put your code

Private Sub Calendar1_AfterUpdate()
    MsgBox "value" & vbTab & Calendar1.Value & vbCrLf & _
           "day" & vbTab & Calendar1.Day & vbCrLf & _
           "month" & vbTab & Calendar1.Month & vbCrLf & _
           "year" & vbTab & Calendar1.Year, , "Calendar1_AfterUpdate"
    
End Sub


Private Sub Calendar1_BeforeUpdate(Cancel As Integer)
    MsgBox "value" & vbTab & Calendar1.Value & vbCrLf & _
           "day" & vbTab & Calendar1.Day & vbCrLf & _
           "month" & vbTab & Calendar1.Month & vbCrLf & _
           "year" & vbTab & Calendar1.Year, , "Calendar1_BeforeUpdate"
    
    If Calendar1.Day = 10 Then Cancel = 1


End Sub


Private Sub Calendar1_Click()
    MsgBox "Calendar1_Click"
End Sub


Private Sub Calendar1_KeyDown(KeyCode As Integer, ByVal Shift As Integer)
    MsgBox "Calendar1_KeyDown"
End Sub


Private Sub Calendar1_KeyPress(KeyAscii As Integer)
    MsgBox "Calendar1_KeyPress"
End Sub


Private Sub Calendar1_KeyUp(KeyCode As Integer, ByVal Shift As Integer)
    MsgBox "Calendar1_KeyUp"
End Sub


Private Sub Calendar1_NewMonth()
    MsgBox "Calendar1_NewMonth"
End Sub


Private Sub Calendar1_NewYear()
    MsgBox "Calendar1_NewYear"
End Sub