Instead of using the irritating msgboxes, I used debug.prints (And I added a
couple more...)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Debug.Print "-=-=-=-=-=-=-="
Debug.Print "1--" & Application.CutCopyMode
If Target.Cells.Count 1 Then Exit Sub
Debug.Print "1.5--" & Application.CutCopyMode
If Not Application.Intersect(Range("C6"), Target) Is Nothing Then
Debug.Print "2--" & Application.CutCopyMode
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Debug.Print "3--" & Application.CutCopyMode
Calendar1.Top = Target.Top + Target.Height
Debug.Print "4--" & Application.CutCopyMode
Calendar1.Visible = True
Debug.Print "5--" & Application.CutCopyMode
' select Today's date in the Calendar
Calendar1.Value = Date
Debug.Print "6--" & Application.CutCopyMode
Else
Debug.Print "6.5--" & Application.CutCopyMode
Calendar1.Visible = False
Debug.Print "7--" & Application.CutCopyMode
End If
Debug.Print "8--" & Application.CutCopyMode
End Sub
I copied a cell and then selected a cell (but not C6).
I got this back.
-=-=-=-=-=-=-=
1--1
1.5--1
6.5--1
7--0
8--0
So the line between 6.5 and 7 cleared the .cutcopymode. That section is:
Debug.Print "6.5--" & Application.CutCopyMode
Calendar1.Visible = False
Debug.Print "7--" & Application.CutCopyMode
So hiding the calendar cleared the .cutcopymode.
=====
Then I tried it by copying a cell and selecting C6.
I got this back:
-=-=-=-=-=-=-=
1--1
1.5--1
2--1
3--0
4--0
5--0
6--0
8--0
So the line between 2 and 3 broke the .cutcopymode.
Debug.Print "2--" & Application.CutCopyMode
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Debug.Print "3--" & Application.CutCopyMode
So setting the calendar's left position broke the .cutcopymode.
==
I think you have to make a choice (and you already did!).
But this isn't unusual. Running macros (even event macros like Ron's) can kill
the .cutcopymode. And if you hadn't noticed, the Edit|Undo was probably broken,
too.
Such is life with macros.
Mike R wrote:
Dave
You may have bitten off more than you want to chew with me. If so, just say
so and I understand. Here is what I am getting after I replaced Rons code
with yours:
Selected any cell (C9) and msg box 1--0 appears, click €œOK€ and 7--0
appears, click €œOK and 8--0 appears. Right mouse click select €œcopy€ & now I
can copy (the cell is selected and the moving dash line is there). When I
select a second cell to paste to (D9) msg box 1--1 appears, then 7--0, then
8--0. When I clear all the msg boxes I loose my selected cell to paste. From
here all I can do is relay what I am getting I wish I was half as good as
some of you guys with this stuff and I might not need so much help. I can
suggest that you make a sheet up and try it on your own. That way you can see
first hand. Here is what I have, the cell locations obviously dont really
matter but: C6 formatted ddd mmm dd yyyy In H6 formatted mmm dd yyyy I have
the formula =DATE(YEAR(C6),MONTH(C6)+1,0). From here I inserted the object
Calendar 9.0, then of course the code from Ron that stopped my paste
function. I removed Rons and replaced yours and here we are. I would like
to keep the calendar and get back my paste if possible. If not I will do
without the calendar!! Thank you soooo much for your help.
Mike R
"Dave Peterson" wrote:
If you noticed, I just added a bunch of msgboxes to Ron's code.
You can either remove those messages when you're happy, or even just try this
against a copy of your workbook.
Mike R wrote:
Debra Thank you for the post
Makes sense that there can only be one SelectionChange procedure per sheet.
But If I replace Ron's code with Dave's I will have my pase back because
Ron's code is gone. If I just remove Ron"s code I have paste. Am I not
fully understanding something?
"Debra Dalgleish" wrote:
I'm not Dave, but Dave's code should replace Ron's code -- you can only
have one SelectionChange procedure per worksheet module.
When you select a different cell, it will trigger the SelectionChange
event, and the code will run. You'll see the messages in the code. The
number in the last message displayed will tell you where the code
stopped working.
Mike R wrote:
Dave Thanks for the post
Do I put this in via the "tab" "view Code" and paste? If so this is where
the other code is and there seems to be some lines that are the same in the
two codes. "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" is
in the first line of yours and Ron's. it brings a debug error. I know so
little about VBA I don't really know what to do from here. I am learning so
anything you can do to point me in a direction is greatly appreciated.
Thanks again!!
"Dave Peterson" wrote:
You can find out by adding some debug statements in your code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
msgbox "1--" & application.cutcopymode
If Target.Cells.Count 1 Then Exit Sub
If Not Application.Intersect(Range("C6"), Target) Is Nothing Then
msgbox "2--" & application.cutcopymode
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
msgbox "3--" & application.cutcopymode
Calendar1.Top = Target.Top + Target.Height
msgbox "4--" & application.cutcopymode
Calendar1.Visible = True
msgbox "5--" & application.cutcopymode
' select Today's date in the Calendar
Calendar1.Value = Date
msgbox "6--" & application.cutcopymode
Else
Calendar1.Visible = False
msgbox "7--" & application.cutcopymode
End If
msgbox "8--" & application.cutcopymode
End Sub
Copy something and then change your selection to see when .cutcopymode changes.
Mike R wrote:
I have some code from Ron deBruin that is used with a calander to select a
date for an active cell. The code works great, but I loose my paste
function. I narrowed it down to this code by removing all code one at a time
until I found the one that was denying me the ability to Paste. What part of
this code is removing my Paste function? And how can I keep the function and
the code?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Application.Intersect(Range("C6"), 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
Else: Calendar1.Visible = False
End If
End Sub
--
Dave Peterson
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
--
Dave Peterson
--
Dave Peterson