Lost my Paste

[Guest]

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]

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.

 

[Dave Peterson]

I meant to use debug.print instead of msgboxes, but I bet either will help.

 

[Guest]

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!!

 

[Debra Dalgleish]

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.

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!!


:

 

[Guest]

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?

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.

 

[Dave Peterson]

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.

 

[Guest]

Thanks for the post back Dave
I think I am catching up with you now. I will try that and see, thanks for
staying with me on this, I really do appreciate it.

Mike R

 

[Guest]

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 Ron’s 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 don’t 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 Ron’s 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]

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.

 

[Guest]

Dave you are a glutton for punishment...
I thank you so much for the education, I know it was not without a time
investment. You guys here are an exceptional asset to people like me that
want to learn, and with people like you and your effort I can not fail.
Thanks again.
Mike R
P.S. How long did it take you to learn all this? You are amazing!!!!

 

[Dave Peterson]

It's always a learning process.

There are a lot of techniques you can pick up in the newsgroups (where I learned
most).

Just lurking helps, but trying is better (failing is ok, too).

Dave you are a glutton for punishment...
I thank you so much for the education, I know it was not without a time
investment. You guys here are an exceptional asset to people like me that
want to learn, and with people like you and your effort I can not fail.
Thanks again.
Mike R
P.S. How long did it take you to learn all this? You are amazing!!!!

 

[my911]

Sorry to hear about your trouble.

However I WANT TO KILL CUT & PASTE.

...to prevent users from (unknowingly) pasting Conditional Formats an
Data Validation properties into "new" cells.

Have you found a way TO MAKE IT HAPPEN?

Here is a segment from my Auto_Open Macro
======================================================
Application.DefaultSaveFormat = xlNormal
Worksheets("Inputs&Actuals").Activate
Sheets("Inputs&Actuals").Select
Range("I6").Select

CreateWorkbookMenus
CreateChartMenus

Application.CellDragAndDrop = False
' Added 8-28-05
Application.CutCopyMode = False


Company = Range("CompanyName").Value
=======================================================
The line that reads

Application.CutCopyMode = False

seems to be what I need. but it doesn't work

two lines before kills Drag & Drop, but I can still Cut/Paste.

Any Clues?....Help?...

Debra?

 

[Dave Peterson]

You have another reply at your other post.

Sorry to hear about your trouble.

However I WANT TO KILL CUT & PASTE.

..to prevent users from (unknowingly) pasting Conditional Formats and
Data Validation properties into "new" cells.

Have you found a way TO MAKE IT HAPPEN?

Here is a segment from my Auto_Open Macro
======================================================
Application.DefaultSaveFormat = xlNormal
Worksheets("Inputs&Actuals").Activate
Sheets("Inputs&Actuals").Select
Range("I6").Select

CreateWorkbookMenus
CreateChartMenus

Application.CellDragAndDrop = False
' Added 8-28-05
Application.CutCopyMode = False

Company = Range("CompanyName").Value
=======================================================
The line that reads

Application.CutCopyMode = False

seems to be what I need. but it doesn't work

two lines before kills Drag & Drop, but I can still Cut/Paste.

Any Clues?....Help?...

Debra??