Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

Code Interferring with copy and paste

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
Fordraiders
Asked:
Fordraiders
  • 5
  • 3
2 Solutions
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rory ArchibaldCommented:
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
 
FordraidersAuthor Commented:
zorvek, worked great ! Thanks


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

0
 
FordraidersAuthor Commented:
zorvek, 2nd solution that is
0
 
FordraidersAuthor Commented:
ZORVEK, Opening workbook and getting error on this line:

Private Sub Workbook_Open()

    Sheet1.Worksheet_Activate

End Sub

0
 
FordraidersAuthor Commented:
changed it to...
Sheets("Sheet1").Activate  ? <----
0
 
FordraidersAuthor Commented:
Thanks to all,,...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now