Msgbox advice please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,923
Office Version
  1. 2007
Platform
  1. Windows
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.




Rich (BB code):
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")
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,998
This is one method :

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Try this
VBA Code:
        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

Well-known Member
Joined
Nov 16, 2010
Messages
3,923
Office Version
  1. 2007
Platform
  1. Windows
Try this
VBA Code:
        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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Omit the second message box, one confirmation will do, won't it?
VBA Code:
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

Well-known Member
Joined
May 16, 2017
Messages
771
What about something like this?
VBA Code:
    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

Well-known Member
Joined
Nov 16, 2010
Messages
3,923
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

What about something like this?
VBA Code:
    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 ?

VBA Code:
        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

Well-known Member
Joined
May 16, 2017
Messages
771
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:
VBA Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,923
Office Version
  1. 2007
Platform
  1. Windows
Many thanks i will take a better lock and test it out
 

Watch MrExcel Video

Forum statistics

Threads
1,129,370
Messages
5,635,864
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top