INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Worksheet Change Code Not Firing

Worksheet Change Code Not Firing

(OP)
Hi There

I am trying to allow a user to select multiple values from a data validation list. The values should be copied to the next empty row in an adjacent column. However, the code does not seem to be firing. Can anyone advise what im doing wrong. I have tried adding a couple of msgboxes but nothing happens which makes me think the code is not even running when the user selects something from the list

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler

Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long
lCol = Target.Column 'column with data validation cell
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
    If Target.Value = "" Then GoTo exitHandler
    Application.EnableEvents = False
    Select Case Target.Column
       Case 7, 8, 9, 10, 11, 12, 14, 15, 16, 17, 18, 19, 20
        If Target.Offset(0, 1).Value = "" Then
           lRow = Target.Row
        Else
           lRow = Cells(Rows.Count, lCol + 1).End(xlUp).Row + 1
        End If
        Cells(lRow, lCol + 1).Value = Target.Value
        Target.ClearContents
    End Select
   
End If

exitHandler:
  Application.EnableEvents = True
  
End Sub 

RE: Worksheet Change Code Not Firing

Have you tried to add a breakpoint to the beginning of your procedure, display locals window, execute step by step the code and observe program flow?

combo

RE: Worksheet Change Code Not Firing

Elsie,

Make sure that Application.EnableEvents is TRUE before you attempt to run this event. You may have inadvertently left it FALSE.

The code does run.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Worksheet Change Code Not Firing

(OP)
Skip to the rescue as usual

RE: Worksheet Change Code Not Firing

(OP)
Is there anyway to use this code but have it so that it only fires for one data validation list in the worksheet but not to fire for the other data validation lists that are on the same worksheet (same column but different rows)

RE: Worksheet Change Code Not Firing

You can use the "Target" argument (as in "ByVal Target As Range") to detect which cell was changed to trigger the call, then make your subsequent actions take account of this.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close