Desktop shortcut

Joined
Jul 25, 2007
Messages
26
Reaction score
0
To anyone who may be able to help,
When I open my workbook the following code runs and sets a desktop icon shortcut to the workbook or if one already exists does not or if a cell on the sheet 'Help' = "Do not place shortcut on desktop". As the workbook name may be changed by the user to anything they want, the name is entered in cell C7 on the 'Help' sheet. This all works well.
But when I save the workbook as a template and then open it I get run time errors. When the workbook is saved the sub runs. When I inserted the first If statement I get a 'run time error 13 Type mismatch' the same with the second If statement. (They are two different versions of the same line).
So if someone can help me I will be most grateful.
Sub DeskTopIcon()
''This code is used It is run by 'Private Sub Workbook_Open()' on 'ThisWorkbook'

Dim WSHShell As Object
Dim MyShortcut As Object
Dim DesktopPath As String
If Sheets("Help").Range("C7").Value = "" Then
If Sheets("Help").Range("C7").Value <> ActiveWorkbook.Name & ".xls" Then
Exit Sub
Else
If Sheets("Help").Range("H2").Value = "Do Not Place A Shortcut On The Desktop" Then
Exit Sub
Else
Set WSHShell = CreateObject("WScript.Shell")
DesktopPath = WSHShell.SpecialFolders("Desktop")
Set MyShortcut = WSHShell.CreateShortcut(DesktopPath & "\" & ActiveWorkbook.Name & ".lnk")
If Not Dir(MyShortcut) <> "" Then
With MyShortcut
.TargetPath = ActiveWorkbook.FullName
.Save
End With
Set WSHShell = Nothing
Application.Run ("Icon_MessageBox")
End If
End If
End If
End Sub

Thanks All,
GeoffreyBarnard
 
Last edited:

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

Top