Yes/No Msg Box upon Close. Send user to link if Yes chosen.

[EMKP123]

Hi All!

I am attempting to create a pop up message box when a user closes the spreadsheet. I'd like it to function by:

1. Showing the Yes/No message box when the user attempts to close the sheet.
2. Send user to a link (google doc) if Yes is chosen.
3. Close sheet if No is chosen.

Here is my attempt (full google link removed), but the the Yes action breaks down:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Is this working?", _
vbQuestion + vbYesNo) = vbYes Then
objshell.Run ("https://docs.google.com/forms/d/e/....")
Else
Cancel = True
End If
End Sub

Admittedly I am a complete macro/VBa novice, so I pieced this together from various Google searches.

Any help is appreciated!!

Thanks!

 

[Logit]

.
This is one way ...

Option Explicit


Sub Test3()
Dim answer As Variant
  answer = MsgBox("Yes = run Test1; No = run Test2", 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

 

[EMKP123]

Thanks for your reply Logit. Can you please clarify where this code would fit into my existing macro? How do I make sure this runs upon Close and where does the reference to the google doc (action after user chooses Yes) go?

Apologies for what may be simple questions; I don't have any experience in this area.

.
This is one way ...

Option Explicit


Sub Test3()
Dim answer As Variant
  answer = MsgBox("Yes = run Test1; No = run Test2", 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

 

[Logit]

.

Option Explicit




Sub Test3()
Dim answer As Variant
  answer = MsgBox("Yes = Link ; No = Quit", vbYesNo + vbQuestion, "Go to Google Link ?")
  If answer = vbYes Then
      Call Test1
   Else
     Call Test2
End If
End Sub




Sub Test1()
   'Code here for Google link
End Sub




Sub Test2()
   'Code here to quit program or other action.
End Sub

 

[Logit]

You are welcome.