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