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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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

C
  • Question
Copy and paste data through multiple workbooks
Replies
2
Views
205
CDaviess
C
S
  • Solved
VBA: Am I double-calling a Sub given nested If statements? Or other inefficiencies?
Replies
8
Views
249
dreid1011
dreid1011
I
  • Solved
If Else advice to allow or forget transfer of values
Replies
7
Views
368
ipbr21054
I
I
  • Question
Excel vba to save as word doc
Replies
6
Views
664
ipbr21054
I
L
  • Question
vba code adjustment when importing csv file into excel
Replies
4
Views
915
londa_vba
L

Forum statistics

Threads
1,215,066
Messages
6,122,948
Members
449,095
Latest member
nmaske

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