MACRO to Approve or Decline
 

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

MACRO to Approve or Decline
 

You're close. Try this change

Dim Response as VBMsgBoxResult

HTH,
Barb Reinhardt

"Neil Holden" wrote:

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

MACRO to Approve or Decline
 

I'd use:
Dim Response As Long

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



Neil Holden wrote:

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

--

Dave Peterson