Gruppe
Ereignis
Problem
Wie kann ich mir beim Eintragen einer Artikelnummer die Daten dieses Artikels aus einer geschlossenen Arbeitsmappe in einer MsgBox anzeigen lassen?
ClassModule: Tabelle12
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim sPath As String, sFile As String, sWks As String
Dim sFormula As String, sFormulaA As String, sFormulaB As String
If Target.Column <> 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
If Target.Value > 20 Then Exit Sub
sPath = ThisWorkbook.Path
sFile = "Test1.xls"
If Dir(sPath & "\" & sFile) = "" Then
Beep
MsgBox "Testdatei Test1.xls wurde nicht gefunden!"
Exit Sub
End If
sWks = "048199"
sFormula = "VLOOKUP(A" & Target.Row & ","
sFormula = sFormula & "'" & sPath & "\[" & sFile & "]" & sWks & "'!"
sFormulaA = sFormula & "A:C,2,0)"
sFormulaB = sFormula & "A:C,3,0)"
Application.EnableEvents = False
On Error GoTo ERRORHANDLER
Target.Offset(0, 1).Formula = "=" & sFormulaA
Target.Offset(0, 2).Formula = "=" & sFormulaB
With Range(Target.Offset(0, 1), Target.Offset(0, 2))
.Value = .Value
End With
ERRORHANDLER:
Application.EnableEvents = True
End Sub