Compile Error: Ambiguous Name Detected: Workbook_Open

**Gos-C** · 05-18-2008, 07:47 AM

Hi all,

I have the following two codes, which go in ThisWorkbook module:

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

and

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

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.

How do I put them in the same ThisWorkbook module without getting the "Ambiguous name detected: Workbook_open" error?

Thank you,
Gos-C

**royUK** · 05-18-2008, 07:50 AM

You need to place your code in Standard modules & call them from the Open event

**Gos-C** · 05-18-2008, 09:18 AM

Thank you for replying, royUK.

I placed the codes in standard modules. How do I call them?

Thanks,
Gos-C

**royUK** · 05-18-2008, 09:24 AM

Replace macro1 7 macro2 with the names that you have given your new macros

Option Explicit

Private Sub Workbook_Open()
Call macro1
Call macro2
End Sub

**Gos-C** · 05-19-2008, 08:55 AM

Hi royUK,

Thanks again for your guidance.

After several tries, I finally got it to work. I kept everything in ThisWorkbook, called the TrackOpen code, and declared some undeclared variables.

Can you please review and advise if everything, especially those in red letters, is okay.

Here is the code:

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
    Dim UN As String
    Dim i As Variant
    '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
    Call TrackOpen
    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

Private Sub TrackOpen()
Dim UN As String
Dim i As Variant
Dim FinalRow As Long
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)
Dim UN As String
Dim i As Variant
Dim FinalRow As Long
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

I appreciate you help.

Thank you,
Gos-C

**royUK** · 05-19-2008, 09:21 AM

I wouldn't place the non workbook code in the workbook module, but in a separate module.

Option Explicit
Const WelcomePage = "Macros"
Dim ws         As Worksheet
 Sub HideAllSheets()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Hide all worksheets except the macro welcome page
    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

 Sub ShowAllSheets()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Show all worksheets except the macro welcome page
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
    Next ws
    Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

 Sub TrackOpen()
    Dim UN     As String
    Dim i      As Long
    Dim FinalRow As Long
    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

Then in the workbook module

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call HideAllSheets
End Sub

Private Sub Workbook_Open()
    Call ShowAllSheets
    Call TrackOpen
End Sub

Compile Error: Ambiguous Name Detected: Workbook_Open

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Compile Error: Ambiguous Name Detected: Workbook_Open

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions