autorun macro with vlookup function

[STEEL010]

Hi There,

I got a little held up on my work to autorun an vlookup function in vba.
I want to lookup a value that I created and auto populate in a cell
this is the code but it will not work, please help!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "$B$5" Then
Call lookup
End If
End Sub

Sub lookup()
On Error Resume Next
Range("B6").Value = "=VLookup(B5,pt_NL, 7, 0)"
Range("B6") = Range("B6")
End Sub

Greetings Steel010

 

[STEEL010]

Re: autorun macro with vlooup funtion

Because I do not want that the users see the code when they are editing the cell for a reason

 

[AlphaFrog]

Re: autorun macro with vlooup funtion

If this is an ActiveX-type combobox, put this in the worksheet's code module. Change the name of Combobox1 to the name of your combobox.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] [B]ComboBox1[/B]_Change()
    [color=darkblue]If[/color] Range("B5").Value <> "" [color=darkblue]Then[/color]
        Range("B6").Value = Evaluate("=VLookup(B5,pt_NL, 7, 0)")
    [color=darkblue]Else[/color]
        Range("B6").ClearContents
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[STEEL010]

Re: autorun macro with vlooup funtion

YES!!!! it works, thank a lot. you helped me so much. thank you also for your patience.

have a good night

greeting

Steel010