MACRO to Approve or Decline

N

Neil Holden

Hi, I have an excel sheet with a button called Approve:

When this button is pressed I would like it to:

Message box - "Are you sure you want to approve this PIP?" YES OR NO option.

IF yes saveas to a default location

If No Reply back to the original sender via outlook saying this has been
declined.

Private Sub CommandButton1_Click()

ActiveWorkbook.Save

Dim Response As String
Dim DefaultFolder As String, DefaultFileName As String
Dim FileToSave

Response = MsgBox("Are you sure you want to Approve this PIP?", _
vbYesNo + vbInformation + vbDefaultButton2)

If Response = vbYes Then
DefaultFolder = "M:\Procurement\Approved PIPS"
If Right(DefaultFolder, 1) <> "\" Then
DefaultFolder = DefaultFolder & "\"
End If
DefaultFileName = Range("B10")
If Right(UCase(DefaultFileName), 3) <> "XLS" Then
DefaultFileName = DefaultFileName & " " & _
Format(Date, "dd-mm-yyyy") & ".xls"
End If
FileToSave = Application.GetSaveAsFilename _
(DefaultFolder & DefaultFileName, filefilter:="Excel Files (*.xls)," _
& "*.xls", Title:="Save File As...")
If FileToSave = False Then
Exit Sub
Else
ThisWorkbook.SaveAs _
Filename:=FileToSave, _
FileFormat:=ActiveWorkbook.FileFormat
End If
End If

End Sub
 
B

Barb Reinhardt

You're close. Try this change

Dim Response as VBMsgBoxResult

HTH,
Barb Reinhardt
 
D

Dave Peterson

I'd use:
Dim Response As Long

I'm not sure when VBMsgBoxResult was added to VBA.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

EXCEL CONNECTED WITH OUTLOOK 1
INPUT BOX in excel 2003 2
SAVE AS MACRO 4
PLEASE HELP!! SAVE MACRO 3
SAVE AS MACRO 8
Emailing in excel 2003 5
Excel 2003 help 1
MACRO help 9

Top