Macro From Excel 2013 Not Working in 2016

Good morning All,

I have fiddled with this for about an hour to no avail. I had the below code working flawlessly in Excel 2013 but with a recent upgrade to 2016 I can't get it to work.

Sub TakePhoto()
Dim FixtureDesignation As Range
'Fixture Designation of cell you want to take a picture of
Set FixtureDesignation = ActiveSheet.Buttons(Application.Caller).TopLeftCell
'Exit if there is no fixture designation
If FixtureDesignation = 0 Then
MsgBox ("A fixture designaiton is required.")
GoTo 10
End If
'Check if picture of fixture designation exists
Dim FilePath As String
Dim TestStr As String
FilePath = Application.ActiveWorkbook.Path & "\Photos"
TestStr = ""
On Error Resume Next
TestStr = Dir(FilePath & "\" & FixtureDesignation & ".bmp")
If TestStr = "" Then
    GoTo 20
Else: GoTo 30
30
    If MsgBox("A photo of this fixture designation already exists. Would you like to overwrite it and take a new photo?", vbYesNo) = vbNo Then Exit Sub
End If
20 'Fixture Designation does not exist or "yes" was answered to overwrite existing photo
 Dim RetVal
     
    ' Make sure the current directory is set to the one
    ' where the Excel file is saved
    ChDir (Application.ActiveWorkbook.Path & "\Photos")
     
    ' First, delete image file if present
    If Dir(FixtureDesignation & ".bmp") > "" Then
        Kill (FixtureDesignation & ".bmp")
    End If
     
    ' Now, wait until image file is definitely gone
    While Dir(FixtureDesignation & ".bmp") > ""
    Wend
     
    ' Capture new image
    RetValA = Shell("cd Photos")
    RetVal = Shell("CommandCam.exe /preview /delay 5000 /filename " & FixtureDesignation & ".bmp", vbHide)
     
    ' Wait until image file is definitely there
    While Dir(FixtureDesignation & ".bmp") = ""
    Wend
     
    ' Short delay to let new file finish saving
    Application.Wait (Now + TimeValue("00:00:01"))
     
    ' Load new image into image object on spreadsheet
    'Image1.Picture = LoadPicture(FixtureDesignation & ".bmp")
    
    'CLEANUP!
    
        'Set FilePath = Nothing
        'Set TestStr = Nothing
        'Set Dir = Nothing
        Set RetValA = Nothing
        Set RetVal = Nothing
    
10 End Sub

It appears everything goes as expected until I hit this line where I suspect the problem is and have searched for a fix and tested different variations;

    ' Capture new image
    RetValA = Shell("cd Photos")
    RetVal = Shell("CommandCam.exe /preview /delay 5000 /filename " & FixtureDesignation & ".bmp", vbHide)

Where it will hang on this line for a while until it finally freezes

    ' Wait until image file is definitely there
    While Dir(FixtureDesignation & ".bmp") = ""
    Wend

Any hints? Thanks for any help.

Put a loop counter ... get out of jail card ... in there. Increment it and test it for some arbitrary number. There's no way out of your While ... Wend loop until the file appears. Tough if it doesn't show.

Great thank you very much. That does get me out of the loop and prevent freezing.

For whatever reason the program is not firing though, do you see anything wrong with the syntax of the shell command I'm issuing?

I'll plug away at it more after work and report back.

I don't use the Shell command so I can't really comment. I don't have Excel 2013 installed, and I don't have anything to test. So, sadly, I'm not in a position to help or advise.

I would suggest that you put a Stop statement as the first command in the subroutine and then step through using F8. That will allow you to check the contents of variables, etc., at each point and, hopefully, narrow down the issue.

The other thing that might prove useful is to create simple test routines that just check whether or not the Shell commands succeeds in isolation.