VBA external VLOOKUP instead of VLOOKUP-formula in cell

Initial situation:

Workbook W1 with 1 sheet, WS1, containing the VBA-code
Workbook W2 with 1 sheet, WS2, containing names and corresponding key-values (ibutton).

The code I have written sofar relies on input from an Userform, containing a field named “Key” .
The value of key is a string of digits representing the scan of a magnetic key-button (ibutton) read by a scanner. This keyvalue will be used in a VLOOKUP formula in WS1, to find the corresponding name.

So far so good.

In WS1 I have the input of the key-scanner stored in column A, while the VLOOKUP-formula in column B will retreive the corresponding name from WS2. Working like a charm, even when W2 is not opened.

The change I am looking for:

Instead of having the VLOOKUP-formula in column B, I want to use VBA to find the corresponding name in WS2 and put the value in column B… so no formula in B, just the result of the VLOOKUP.
Preferably not having to open WS2.

W2 is also used for other VLOOKUPs in other sheets, so I want to keep this WS2 as standalone source; so not having to copy it to other locations to minimize the risk of having several versions of it.

Any help is much appreciated.

Hello sauerj,

Apologies for the delay in reply. Your informaton put me on the right track. Some tweaking and trimming done; also adjusted some other code which had a same issue. Works neat!
Thanx!!

Cool! ... Take care!