Hi all,
I have the following two codes, which go in ThisWorkbook module:
and![]()
Option Explicit Const WelcomePage = "Macros" Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Author : Ken Puls (www.excelguru.ca) 'Macro Purpose: Ensure that the macro instruction sheet is saved as the only ' visible worksheet in the workbook Dim ws As Worksheet Dim wsActive As Worksheet Dim vFilename As Variant Dim bSaved As Boolean 'Turn off screen flashing With Application .EnableEvents = False .ScreenUpdating = False End With 'Record active worksheet Set wsActive = ActiveSheet 'Save workbook directly or prompt for saveas filename If SaveAsUI = True Then vFilename = Application.GetSaveAsFilename( _ fileFilter:="Excel Files (*.xls*), *.xls*") If CStr(vFilename) = "False" Then bSaved = False Else 'Save the workbook using the supplied filename Call HideAllSheets ThisWorkbook.SaveAs vFilename Application.RecentFiles.Add vFilename Call ShowAllSheets bSaved = True End If Else 'Save the workbook Call HideAllSheets ThisWorkbook.Save Call ShowAllSheets bSaved = True End If 'Restore file to where user was wsActive.Activate 'Restore screen updates With Application .ScreenUpdating = True .EnableEvents = True End With 'Set application states appropriately If bSaved Then ThisWorkbook.Saved = True Cancel = True Else Cancel = True End If End Sub Private Sub Workbook_Open() 'Author : Ken Puls (www.excelguru.ca) 'Macro Purpose: Unhide all worksheets since macros are enabled Application.ScreenUpdating = False Call ShowAllSheets Application.ScreenUpdating = True ThisWorkbook.Saved = True End Sub Private Sub HideAllSheets() 'Author : Ken Puls (www.excelguru.ca) 'Macro Purpose: Hide all worksheets except the macro welcome page Dim ws As Worksheet Worksheets(WelcomePage).Visible = xlSheetVisible For Each ws In ThisWorkbook.Worksheets If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden Next ws Worksheets(WelcomePage).Activate End Sub Private Sub ShowAllSheets() 'Author : Ken Puls (www.excelguru.ca) 'Macro Purpose: Show all worksheets except the macro welcome page Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible Next ws Worksheets(WelcomePage).Visible = xlSheetVeryHidden End Sub
The first code forces the user to enable macros, and the second code tracks the opening and closing of the workbook. The Tracking Log worksheet is "xlSheetVeryHidden" and the TestFile workbook is hidden.![]()
Private Sub Workbook_Open() UN = Environ("username") Application.Workbooks.Open ("C:\Documents and Settings\Owner\Desktop\TestFile.xlsm") i = 1 Do Until Workbooks("TestFile").Sheets("Tracking Log").Range("A" & i) = "" i = i + 1 Loop FinalRow = Workbooks("TestFile").Sheets("Tracking Log").Range("A1048576").End(xlUp).Row + 1 Workbooks("TestFile").Sheets("Tracking Log").Range("A" & FinalRow).Value = Now Workbooks("TestFile").Sheets("Tracking Log").Range("B" & FinalRow).Value = UN End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) UN = Environ("username") i = 1 Do Until Workbooks("TestFile").Sheets("Tracking Log").Range("A" & i) = "" i = i + 1 Loop FinalRow = Workbooks("TestFile").Sheets("Tracking Log").Range("C1048576").End(xlUp).Row + 1 Workbooks("TestFile").Sheets("Tracking Log").Range("C" & FinalRow).Value = Now Workbooks("TestFile").Sheets("Tracking Log").Range("D" & FinalRow).Value = UN Application.DisplayAlerts = False Workbooks("TestFile.xlsm").Save Workbooks("TestFile.xlsm").Close Application.DisplayAlerts = True End Sub
How do I put them in the same ThisWorkbook module without getting the "Ambiguous name detected: Workbook_open" error?
Thank you,
Gos-C











LinkBack URL
About LinkBacks
Register To Reply
Bookmarks