Solved

Code Interferring with copy and paste

Posted on 2011-03-16
9
326 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
  • 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
Industry Leaders: 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

713 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