Msgbox YesNo advice

[ipbr21054]

Eventing.

I have the code show below.

I need some some advice please for the correct code so when Yes is selected I am taken to a specific folder.
I can’t advise the path at present but stuck with the main code for when Yes is selected.

[COLOR=#000000]Private Sub Worksheet_Change(ByVal Target As Range)Dim shp As Shape[/COLOR]If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
If Target.Row Mod 20 = 0 Then Exit Sub
On Error GoTo son


For Each shp In ActiveSheet.Shapes
If shp.Type = msoPicture And shp.TopLeftCell.Address = Target.Offset(0, 1).Address Then shp.Delete
Next


If Target.Value <> "" And Dir("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\" & "\" & Target.Value & ".jpg") = "" Then
        'picture not there!
         MsgBox "Photo " & Target.Value & " Doesn't exist" & vbCrLf & "Open The Picture Folder ?", vbCritical + vbYesNo, "No Photo Found"
         Exit Sub
End If


ActiveSheet.Pictures.Insert("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\" & "\" & Target.Value & ".jpg").Select
Selection.Top = Target.Offset(0, 1).Top + 5
Selection.Left = Target.Offset(0, 1).Left + 5


With Selection.ShapeRange
.LockAspectRatio = msoFalse
.Height = Target.Offset(0, 1).Height - 10
.Width = Target.Offset(0, 1).Width - 10
End With
Target.Offset(1, 0).Select
son:

 [COLOR=#000000]End Sub[/COLOR]

 

[mumps]

Does this help?

If MsgBox("Your message here.", vbYesNo) = vbYes Then
    'do something
End If

 

[ipbr21054]

Hi,
Thanks for the reply.

I have this now in place but get a syntax error.

Private Sub Worksheet_Change(ByVal Target As Range)Dim shp As Shape
If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
If Target.Row Mod 20 = 0 Then Exit Sub
On Error GoTo son


For Each shp In ActiveSheet.Shapes
If shp.Type = msoPicture And shp.TopLeftCell.Address = Target.Offset(0, 1).Address Then shp.Delete
Next


If Target.Value <> "" And Dir("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\" & Target.Value & ".jpg") = "" Then
        'picture not there!
         MsgBox "Photo " & Target.Value & " Doesn't exist" & vbCrLf & "Open The Picture Folder ?", vbCritical + vbYesNo, "No Photo Found" =vbYes Then
         CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\")
End If


ActiveSheet.Pictures.Insert("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\" & "\" & Target.Value & ".jpg").Select
Selection.Top = Target.Offset(0, 1).Top + 5
Selection.Left = Target.Offset(0, 1).Left + 5


With Selection.ShapeRange
.LockAspectRatio = msoFalse
.Height = Target.Offset(0, 1).Height - 10
.Width = Target.Offset(0, 1).Width - 10
End With
Target.Offset(1, 0).Select
son:


End Sub

 

[mumps]

If Target.Value <> "" And Dir("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\" & Target.Value & ".jpg") = "" Then 'picture not there!
    If MsgBox("Photo " & Target.Value & " Doesn't exist" & vbCrLf & "Open The Picture Folder ?", vbCritical + vbYesNo, "No Photo Found") = vbYes Then
        CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\")
    End If
End If

 

[ipbr21054]

Great
Thanks very much

 

[mumps]

You are very welcome.

 

[ipbr21054]

Hi,
Code in use shown below.
Everything works fine apart from when i click Yes on the message box.
Example
I click yes
I quickly see an error message
Now picture folder opens.
I close picture folder.
The error message i quickly saw is on the screen.
Run time error 1004,unable to get the insert property of the picture class.
Clicking debug shows this in yellow.
ActiveSheet.Pictures.Insert("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME" & Target.Value & ".jpg").Select

The above should not have triggered etc as i selected Yes to show me picture folder but the code continues on its next step & this message had popped up.
We know it wont find it as in the previous part of the code told us,hence the message box.

Private Sub Worksheet_Change(ByVal Target As Range)Dim shp As Shape
If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
If Target.Row Mod 20 = 0 Then Exit Sub
On Error GoTo son


For Each shp In ActiveSheet.Shapes
If shp.Type = msoPicture And shp.TopLeftCell.Address = Target.Offset(0, 1).Address Then shp.Delete
Next


If Target.Value <> "" And Dir("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\" & Target.Value & ".jpg") = "" Then 'picture not there!
    If MsgBox("Photo " & Target.Value & " Doesn't exist" & vbCrLf & "Open The Picture Folder ?", vbCritical + vbYesNo, "No Photo Found") = vbYes Then
        CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\")
        Else
        Exit Sub
    End If
End If
ActiveSheet.Pictures.Insert("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\" & Target.Value & ".jpg").Select
Selection.Top = Target.Offset(0, 1).Top + 5
Selection.Left = Target.Offset(0, 1).Left + 5


With Selection.ShapeRange
.LockAspectRatio = msoFalse
.Height = Target.Offset(0, 1).Height - 10
.Width = Target.Offset(0, 1).Width - 10
End With
Target.Offset(1, 0).Select
son:


End Sub

 

[mumps]

When I try the macro, if I click "No" when asked to show me the picture folder, nothing happens and no error comes up. If I click "Yes", the picture folder is displayed and again there is no error. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[ipbr21054]

Here is the file for downloading.
Selecting No on the msgbox is fine.

If you select yes you see the error message then the picture folder opens.
Close the picture folder and the error message is there .

https://drive.google.com/open?id=1WztiNiY6pUxvkmQjk7veBvRVQEdGn7a3

 

[mumps]

I've tried to interpret what you want to do and have made a few changes to your code. It works without any error messages for me. Give it a try.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim shp As Shape
    If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
    If Target.Row Mod 20 = 0 Then Exit Sub
    On Error GoTo son
    
    If Target.Value <> "" And Dir("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\" & Target.Value & ".jpg") = "" Then 'picture not there!
        If MsgBox("Photo " & Target.Value & " Doesn't exist" & vbCrLf & "Open The Picture Folder ?", vbCritical + vbYesNo, "No Photo Found") = vbYes Then
            Target.ClearContents
            CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\")
        Else
            Target.ClearContents
            Application.EnableEvents = True
            Exit Sub
        End If
    End If
    For Each shp In ActiveSheet.Shapes
        If shp.TopLeftCell.Address = Target.Offset(0, 1).Address Then shp.Delete
    Next
    ActiveSheet.Pictures.Insert("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\" & Target.Value & ".jpg").Select
    Selection.Top = Target.Offset(0, 1).Top + 5
    Selection.Left = Target.Offset(0, 1).Left + 5
    
    With Selection.ShapeRange
    .LockAspectRatio = msoFalse
    .Height = Target.Offset(0, 1).Height - 10
    .Width = Target.Offset(0, 1).Width - 10
    End With
    Target.Offset(1, 0).Select
son:
    Application.EnableEvents = True
End Sub