Excel Calendar

check this url you may get some idea

http://www.fontstuff.com/vba/vbatut07.htm
or
http://www.rondebruin.nl/calendar.htm
or

http://www.download.com/Excel-Calendar-Template/3000-2077_4-10346449.html

Hi Venkat

I've been to this website and i've followed the instructions. I got the calendar working no problems, my problem is this: the people who update the spreadsheet will not bother to run the macro each time they want to put a date in. I need them to select a cell and the calendar appear automatically. I've a few sites that solve most of my problems, the trouble I now have is putting them altogether.

Sorry to be a pain.

Put this in your worksheet.
You have to change the range as you wishes..
This example does column C and D from cell 10 til 300
Hope this helps

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("C10:C300,D10:D300"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
Calendar1.Value = Date
Else: Calendar1.Visible = False
End If

End Sub


Biedubbeljoe

i have tried to use your code biedubbeljoe so that when F20 is selected the calendar automatically appears but no luck. i adjusted the code as follows;


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("f20"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
Calendar1.Value = Date
Else: Calendar1.Visible = False
End If

End Sub

cheveley said:

i have tried to use your code biedubbeljoe so that when F20 is selected the calendar automatically appears but no luck. i adjusted the code as follows;


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("f20"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
Calendar1.Value = Date
Else: Calendar1.Visible = False
End If

End Sub

Click to expand...

I have to search in column D till column O:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("D10:D400,E10:E400,F10:F400,G10:G400,H10:H400,I10:I400,J10:J400,K10:K400,L10:L400,M10:M400,N10:N400,O10:O400"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
Calendar1.Value = Date
Else: Calendar1.Visible = False
End If

End Sub

There are several limits on what can be done with a shared workbook. Check if what you are trying to do doesn't fall into one of those limits. For a *non-exhaustive* list see http://support.microsoft.com/default.aspx?scid=kb;en-us;130560