[VBA] Check if a cell content has a match in a folder and if yes, open file

[Eawyne]

Hi all =)

Here's what I need :

=> I enter a value in a cell (usually, a number, like 141KL540024)
=> There's an autocheck in a folder to determine if there exists a corresponding Powerpoint file (it would be 141KL540024.pptx)
=> If the file exists, a message box will warn us, and ask if we want to open said file : if yes it opens it ; if no, nothing happens

I'm a relative newbie in terms of VBA ; I've already tinkered a few things so far, and I've managed to locate some old codes that were previously used in another file, but in that case, the code pointed to a sheet in the same workbook.

Sub Affichagedérogation()
Application.ScreenUpdating = False
On Error GoTo ErrorHandler

Dim masque As Variant
' copie la cellule sur elle même pour que l'auto recipe checker fonctionne
    ActiveCell.Offset(0, 0).Select
    'ActiveCell.Select
    Selection.Copy
    ActiveCell.Select
    ActiveSheet.Paste

masque = ActiveCell.Value
 
 Sheets("Liste").Select
    Cells.Find(What:=masque, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Select
   MsgBox ("Il y a une fiche de dérogation pour ce masque.")
Selection.Hyperlinks(1).Follow
Sheets("LSTAR03").Select

ErrorHandler:
Windows("STARLights3 2018.xlsm").Activate
Sheets("LSTAR03").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I've tried to come up with something, using the DIR variable, but here's where my limited knowledge comes to a halt. I can't manage to assign a "value" that I can use throughout the code to be referenced without having to name the value to a corresponding file in my folder ; the value should always be what's in the cell ; it appears the "masque = ActiveCell.Value" command does just that, but...

Thanks for any help !

 

[Eawyne]

Hi =)

sorry for not chiming up earlier, but I was on vacation, and had no possibilities to do further tests.

I've actually narrowed the problem down : it happens when you select multiple rows/columns and delete their content. This error happens :

Run-time error : '13':
Type mismatch

And this is the line that gets selected in the code :

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

I tried to relocate that line in the code under the Intersect check, but it didn't change anything.

Previously, I also changed this line :

If Not Intersect(Target, Range("A:A")) Is Nothing And Target.Row >= 2 Then

into

If Target.Column = 2 Then

to find out if it helped go around that error, but it still happens, as it's not the source of the conflict, apparently.

What's interesting is that having this :

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

       If Target.Column = 2 Then
       
        fileName = Dir(searchFolder & "*" & Target.Value & "*.ppt*")

Creates the very same error if I try to expand cells anywhere in the sheeet ; but this :

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

       If Target.Column = 2 Then
     
        fileName = Dir(searchFolder & "*" & Target.Value & "*.ppt*")

doesn't... it only happens in the second column.

I'm at a loss here ! I hope this has all been clear enough...

 

[Eawyne]

As an example :

if I delete, or expand, the contents of all the cells in the screenshot, nothing will happen. If I select all but the first column, it bugs. So it appears that if the second column is the first one to be in a selection, or the only one, it creates some kind of conflict.

 

[John_w]

I've actually narrowed the problem down : it happens when you select multiple rows/columns and delete their content. This error happens :

Run-time error : '13':
Type mismatch

And this is the line that gets selected in the code :

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

I tried to relocate that line in the code under the Intersect check, but it didn't change anything.

Putting each part of the compound If on separate lines usually works:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim searchFolder As String, fileName As String
    Static PowerPointApp As Object
    
    searchFolder = "C:\path\to\folder\"
    
    If Right(searchFolder, 1) <> "\" Then searchFolder = searchFolder & "\"
    
    If Not Intersect(Target, Range("A:A")) Is Nothing And Target.Row >= 2 Then
        If Target.CountLarge > 1 Then Exit Sub
        If Target.Value = "" Then Exit Sub
        fileName = Dir(searchFolder & "*" & Target.Value & "*.pptx")
        If fileName <> vbNullString Then
            If MsgBox(fileName & " exists.  Do you want to open it?", vbYesNo + vbInformation, "Open PowerPoint file?") = 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

That's based on my previous code and doesn't account for any changes you've made since.

 

[Eawyne]

Yes, it worked ! How simple, I should've thought about it I guess... Anyway, amazing help from you. It helped me tremendously, even on the code-grasping aspect =)

Though I have to admit, I don't really get why putting this on two lines doesn't trigger the bug ^^' So maybe I couldn't have thought about it regardless.

 

[John_w]

Though I have to admit, I don't really get why putting this on two lines doesn't trigger the bug ^^' So

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

VBA always evaluates all parts of a compound If statement, even if the first part is True. When Target is multiple cells, Target.Value is not a single value, but a 2-dimensional array of values, therefore comparing that array to a string ("") , fails with the error "Type mismatch" because the data types being compared are different.

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

With the 2 checks separated, VBA doesn't execute the second check when Target is multiple cells, therefore the error doesn't occur.

 

[Eawyne]

Cool. It sounds quite logical once explained.

Actually, it's the kind of detailed information I always find hard to find. It's either buried deep in thick layers of hard to digest articles, or not explained "properly" (aka. for newbies) in simpler documentation...

So again, thanks for your time and dedication