MsgBox - vbNo does not exit sub

TheWaterDog

TheWaterDog

New Member
Joined
May 24, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Apologies if this thread exists - I've searched and not found a solution in any of the threads.

Can anyone tell me why my vbNo answer runs the same as vbYes - what have I missed? :( No error - just runs the sub the same as vbYes; I've tried six million ways of ending sub and nothing works.
Section in RED below

Explanation of sub:
Shapes on sheet "Image Map" are named as Ref (i.e NH.02.34)
Ref is inputted in Cell R2 on shape click
Cells R3:X12 use R2 to index/match data from another sheet (Schedule Data)
MsgBox displays data from Cells R3:X12
User Clicks Yes - Cells R3:X12 exported and saved as PDF
MsgBox display success with PDF name

But...
User Clicks No
...and sub does not exit...runs the same as Yes click


Rich (BB code): 
Sub Fetch_NH_02_34()
'Shape Located in Elevation (North 1) 0207 - Top Row
'Click to input Shape Reference into Cell R2 to allow index/match function in cells R3 to Z12 to call data from Schedule Sheet
        ActiveSheet.Shapes.Range(Array("NH.02.34")).Select
        Range("R2").Select
        ActiveCell.FormulaR1C1 = "NH.02.34"
'Keep the sheet from scrolling away from location
        Range("A20").Select
'Open Message Box and display data in cells R2 to Z12
        MsgBox "WINDOW " & Worksheets("Image Map").Range("r2").Value & vbNewLine & Worksheets("Image Map").Range("t3").Value & "mm width  | " & Worksheets("Image Map").Range("u3").Value & "mm height" & vbNewLine & vbNewLine & vbNewLine _
        & "A1 Schedule Ref: " & Worksheets("Image Map").Range("r3").Value & vbNewLine & Worksheets("Image Map").Range("s3").Value & " (" & Worksheets("Image Map").Range("w3").Value & ")" & vbNewLine & _
        Worksheets("Image Map").Range("x3").Value & " - " & Worksheets("Image Map").Range("y3").Value & " (" & Worksheets("Image Map").Range("z3").Value & ")" & vbNewLine & vbNewLine _
        & "A1 Schedule Ref: " & Worksheets("Image Map").Range("r4").Value & vbNewLine & Worksheets("Image Map").Range("s4").Value & " (" & Worksheets("Image Map").Range("w4").Value & ")" & vbNewLine & _
        Worksheets("Image Map").Range("x4").Value & " - " & Worksheets("Image Map").Range("y4").Value & " (" & Worksheets("Image Map").Range("z4").Value & ")" & vbNewLine & vbNewLine _
        & "A1 Schedule Ref: " & Worksheets("Image Map").Range("r5").Value & vbNewLine & Worksheets("Image Map").Range("s5").Value & " (" & Worksheets("Image Map").Range("w5").Value & ")" & vbNewLine & _
        Worksheets("Image Map").Range("x5").Value & " - " & Worksheets("Image Map").Range("y5").Value & " (" & Worksheets("Image Map").Range("z5").Value & ")" & vbNewLine & vbNewLine _
        & "A1 Schedule Ref: " & Worksheets("Image Map").Range("r6").Value & vbNewLine & Worksheets("Image Map").Range("s6").Value & " (" & Worksheets("Image Map").Range("w6").Value & ")" & vbNewLine & _
        Worksheets("Image Map").Range("x6").Value & " - " & Worksheets("Image Map").Range("y6").Value & " (" & Worksheets("Image Map").Range("z6").Value & ")" & vbNewLine & vbNewLine _
        & "A1 Schedule Ref: " & Worksheets("Image Map").Range("r7").Value & vbNewLine & Worksheets("Image Map").Range("s7").Value & " (" & Worksheets("Image Map").Range("w7").Value & ")" & vbNewLine & _
        Worksheets("Image Map").Range("x7").Value & " - " & Worksheets("Image Map").Range("y7").Value & " (" & Worksheets("Image Map").Range("z7").Value & ")" & vbNewLine & vbNewLine _
        & "A1 Schedule Ref: " & Worksheets("Image Map").Range("r8").Value & vbNewLine & Worksheets("Image Map").Range("s8").Value & " (" & Worksheets("Image Map").Range("w8").Value & ")" & vbNewLine & _
        Worksheets("Image Map").Range("x8").Value & " - " & Worksheets("Image Map").Range("y8").Value & " (" & Worksheets("Image Map").Range("z8").Value & ")" & vbNewLine & vbNewLine _
        & "A1 Schedule Ref: " & Worksheets("Image Map").Range("r9").Value & vbNewLine & Worksheets("Image Map").Range("s9").Value & " (" & Worksheets("Image Map").Range("w9").Value & ")" & vbNewLine & _
        Worksheets("Image Map").Range("x9").Value & " - " & Worksheets("Image Map").Range("y9").Value & " (" & Worksheets("Image Map").Range("z9").Value & ")" & vbNewLine & vbNewLine _
        & "A1 Schedule Ref: " & Worksheets("Image Map").Range("r10").Value & vbNewLine & Worksheets("Image Map").Range("s10").Value & " (" & Worksheets("Image Map").Range("w10").Value & ")" & vbNewLine & _
        Worksheets("Image Map").Range("x10").Value & " - " & Worksheets("Image Map").Range("y10").Value & " (" & Worksheets("Image Map").Range("z10").Value & ")" & vbNewLine & vbNewLine _
        & "A1 Schedule Ref: " & Worksheets("Image Map").Range("r11").Value & vbNewLine & Worksheets("Image Map").Range("s11").Value & " (" & Worksheets("Image Map").Range("w11").Value & ")" & vbNewLine & _
        Worksheets("Image Map").Range("x11").Value & " - " & Worksheets("Image Map").Range("y11").Value & " (" & Worksheets("Image Map").Range("z11").Value & ")" & vbNewLine & vbNewLine _
        & "A1 Schedule Ref: " & Worksheets("Image Map").Range("r12").Value & vbNewLine & Worksheets("Image Map").Range("s12").Value & " (" & Worksheets("Image Map").Range("w12").Value & ")" & vbNewLine & _
        Worksheets("Image Map").Range("x12").Value & " - " & Worksheets("Image Map").Range("y12").Value & " (" & Worksheets("Image Map").Range("z12").Value & ")" & vbNewLine & vbNewLine _
        & vbNewLine & "Tap 'YES' to create an index card for this data or 'NO' to exit.", vbYesNo
'User answers Yes
        If vbYes Then
'Create PDF from Cells Q1 to AC12
'Save in Index Card Folder with filename as Shape Reference
        ActiveSheet.Range("Q1:AC12").ExportAsFixedFormat Type:=0, _
        filename:="C:\Users\FRC\Desktop\IE85\A1 Index Cards\" & ActiveSheet.Range("R2").Value & " - " & Format(Now(), "YYYY-MM-DD hhmm"), _
        Quality:=0, IncludeDocProperties:=False, IgnorePrintAreas:=False, _
        openafterpublish:=False
'Display confirmation of Save
        MsgBox "Saved as " & Worksheets("Image Map").Range("r2").Value & " - " & Format(Now(), "YYYY-MM-DD hhmm")
        End If
'User answers No or X
        If vbNo Then
        Exit Sub
        End If
        End Sub
 
Last edited by a moderator:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Hi TheWaterDog,

you would need a variable to hold the answer to the msgbox like

VBA Code: 
Sub Fetch_NH_02_34()
Dim lngRet As Long
'Shape Located in Elevation (North 1) 0207 - Top Row
'Click to input Shape Reference into Cell R2 to allow index/match function in cells R3 to Z12 to call data from Schedule Sheet
ActiveSheet.Shapes.Range(Array("NH.02.34")).Select
Range("R2").FormulaR1C1 = "NH.02.34"
'Keep the sheet from scrolling away from location
Range("A20").Select
'Open Message Box and display data in cells R2 to Z12
With Worksheets("Image Map")
  lngRet = MsgBox("WINDOW " & .Range("r2").Value & vbNewLine & .Range("t3").Value & "mm width | " & .Range("u3").Value & "mm height" & vbNewLine & vbNewLine & vbNewLine _
      & "A1 Schedule Ref: " & .Range("r3").Value & vbNewLine & .Range("s3").Value & " (" & .Range("w3").Value & ")" & vbNewLine & _
      .Range("x3").Value & " - " & .Range("y3").Value & " (" & .Range("z3").Value & ")" & vbNewLine & vbNewLine _
      & "A1 Schedule Ref: " & .Range("r4").Value & vbNewLine & .Range("s4").Value & " (" & .Range("w4").Value & ")" & vbNewLine & _
      .Range("x4").Value & " - " & .Range("y4").Value & " (" & .Range("z4").Value & ")" & vbNewLine & vbNewLine _
      & "A1 Schedule Ref: " & .Range("r5").Value & vbNewLine & .Range("s5").Value & " (" & .Range("w5").Value & ")" & vbNewLine & _
      .Range("x5").Value & " - " & .Range("y5").Value & " (" & .Range("z5").Value & ")" & vbNewLine & vbNewLine _
      & "A1 Schedule Ref: " & .Range("r6").Value & vbNewLine & .Range("s6").Value & " (" & .Range("w6").Value & ")" & vbNewLine & _
      .Range("x6").Value & " - " & .Range("y6").Value & " (" & .Range("z6").Value & ")" & vbNewLine & vbNewLine _
      & "A1 Schedule Ref: " & .Range("r7").Value & vbNewLine & .Range("s7").Value & " (" & .Range("w7").Value & ")" & vbNewLine & _
      .Range("x7").Value & " - " & .Range("y7").Value & " (" & .Range("z7").Value & ")" & vbNewLine & vbNewLine _
      & "A1 Schedule Ref: " & .Range("r8").Value & vbNewLine & .Range("s8").Value & " (" & .Range("w8").Value & ")" & vbNewLine & _
      .Range("x8").Value & " - " & .Range("y8").Value & " (" & .Range("z8").Value & ")" & vbNewLine & vbNewLine _
      & "A1 Schedule Ref: " & .Range("r9").Value & vbNewLine & .Range("s9").Value & " (" & .Range("w9").Value & ")" & vbNewLine & _
      .Range("x9").Value & " - " & .Range("y9").Value & " (" & .Range("z9").Value & ")" & vbNewLine & vbNewLine _
      & "A1 Schedule Ref: " & .Range("r10").Value & vbNewLine & .Range("s10").Value & " (" & .Range("w10").Value & ")" & vbNewLine & _
      .Range("x10").Value & " - " & .Range("y10").Value & " (" & .Range("z10").Value & ")" & vbNewLine & vbNewLine _
      & "A1 Schedule Ref: " & .Range("r11").Value & vbNewLine & .Range("s11").Value & " (" & .Range("w11").Value & ")" & vbNewLine & _
      .Range("x11").Value & " - " & .Range("y11").Value & " (" & .Range("z11").Value & ")" & vbNewLine & vbNewLine _
      & "A1 Schedule Ref: " & .Range("r12").Value & vbNewLine & .Range("s12").Value & " (" & .Range("w12").Value & ")" & vbNewLine & _
      .Range("x12").Value & " - " & .Range("y12").Value & " (" & .Range("z12").Value & ")" & vbNewLine & vbNewLine _
      & vbNewLine & "Tap 'YES' to create an index card for this data or 'NO' to exit.", vbYesNo)
End With
'User answers Yes
If lngRet = vbYes Then
  'Create PDF from Cells Q1 to AC12
  'Save in Index Card Folder with filename as Shape Reference
  ActiveSheet.Range("Q1:AC12").ExportAsFixedFormat Type:=0, _
      Filename:="C:\Users\FRC\Desktop\IE85\A1 Index Cards\" & ActiveSheet.Range("R2").Value & " - " & Format(Now(), "YYYY-MM-DD hhmm"), _
      Quality:=0, IncludeDocProperties:=False, IgnorePrintAreas:=False, _
      openafterpublish:=False
  'Display confirmation of Save
  MsgBox "Saved as " & Worksheets("Image Map").Range("r2").Value & " - " & Format(Now(), "YYYY-MM-DD hhmm")
Else
  Exit Sub
End If
End Sub

Ciao,
Holger
 
Upvote 0
Solution
I just,,,,,,,
I just LOVE this place!!!!!!
Thank you so, so much - absolutely perfect - you're a hero!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Assistance changing VBA code to display as HTML or picture of a selection in Lotus Notes
Replies
2
Views
238
jularobert
J
I
  • Question Question
Advice for slight alteration to existing working code
Replies
1
Views
238
ipbr21054
I
M
  • Question Question
Please help me to correct Excel VBA code!
Replies
1
Views
368
Minh Tuan VN
M
R
  • Solved
Msbox VBA code when cell value is changed
2
Replies
18
Views
611
Ramadan2512
R
P
  • Question Question
putting a snippet of code from one to another somehow
Replies
5
Views
322
Patriot2879
P

Forum statistics

Threads
1,224,827
Messages
6,181,194
Members
453,021
Latest member
pingpong7117

Share this page

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
Back
Top