calander control and copy command problem

[Guest]

Hello:
I have a macro to invoke the calander control when a particular is active or
selected. When I select the cell and the calander control appears I find the
date I want, double click and that date drops in the active cell. the problem
I found is when I inserted the calander control from Insert object pull
down my paste function is greyed out. When I deleted the calander control my
paste function became available.
Here is the code that I have in my spreadsheet:

Private Sub Calendar1_DblClick()
ActiveCell.NumberFormat = "mm/dd/yy"
ActiveCell = Calendar1.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("A1:A100"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
Else: Calendar1.Visible = False
End If
End Sub

any help as to why my paste function is unavailabe (greyed out) when I
insert the calander control?
Thank you

 

[Ron de Bruin]

Hi

I update my example
http://www.rondebruin.nl/calendar.htm

 

[Guest]

Ron: The calander control works perfectly using your previous macro. The
problem I have when the calander is inserted is it disables my paste and
paste special functions (grayed out).
For example: I want to copy Range A1 to Range A2 and the copy is selectable
but not the paste. I found when I removed the calander (Insert>Object) my
paste and paste special functions work again??

 

[Ron de Bruin]

Hi

I change one line in the code so the copy/paste will work outside A1:A100
But you can't do it in the range

 

[Guest]

When I used your macro I changed the range to ("B7") - ie:If Not
Application.Intersect(Range("B7"), Target) Is Nothing Then.... so that when I
clicked in B7 the calander would activate. Im still confused. Ive went all
over the spreadsheet even out to column Z and down to Row 200 and the paste
function is still disabled??

 

[Ron de Bruin]

Hi

You can't copy/paste B7 but you can use the copy/paste in all the other cells now