Solved

Code Interferring with copy and paste

Posted on 2011-03-16
9
328 Views
Last Modified: 2012-06-22
Excel 2003 vba

I have a combobox I'm moving up and down the spreadsheet in Column"C"

Since I have done this my "Copy and Paste" functionality has disappeared ?

any suggestions
Thanks
fordraiders
Private Sub Worksheet_SelectionChange(ByVal target As Range)

ComboBox1.Top = ActiveCell.Top
ComboBox1.Top = target.Top
   
    'ComboBox1 value (if changed) will be stored in same row as target, column c
Set oldTarget = Cells(target.Row, 3)
ComboBox1.Height = ActiveCell.RowHeight - 6
ComboBox1.Width = 148
' get out of column Headers
If target.Rows.Count = Cells.Rows.Count Then Exit Sub
   If Not Intersect(target, [A1:AU1]) Is Nothing Then
      MsgBox "Not Allowed To Edit!", vbCritical, "Template Protection Error"
      Range("A2").Select
   End If

End Sub

Open in new window

0
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35151121
When you manipulate a control such as changing it's properties the current copy paste operation is canceled.

It's difficult to work around this problem.

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35151178
Here is a start at solving the problem. It isn't well tested. Note that it does not determine the difference between a cut and a copy and assumes a copy.

Dim PreviousRange As Range

Private Sub Worksheet_SelectionChange(ByVal target As Range)

Dim oldTarget As Variant
Dim CopyPending As Boolean

If Application.CutCopyMode Then
    CopyPending = True
End If

ComboBox1.Top = ActiveCell.Top
ComboBox1.Top = target.Top
   
    'ComboBox1 value (if changed) will be stored in same row as target, column c
Set oldTarget = Cells(target.Row, 3)
ComboBox1.Height = ActiveCell.RowHeight - 6
ComboBox1.Width = 148

If CopyPending Then
    PreviousRange.Copy
    target.Select
Else
    Set PreviousRange = target
End If
' get out of column Headers
If target.Rows.Count = Cells.Rows.Count Then Exit Sub
   If Not Intersect(target, [A1:AU1]) Is Nothing Then
      MsgBox "Not Allowed To Edit!", vbCritical, "Template Protection Error"
      Range("A2").Select
   End If

End Sub

Kevin
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 400 total points
ID: 35151334
A more complete solution that detects the difference between cut and copy and initializes when the workbook is opened.

Dim PreviouslySelectedRange As Range
Dim oldTarget As Variant

Public Sub Worksheet_Activate()

    If PreviouslySelectedRange Is Nothing Then Set PreviouslySelectedRange = Selection

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim CutCopyMode As XlCutCopyMode
   
    If PreviouslySelectedRange Is Nothing Then
        Set PreviouslySelectedRange = Target
        Application.CutCopyMode = False
    End If

    CutCopyMode = Application.CutCopyMode

    ComboBox1.Top = ActiveCell.Top
    ComboBox1.Top = Target.Top
   
    'ComboBox1 value (if changed) will be stored in same row as target, column c
    Set oldTarget = Cells(Target.Row, 3)
    ComboBox1.Height = ActiveCell.RowHeight - 6
    ComboBox1.Width = 148

    Select Case CutCopyMode
        Case xlCopy:
            PreviouslySelectedRange.Copy
        Case xlCut:
            PreviouslySelectedRange.Cut
        Case Else:
            Set PreviouslySelectedRange = Target
    End Select
   
    ' get out of column Headers
    If Target.Rows.Count = Cells.Rows.Count Then Exit Sub
   
    If Not Intersect(Target, [A1:AU1]) Is Nothing Then
        MsgBox "Not Allowed To Edit!", vbCritical, "Template Protection Error"
        Range("A2").Select
    End If

End Sub

Place this code in the ThisWorkbook code module:

Private Sub Workbook_Open()

    Sheet1.Worksheet_Activate

End Sub

Kevin
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 100 total points
ID: 35152351
You can use something like:
Private Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "User32" () As Long

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Dim blnCopying As Boolean
If Application.CutCopyMode <> False Then
    blnCopying = True
    OpenClipboard 0
End If
ComboBox1.Top = ActiveCell.Top
ComboBox1.Top = target.Top
     
    'ComboBox1 value (if changed) will be stored in same row as target, column c
Set oldTarget = Cells(target.Row, 3)
ComboBox1.Height = ActiveCell.RowHeight - 6
ComboBox1.Width = 148
' get out of column Headers
If target.Rows.Count <> Cells.Rows.Count Then
   If Not Intersect(target, [A1:AU1]) Is Nothing Then
      MsgBox "Not Allowed To Edit!", vbCritical, "Template Protection Error"
      Range("A2").Select
   End If
End If
If blnCopying Then CloseClipboard
End Sub

Open in new window

0
 
LVL 3

Author Comment

by:fordraiders
ID: 35153966
zorvek, worked great ! Thanks


rorya,
code seems to not work with cutting and pasting...but copy and paste work..

0
 
LVL 3

Author Comment

by:fordraiders
ID: 35153967
zorvek, 2nd solution that is
0
 
LVL 3

Author Comment

by:fordraiders
ID: 35154031
ZORVEK, Opening workbook and getting error on this line:

Private Sub Workbook_Open()

    Sheet1.Worksheet_Activate

End Sub

0
 
LVL 3

Author Comment

by:fordraiders
ID: 35156133
changed it to...
Sheets("Sheet1").Activate  ? <----
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 35156159
Thanks to all,,...
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question