Solved

Code Interferring with copy and paste

Posted on 2011-03-16
9
316 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now