Excel Calendar

[jollyboy]

I have a large spreadsheet that is shared on a network drive. It is updated by 10-12 people. One column is for a date to be entered, i want a calendar to appear everytime a cell in that column is selected where the user can click on a date and that date is entered in the selected cell, the calendar will then disappear.

My VBA knowledge is fairly basic so any help would be most welcome.

Thanks in advance.

 

[venkat1926]

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

 

[jollyboy]

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.

 

[Biedubbeljoe]

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

 

[cheveley]

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

 

[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

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

 

[tusharm]

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