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
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