VB Help, Ambiguous name detected error

[wildride]

Hey everyone,

I don't know much about but put together this code from a few different posts, but I am getting an error that reads: Compile Error: Ambiguous name detected, workbook_open. It happens when I add the last bit of code to the existing code I already had. Here is all the code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
HideSheets
End Sub

Private Sub Workbook_Open()
UnhideSheets
End Sub

Private Sub HideSheets()
Dim sht As Object

Application.ScreenUpdating = False

ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVisible

For Each sht In ThisWorkbook.Sheets

If sht.Name <> "Macros Disabled" Then sht.Visible = xlSheetVeryHidden

Next sht

Application.ScreenUpdating = True

ThisWorkbook.Save

End Sub

Private Sub UnhideSheets()
Dim sht As Object

Application.ScreenUpdating = False

For Each sht In ThisWorkbook.Sheets

sht.Visible = xlSheetVisible

Next sht

ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVeryHidden

Application.ScreenUpdating = True

End Sub

Private Sub Workbook_Open()
If Now() > #5/30/2004# Then
Serial = InputBox("Thank You for beta testing. Input required serial number.")
If Serial <> "e4w3t8" Then
ActiveWorkbook.Close
End If
End If

Thanks for any help

 

[just_jon]

You have 2 macros with the same name, Workbook_Open -- you can have only 1.

 

[wildride]

Well that was easy....

Thanks Jon

 

[wildride]

Well I guess it wasn't easy for me lol. I tested the code and the name I changed will not work now. What can I change workbook_open to and still have it execute right?

Here is the code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
HideSheets
End Sub

Private Sub Workbook_Open()
UnhideSheets
End Sub

Private Sub HideSheets()
Dim sht As Object

Application.ScreenUpdating = False

ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVisible

For Each sht In ThisWorkbook.Sheets

If sht.Name <> "Macros Disabled" Then sht.Visible = xlSheetVeryHidden

Next sht

Application.ScreenUpdating = True

ThisWorkbook.Save

End Sub

Private Sub UnhideSheets()
Dim sht As Object

Application.ScreenUpdating = False

For Each sht In ThisWorkbook.Sheets

sht.Visible = xlSheetVisible

Next sht

ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVeryHidden

Application.ScreenUpdating = True

End Sub

Private Sub Workbook_Open()
If Now() > #5/20/2004# Then
Serial = InputBox("Thank You for beta testing. Input required serial number.")
If Serial <> "e4w3t8" Then
ActiveWorkbook.Close
End If
End If
End Sub


Thanks again

 

[Greg Truby]

Name it something else:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    HideSheets
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    AskForSerial
    UnhideSheets
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> HideSheets()
    <SPAN style="color:#00007F">Dim</SPAN> sht <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>

    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVisible
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> sht <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Sheets
        <SPAN style="color:#00007F">If</SPAN> sht.Name <> "Macros Disabled" <SPAN style="color:#00007F">Then</SPAN> sht.Visible = xlSheetVeryHidden
    <SPAN style="color:#00007F">Next</SPAN> sht
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    ThisWorkbook.Save
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UnhideSheets()
    <SPAN style="color:#00007F">Dim</SPAN> sht <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> sht <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Sheets
        sht.Visible = xlSheetVisible
    <SPAN style="color:#00007F">Next</SPAN> sht
    ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVeryHidden
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> AskForSerial()
    <SPAN style="color:#00007F">If</SPAN> Now() > #5/20/2004# <SPAN style="color:#00007F">Then</SPAN>
        Serial = InputBox("Thank You for beta testing. Input required serial number.")
        <SPAN style="color:#00007F">If</SPAN> Serial <> "e4w3t8" <SPAN style="color:#00007F">Then</SPAN> ActiveWorkbook.Close
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[SnakeEyed]

Hi, i am having a similar issue.

What part do i need to change in order to get this to run?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("C2").Address Then
If Range("C2") = "Yes" Then
Q1Yes
Else
Q1No
End If
End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("C3").Address Then
If Range("C3") = "Yes" Then
Q2Yes
Else
Q2No
End If
End If
End Sub

 

[Alphonse68]

Wildride,
Since you want both routines to run when the workbook is opened, why don't you combine them into one Private Sub Workbook_Open()?

 

[JackDanIce]

@Alphonse68 You're aware this thread started in 2004... I suspect Wildride has their answer or a workaround by now

 

[Alphonse68]

SnakeEyed,
I also would say you need combine your subs into one, being that they are event-triggered, but you can only have one Sub Worksheet_Change(). You might want to do as Greg wisely suggested: rename both to two different names, like Sub ChangeInC2() and Sub ChangeInC3(), and then call them from Sub Worksheet_Change().

Cheers.

Hahahahaaa... thanks... no, I hadn't!
I had been answering other current ones and I guess I got carried away!!
Cheers, Jack.