Code: Select all
If Application.Dialogs(xlDialogSaveAs).Show(arg2:=xlOpenXMLWorkbookMacroEnabled) Then
Dim WSHShell As Object
Dim strDesktopPath As String
Set WSHShell = CreateObject("WScript.Shell")
' Read desktop path using WshSpecialFolders object
strDesktopPath = WSHShell.SpecialFolders("Desktop")
If Not Right(strDesktopPath, 1) = "\" Then
strDesktopPath = strDesktopPath & "\"
End If
With WSHShell.CreateShortcut(strDesktopPath & "\" & ActiveWorkbook.Name & ".lnk")
' Set shortcut object properties and save it
.TargetPath = ActiveWorkbook.FullName
.Save
End With
Set WSHShell = Nothing
MsgBox ("Your new report has been saved and a shortcut icon for this new report has been added to your desktop")
ActiveWorkbook.Close
Else
MsgBox ("You have selected cancel")
End If
That way when the code is used and a new workbook is created with a different name then this code will not run within new workbook
I know there is going to be an "Else" in there somewhere