Getting a VBA or Macro Code to activate upon leaving a cell?

[Desperaotto]

Hello,

I am a first time poster but have used a lot of these threads in the past in increasing my excel skills. I am using Excel 2016 on Windows 7 Pro.

My question is how can I get a VBA macro to activate upon either hitting enter or leaving the cell?

To provide background in what I am trying to accomplish I have a VBA code that is designed to perform a vlookup after something is picked from a drop down list. I am trying to populate both a value that shows a job code and job title in the pick list but only populates a job code once selected since there are other formulas in the spreadsheet which auto-populate based on the job code. Since this report is used in other areas with a define column length, I don't want to have a "help" column to perform this action. I was able to write a VBA macro that accomplishes this action but the VBA only works after I leave the cell than re-select it. The code is below. Is there anyway I can update the VBA to run either when enter is hit or another cell is selected? Thanks for your help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 selectedVal = Target.Value
    If Target.Column = 7 Then
        selectednum = Application.VLookup(selectedVal, Worksheets("Look up Table").Range("picklist"), 2, False)
        If Not IsError(selectednum) Then
            Target.Value = selectednum
        End If
    End If
End Sub

 

[Jerry Sullivan]

Welcome to MrExcel,

The Worksheet_Change event should do what you describe.

When working with event code, you need to prevent your code's actions from unintentionally triggering other events. In your scenario, if the lookup finds a value it writes that to the same cell. By temporarily disabling events, the code below avoids triggering another Worksheet_Change event when it writes that value to the Target cell.

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim sErrMsg As String
 Dim selectedVal As Variant, selectednum As Variant
  
 If Target.Column <> 7 Then GoTo ExitProc
 If Target.CountLarge > 1 Then
   MsgBox "Your message if someone pastes multiple values into Col 7"
   GoTo ExitProc
 End If
 
 selectedVal = Target.Value
 
 On Error GoTo ErrProc
 Application.EnableCancelKey = xlErrorHandler
 Application.EnableEvents = False

 selectednum = Application.VLookup( _
   selectedVal, Worksheets("Look up Table").Range("picklist"), 2, False)

 If Not IsError(selectednum) Then
   Target.Value = selectednum
 End If
 
ExitProc:
 On Error Resume Next
 Application.EnableEvents = True
 If Len(sErrMsg) Then MsgBox sErrMsg
 Exit Sub

ErrProc:
 sErrMsg = Err.Number & ": " & Err.Description
 Resume ExitProc
End Sub

Another considerations with Worksheet_Change code is what to do if the user enters multiple values. The code above just exits with an error message you can customize. You might prefer to modify that to process each value changed in Column 7.

 

[Desperaotto]

Hi Jerry,

Thank you for your help it is much appreciated. I get what you are saying and it all makes sense. But when I modified the VBA with your updates I am getting an error stating "1004: Application-defined or object-defined error". I looked at other threads with the same error and gave some solutions they used a try like unlocking all the cells but it did not work. Any ideas?

 

[Jerry Sullivan]

Did you replace your previous code by copying and pasting my suggestion or did you modify yours with the differences?

If the code that gave the error isn't exactly what I posted, then please post the code that you were trying.

 

[Desperaotto]

I originally tried updating my own code just to go through and get a better grasp of VBA. When that did not work I deleted out my code and copied and pasted exactly what you provided. It caused the same error.

 

[Jerry Sullivan]

Let's to do some debugging on your file since this works on my mockup.

First, place a single quote in front of this statement so the code can be run without the error handling.

' On Error GoTo ErrProc

Try the code again. When it errors, post which line of code is highlighted when it stops.

Note, you'll need to reset EnableEvents if the code ends without reaching that statement. To do that you can paste this into the Immediate Window of the VB Editor, then hit enter.

 Application.EnableEvents = True

 

[Desperaotto]

Hi Jerry,

Thank so much for help. I did the debugger and I figured out the error. I had an outdated reference with the "picklist" because I had made a dynamic table and changed the drop down list reference. It work perfectly now. Thanks!

 

[Jerry Sullivan]

Glad to hear that. Thanks for the feedback.