Excel [VBA] Check from a cell into another sheet and bring back result in MsgBox

[EAWYNE]

Hi all,

so, here's a code I have running on a file we use at work ; what it does is go check into a folder whenever an entry is written in a cell from a specific Column : if there's a match, a Msgobx asks if we want to open the corresponding file.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim searchFolder As String, fileName As String
Static PowerPointApp As Object

searchFolder = "C:\Users\crolles300 oper\STMicroelectronics\C300 Lithography Module - Derogations STARLight"

If Right(searchFolder, 1) <> "\" Then searchFolder = searchFolder & "\"

If Target.Column = 2 Then
If Target.CountLarge > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

fileName = Dir(searchFolder & "*" & Target.Value & "*.ppt*")
If fileName <> vbNullString Then
If MsgBox(fileName & " existe. Voulez-vous l'ouvrir ?", vbYesNo + vbQuestion, "Fiche de dérogation") = vbYes Then
If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject("PowerPoint.Application")
PowerPointApp.Presentations.Open searchFolder & fileName
End If
End If
End If

End Sub

It required a lot of trial and error, and help, as my VBA/coding knowledge is fairly limited. But I need an aditional check running in the same cell from which the first macro ran :

=> check cell value into a Column from another Sheet of the same file
=> if there's a match, then a Msgbox should display the content of the Offset cell from the matching cell

I suppose I have to play around with the Target.Value & variable to get the proper message displayed in the MsgBox, but I'm just not sure. Ideally, both checks should run one after the other, without the boxes getting stuck - if that's even possible ? Or should there be two different macros altogether ?

As you can see, a lot of questions, a lot of fog =/ I googled many different queries to try and narrow down specific code I could use, but it's never exactly what I'm after - and despite my best efforts, I just can't manage to adapt what I find.

Thanks for any help you may provide !