+ Reply to Thread
Results 1 to 3 of 3

Compile Error: Ambiguous Name Detected: Workbook_BeforeClose

  1. #1
    Registered User
    Join Date
    10-29-2012
    Location
    New Orleans
    MS-Off Ver
    Excel 2010
    Posts
    2

    Compile Error: Ambiguous Name Detected: Workbook_BeforeClose

    I have two sets of code that I need to apply to this workbook. The first code forces you to enable macros and the second code prevents any copying, cutting or pasting. I am getting an "Ambiguous Name" error. I know that is because I reference the same "Workbook_BeforeClose" and "Workbook_Open" in both but I am not sure how to fix it. I have seen other posts on here referencing the same problem but the solution has been a guru combines the two codes for the poster and I am not smart enough to figure out how. Two codes are below:

    Option Explicit

    Const WelcomePage = "Macros"

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Turn off events to prevent unwanted loops
    Application.EnableEvents = False

    'Evaluate if workbook is saved and emulate default propmts
    With ThisWorkbook
    If Not .Saved Then
    Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
    vbYesNoCancel + vbExclamation)
    Case Is = vbYes
    'Call customized save routine
    Call CustomSave
    Case Is = vbNo
    'Do not save
    Case Is = vbCancel
    'Set up procedure to cancel close
    Cancel = True
    End Select
    End If

    'If Cancel was clicked, turn events back on and cancel close,
    'otherwise close the workbook without saving further changes
    If Not Cancel = True Then
    .Saved = True
    Application.EnableEvents = True
    .Close savechanges:=False
    Else
    Application.EnableEvents = True
    End If
    End With
    End Sub

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    'Turn off events to prevent unwanted loops
    Application.EnableEvents = False

    'Call customized save routine and set workbook's saved property to true
    '(To cancel regular saving)
    Call CustomSave(SaveAsUI)
    Cancel = True

    'Turn events back on an set saved property to true
    Application.EnableEvents = True
    ThisWorkbook.Saved = True
    End Sub

    Private Sub Workbook_Open()
    'Unhide all worksheets
    Application.ScreenUpdating = False
    Call ShowAllSheets
    Application.ScreenUpdating = True
    End Sub

    Private Sub CustomSave(Optional SaveAs As Boolean)
    Dim ws As Worksheet, aWs As Worksheet, newFname As String
    'Turn off screen flashing
    Application.ScreenUpdating = False

    'Record active worksheet
    Set aWs = ActiveSheet

    'Hide all sheets
    Call HideAllSheets

    'Save workbook directly or prompt for saveas filename
    If SaveAs = True Then
    newFname = Application.GetSaveAsFilename( _
    fileFilter:="Excel Files (*.xls), *.xls")
    If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
    Else
    ThisWorkbook.Save
    End If

    'Restore file to where user was
    Call ShowAllSheets
    aWs.Activate

    'Restore screen updates
    Application.ScreenUpdating = True
    End Sub

    Private Sub HideAllSheets()
    '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()
    '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

    Option Explicit

    Private Sub Workbook_Activate()
    Call ToggleCutCopyAndPaste(False)
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call ToggleCutCopyAndPaste(True)
    End Sub

    Private Sub Workbook_Deactivate()
    Call ToggleCutCopyAndPaste(True)
    End Sub

    Private Sub Workbook_Open()
    Call ToggleCutCopyAndPaste(False)
    End Sub


    Any advice is appreciated! Thank you!

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Compile Error: Ambiguous Name Detected: Workbook_BeforeClose

    Welcome to the Forum!

    I believe the single line of code in the blue Subs can be used as the last line of code in the corresponding Sub in red.

    Also, I have responded since it is your first post, but please review forum rules. Code needs to be posted with code tags (see below and my sig). Text in a post is treated like text in HTML, so that any leading spaces are omitted and extra spaces are compressed to a single space. Using code tags preserves all the spacing of the code (hopefully you have used indentation appropriately).

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    10-29-2012
    Location
    New Orleans
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Compile Error: Ambiguous Name Detected: Workbook_BeforeClose

    Thanks 6StringJazzer! It works perfectly. I apologize for not using the code tags; I will be sure to do that going forward. Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1