Novice - MsgBox Yes/No - Continue if Yes, Close if No

B

Beetlejuice

I have a form to be completed by staff ONLY if they have approval to proceed
with the work. I've managed (with this group's assistance!) to have a
message box pop up but I want to add a wrinkle where if they press "yes",
they can proceed to fill in the form, if they press "no", the file closes.
I've never done If/Then in VBA (this is actually my first foray into VBA).
Help please!:

Sub Auto_open()
MsgBox "The Project Manager's or Project Administrator's approval must be
obtained prior to proceeding with this work. Has approval been obtained?.",
vbYesNo, "WARNING"
End Sub
 
D

Dave Peterson

Option Explicit
Sub Auto_open()
Dim resp As Long
resp = MsgBox(prompt:="The Project Manager's or Project Administrator's" & _
" approval must be obtained prior to proceeding with" & _
" this work." & vbLf & vbLf & "Has approval been obtained?", _
Buttons:=vbYesNo, Title:="Warning")

If resp = vbNo Then
ThisWorkbook.Close savechanges:=False
End If

End Sub

I inserted a couple of vblf's to make it easier to read (for me, anyway).
 
B

Beetlejuice

Thank you! It works like a charm - I'm sure I'll be cursed from here to
eternity.
 
D

David Hepner

With out looking at your code I don't know what is causing the problem. I
didn't include the Sub Auto_open() in my previous response. See if this works
for you:


Sub Auto_open()

Dim response As Integer
Dim msg As String

msg = "The Project Manager's or Project Administrator's approval must be
obtained prior to proceeding with this work. Has approval been obtained?."
response = MsgBox(msg, vbYesNo, "WARNING")
If response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Else ' User chose No.
MyString = "No" ' Perform some action."
End If

End Sub
 
D

David Hepner

Try this:

Dim response As Integer
response = MsgBox("The Project Manager's or Project Administrator's approval
must be obtained prior to proceeding with this work. Has approval been
obtained?.", vbYesNo, "WARNING")
If response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Else ' User chose No.
MyString = "No" ' Perform some action."
End If
 
F

FSt1

hi,
if msgbox("The Project Manager's or Project Administrator's " & vbnewline _
& "approval must be obtained prior to proceeding with this work. " &
vbnewline _
& "Has approval been obtained?.", vbYesNo, "WARNING") = vbno then
Activeworkbook.close savechanges:=false
end if
 
B

Beetlejuice

I keep getting an "Invalid outside procedure"

David Hepner said:
Try this:

Dim response As Integer
response = MsgBox("The Project Manager's or Project Administrator's approval
must be obtained prior to proceeding with this work. Has approval been
obtained?.", vbYesNo, "WARNING")
If response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Else ' User chose No.
MyString = "No" ' Perform some action."
End If
Click to expand...
 
Top