| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Lost my Paste
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
I meant to use debug.print instead of msgboxes, but I bet either will help.
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 -- Dave Peterson |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
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 "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 |
|
#9
|
|||
|
|||
|
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 |
|
#10
|
|||
|
|||
|
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 |
|
#11
|
|||
|
|||
|
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?? -- my911 ------------------------------------------------------------------------ my911's Profile: http://www.excelforum.com/member.php...o&userid=26733 View this thread: http://www.excelforum.com/showthread...hreadid=337783 |
|
#12
|
|||
|
|||
|
You have another reply at your other post.
my911 wrote: 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?? -- my911 ------------------------------------------------------------------------ my911's Profile: http://www.excelforum.com/member.php...o&userid=26733 View this thread: http://www.excelforum.com/showthread...hreadid=337783 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Paste Special | Excel Discussion (Misc queries) | |||
| Paste rows of numbers from Word into single Excel cell | Excel Discussion (Misc queries) | |||
| copy paste cell character limit | Excel Discussion (Misc queries) | |||
| How to cut and paste with locked formulas | Excel Discussion (Misc queries) | |||
| Paste Special Question | Excel Discussion (Misc queries) | |||
Linear Mode
