After playing around with your guys' code a bit, I managed to get something to work (seen below). I inserted snb's code into the Workbook_BeforeClose command of the "ThisWorkbook" code. The user has to go through all of the folders to get to their timesheet only once, and then it creates the shortcut with the cusomised .ico file when the document is closed. They have to open it the hard way once on each workstation. I am satisifed with this solution.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
Application.DisplayStatusBar = True
With CreateObject("WScript.Shell").CreateShortCut("C:\Users\jmv\Desktop\FY2011 Timesheet Template.lnk")
.targetpath = "G:\Timesheets\FY2011\Communications\FY2011 Timesheet Template.xlsb"
.iconlocation = "G:\Timesheets\PDF Archive\Miscellaneous\timesheeticon.ico"
.Save
End With
End Sub
Private Sub Workbook_Deactivate()
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
Application.DisplayStatusBar = True
End Sub
Private Sub Workbook_Activate()
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
Application.DisplayStatusBar = True
End Sub
Private Sub Workbook_Open()
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Worksheets("Menu").Select
Application.DisplayStatusBar = False
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
If IsNumeric(Sh.Name) Then
Sh.ScrollArea = "A1:O30"
Else
Sh.ScrollArea = "A1:M12"
End If
End Sub
Bookmarks