Msgbox advice please

[ipbr21054]

Hi,
Below is a section of my working code.
The code in red WAS vbInformation but now it is vbYesNo.

If i select YES it then hyperlinks the photo.

If i select NO it also hyperlinks the photo.

Can you advise how i just close down the msgbox if NO is selected.

err:
 Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\"
    If ActiveCell.Column = Columns("B").Column Then
 
    If Len(Dir(FILE_PATH & ActiveCell.Value & ".jpg")) Then
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".jpg"
        MsgBox "CUSTOMER PHOTO HYPERLINK WAS SUCCESSFUL.", vbInformation, "POSTAGE SHEET HYPERLINK MESSAGE"
        End If
        
        Else
        MsgBox "PLEASE SELECT A CUSTOMER FIRST TO HYPERLINK THE PHOTO.", vbCritical, "POSTAGE SHEET HYPERLINK MESSAGE"
        Exit Sub
        End If
        
        If Dir(FILE_PATH & ActiveCell.Value & ".jpg") = "" Then
        If MsgBox("THERE IS NO PHOTO TO HYPERLINK FOR THIS CUSTOMER" & vbCrLf & vbCrLf & _
        "WOULD YOU LIKE TO OPEN THE PHOTO FOLDER ?" & vbCrLf & vbCrLf & _
        "YES = OPEN THE PHOTO FOLDER" & vbCrLf & vbCrLf & _
        "NO = HYPERLINK IS NOT REQUIRED", vbYesNo + vbCritical, "HYPERLINK CUSTOMER MISSING PHOTO MESSAGE.") = vbYes Then
        
        CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\")
        MsgBox "CONTINUE TO NOW HYPERLINK CUSTOMER & PHOTO ?", vbYesNo, "HYPERLINK PHOTO MESSAGE"
        
        GoTo err
        End If

     
    End If
    

End With
    
TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")

 

[Logit]

This is one method :

Option Explicit

Sub Test3()
Dim answer As Variant
  answer = MsgBox("YES = OPEN THE PHOTO FOLDER; NO = HYPERLINK IS NOT REQUIRED", vbYesNo + vbQuestion, "Choose macro to run")
  If answer = vbYes Then
      Call Test1
   Else
     Call Test2
End If
End Sub

Sub Test1()
   Range("A1").Value = "A"
End Sub

Sub Test2()
   Range("A2").Value = "B"
End Sub

 

[GWteB]

Try this

        Dim vbResult As VbMsgBoxResult         ' <<<<<<<<<<

        vbResult = MsgBox("THERE IS NO PHOTO TO HYPERLINK FOR THIS CUSTOMER" & vbCrLf & vbCrLf & _
                        "WOULD YOU LIKE TO OPEN THE PHOTO FOLDER ?" & vbCrLf & vbCrLf & _
                        "YES = OPEN THE PHOTO FOLDER" & vbCrLf & vbCrLf & _
                        "NO = HYPERLINK IS NOT REQUIRED", vbYesNo + vbCritical, "HYPERLINK CUSTOMER MISSING PHOTO MESSAGE.")
        If vbResult = vbYes Then
            CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\")
            MsgBox "CONTINUE TO NOW HYPERLINK CUSTOMER & PHOTO ?", vbYesNo, "HYPERLINK PHOTO MESSAGE"
            GoTo Err
        End If

 

[ipbr21054]

Try this

        Dim vbResult As VbMsgBoxResult         ' <<<<<<<<<<

        vbResult = MsgBox("THERE IS NO PHOTO TO HYPERLINK FOR THIS CUSTOMER" & vbCrLf & vbCrLf & _
                        "WOULD YOU LIKE TO OPEN THE PHOTO FOLDER ?" & vbCrLf & vbCrLf & _
                        "YES = OPEN THE PHOTO FOLDER" & vbCrLf & vbCrLf & _
                        "NO = HYPERLINK IS NOT REQUIRED", vbYesNo + vbCritical, "HYPERLINK CUSTOMER MISSING PHOTO MESSAGE.")
        If vbResult = vbYes Then
            CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\")
            MsgBox "CONTINUE TO NOW HYPERLINK CUSTOMER & PHOTO ?", vbYesNo, "HYPERLINK PHOTO MESSAGE"
            GoTo Err
        End If

When i select YES to open the folder i then do what i need to do and close the folder.
I then see the hyperlink question Yes NO, selecting Yes just closes then shows me the same message again

 

[GWteB]

Omit the second message box, one confirmation will do, won't it?

err:
    Dim vbResult As VbMsgBoxResult
    Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\"
    
    If ActiveCell.Column = Columns("B").Column Then
 
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".jpg")) Then
            ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".jpg"
            MsgBox "CUSTOMER PHOTO HYPERLINK WAS SUCCESSFUL.", vbInformation, "POSTAGE SHEET HYPERLINK MESSAGE"
            End If
        Else
            MsgBox "PLEASE SELECT A CUSTOMER FIRST TO HYPERLINK THE PHOTO.", vbCritical, "POSTAGE SHEET HYPERLINK MESSAGE"
            Exit Sub
        End If
        
        vbResult = MsgBox("THERE IS NO PHOTO TO HYPERLINK FOR THIS CUSTOMER" & vbCrLf & vbCrLf & _
                        "WOULD YOU LIKE TO OPEN THE PHOTO FOLDER ?" & vbCrLf & vbCrLf & _
                        "YES = OPEN THE PHOTO FOLDER" & vbCrLf & vbCrLf & _
                        "NO = HYPERLINK IS NOT REQUIRED", vbYesNo + vbCritical, "HYPERLINK CUSTOMER MISSING PHOTO MESSAGE.")
        If vbResult = vbYes Then
            CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\")
'            MsgBox "CONTINUE TO NOW HYPERLINK CUSTOMER & PHOTO ?", vbYesNo, "HYPERLINK PHOTO MESSAGE"
            GoTo err
        End If
    End If
End With
    
TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")

 

[rlv01]

What about something like this?

    If Dir(FILE_PATH & ActiveCell.Value & ".jpg") = "" Then
        Select Case MsgBox("THERE IS NO PHOTO TO HYPERLINK FOR THIS CUSTOMER " & vbCrLf & vbCrLf _
                         & "WOULD YOU LIKE TO OPEN THE PHOTO FOLDER ? " & vbCrLf & vbCrLf _
                         & "YES = OPEN THE PHOTO FOLDER " & vbCrLf & vbCrLf _
                         & "NO = HYPERLINK IS NOT REQUIRED", vbYesNo Or vbCritical, "HYPERLINK CUSTOMER MISSING PHOTO MESSAGE")

        Case vbYes                                    'if yes, open the photo folder
            CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\")

            'Ask user about going back to hyperlink creation
            Select Case MsgBox("CONTINUE TO NOW HYPERLINK CUSTOMER & PHOTO ?", vbYesNo Or vbQuestion, Application.Name)

            Case vbYes                                'if yes, go back and continue to hyperlink
                GoTo err
            End Select
        End Select
    End If

 

[ipbr21054]

What about something like this?

    If Dir(FILE_PATH & ActiveCell.Value & ".jpg") = "" Then
        Select Case MsgBox("THERE IS NO PHOTO TO HYPERLINK FOR THIS CUSTOMER " & vbCrLf & vbCrLf _
                         & "WOULD YOU LIKE TO OPEN THE PHOTO FOLDER ? " & vbCrLf & vbCrLf _
                         & "YES = OPEN THE PHOTO FOLDER " & vbCrLf & vbCrLf _
                         & "NO = HYPERLINK IS NOT REQUIRED", vbYesNo Or vbCritical, "HYPERLINK CUSTOMER MISSING PHOTO MESSAGE")

        Case vbYes                                    'if yes, open the photo folder
            CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\")

            'Ask user about going back to hyperlink creation
            Select Case MsgBox("CONTINUE TO NOW HYPERLINK CUSTOMER & PHOTO ?", vbYesNo Or vbQuestion, Application.Name)

            Case vbYes                                'if yes, go back and continue to hyperlink
                GoTo err
            End Select
        End Select
    End If

This worked as i would like it to apart from,

When i select NO for the msgbox question shown below the customers name is turned blue & underlined "as if it was hyperlinked"
Obviously clicking this name does nothing as there is nothing required to hyperlink,so why is it blue & underlined because NO was selected ?

        Select Case MsgBox("THERE IS NO PHOTO TO HYPERLINK FOR THIS CUSTOMER " & vbCrLf & vbCrLf _
                         & "WOULD YOU LIKE TO OPEN THE PHOTO FOLDER ? " & vbCrLf & vbCrLf _
                         & "YES = OPEN THE PHOTO FOLDER " & vbCrLf & vbCrLf _
                         & "NO = HYPERLINK IS NOT REQUIRED", vbYesNo Or vbCritical, "HYPERLINK CUSTOMER MISSING PHOTO MESSAGE")

 

[rlv01]

This worked as i would like it to apart from, When i select NO for the msgbox question shown below the customers name is turned blue & underlined "as if it was hyperlinked"
Obviously clicking this name does nothing as there is nothing required to hyperlink,so why is it blue & underlined because NO was selected ?

I think that behavior is not related to the msgbox code. As so often happens in coding, fixing one problem reveals another. When you select NO, the next statement executed (per your posted code) should be:

TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy") '<----- here

So whatever is happening to cause this new problem is happening either before or after the msgbox code. My guess would be it is a badly formed hyperlink you have somehow created elsewhere in code.

 

[ipbr21054]

Many thanks i will take a better lock and test it out