Solved

Code Interferring with copy and paste

Posted on 2011-03-16
9
322 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 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