Results 1 to 7 of 7

Trigger Worksheet Change by copy and paste

Threaded View

maddog9486 Trigger Worksheet Change by... 05-16-2014, 02:05 PM
TMS Re: Trigger Worksheet Change... 05-16-2014, 02:15 PM
maddog9486 Re: Trigger Worksheet Change... 05-16-2014, 02:34 PM
TMS Re: Trigger Worksheet Change... 05-16-2014, 06:41 PM
maddog9486 Re: Trigger Worksheet Change... 05-19-2014, 10:25 AM
maddog9486 Re: Trigger Worksheet Change... 05-19-2014, 12:34 PM
maddog9486 Re: Trigger Worksheet Change... 05-20-2014, 12:05 PM
  1. #1
    Registered User
    Join Date
    06-30-2008
    Location
    Georgia
    MS-Off Ver
    2013
    Posts
    72

    Trigger Worksheet Change by copy and paste

    The following code seems to work fine if the user selects a value from the drop down cell in column T, but if that user then copies and pastes his selection into subsequent cells, it appears that the event is not trigger so the data validation does not get added to column S as it should. Could anyone provide some assistance to this? I am sure my code is a little redundant (still trying to learn all this). This code is on the actual sheet tab, just in case you didn't already know that.
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Not Intersect(Target, Range("T:T")) Is Nothing Then
            If Target.Count > 1 Then
                Me.Unprotect Password:="password"
                Application.EnableEvents = False
                Target.Offset(0, -1).Validation.Delete
                Target.Offset(0, -1).Locked = True
                Application.EnableEvents = True
                Me.Protect Password:="password", AllowFiltering:=True
                Exit Sub
            Else:
                If Target.Value <> """" Then
                Me.Unprotect Password:="password"
                Application.EnableEvents = False
                    If Target.Offset(0, 7).Value = "P" Then
                        Target.Offset(0, -1).Validation.Add Type:=xlValidateList, Formula1:="ITR1,ITR2,ITR3,ITR4,ITR5,ITR6,ITR7,ITR8,ITR9,ITR10,ITR11,ITR12,ITR13,ITR14,ITR15"
                        Target.Offset(0, -1).Locked = False
                    Else: Target.Offset(0, -1).Validation.Delete
                        Target.Offset(0, -1).Locked = True
                    End If
                End If
                Application.EnableEvents = True
                Me.Protect Password:="password", AllowFiltering:=True
            End If
        End If
    
    End Sub
    Last edited by maddog9486; 05-20-2014 at 12:07 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Worksheet Change Trigger - Update Another Cell
    By Coeus in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-04-2013, 04:59 AM
  2. Copy and Paste using worksheet change
    By arader1216 in forum Excel Programming / VBA / Macros
    Replies: 34
    Last Post: 02-05-2012, 12:12 AM
  3. Link to cell in different sheet - trigger copy/paste transposed row into column
    By janethenoob in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-10-2011, 01:42 PM
  4. Cell value trigger copy paste macro
    By byrdsongdl in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-12-2010, 03:43 PM
  5. Cell value change to trigger macro (worksheet change event?)
    By Neil Goldwasser in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2006, 10:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1