Two criterias with msgbox i would like to achieve when using Application.Vlookup in Worksheet_change

[SamDsouza]

Hello

I would like to achive two criterias with VBA code

Crietria 1
1. Msgbox "Pl Enter in Col B4:B20" if someone enters in range other than range B4:B20

Criteria 2
in range from B4 to D20
2.
Another msgbox to be displayed if someone entered in range from B4 to D20
For eg
in col C which is Target.Offset of Col B as seen below in code. Msgbox "You cant enter in this range Only for Vlook up values"
so if some enters from range B4 to D20 then msg

How can i Achieve the above with below code ?

myLookupValue = Target.Value

If Target.Count = 1 Then
  If Not Intersect(Range("B4:B20"), Target) Is Nothing Then
     On Error Resume Next
     Set myTableArray = wsMF.Range("B4:F840")
         myVLookupCode = Application.WorksheetFunction.VLookup(myLookupValue, myTableArray, CodeColndx, False)
         myVLookupRate = Application.WorksheetFunction.VLookup(myLookupValue, myTableArray, RateColndx, False)
     On Error GoTo 0
     If myVLookupCode <> "" Then   '
             Target.Offset(, 1).Value = myVLookupCode
             Target.Offset(, 2).Value = myVLookupRate
        Else
             MsgBox myLookupValue & "  Prod.code Does Not Exists " & vbCrLf & "Please Enter Correct Prod.code"
       End If
    End If
End If

SamD
120

 

[NimishK]

SamD

You need to add as many If Not Intersect...... Is Nothing Statements as per your requirement.

FYI
your Range defined in statement
If Not Intersect(Range("B4:B20"), Target) Is Nothing Then
in above statement Event will trigger vlookUp in range B4:B20

likewise
if you want other things to trigger you need to have your appropriate range defined like below
the below codes whenever you type in col A E F and G msgbox will appear

If Target.Count = 1 Then
  If Not Intersect(Target, Range("A:A, E:E, F:F, G:G")) Is Nothing Then
     On Error Resume Next
             MsgBox "Your Range For Code LookUp is from B4:B20"
  End If
End If

NimishK

 

[NimishK]

One uses Change event to check whether Cell value has changed
and Selection Change event is fired as you move in the worksheet
Would request you to kindly place the code in Worksheet_SelectionChange

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Target, Range("C4:D20")) Is Nothing Then
             Cells(Target.Row, Target.Column).Offset(0, 2).Select
  End If
End Sub

HTH
NimishK

 

[SamDsouza]

NimishK

Thank you so much for the wonderful help. As this was first time using Ws_change event. I observed some errors happening in this event.

Yes thanks for the suggestion WS_Sel Change event. A Perfect guidance. I 've achived what i wanted

Thank you

SamD ?
121