Help with - "Ambigious name detected" - mulitple codes on sheet.

[galvinpaddy]

Good morning happy campers.

I have the following codes set in a workbook.
This Workbook -

Private Sub WorkBook_Open()
    Call GetSavesStarted
    Call GetSaves_Started
End Sub

Private Sub WorkBook_BeforeClose(Cancel As Boolean)
If Executed = True Then Application.OnTime EarliestTime:=RunWhen, Procedure:=RunWhat, _
        Schedule:=False
End Sub

Module 1

Public Wb As Workbook
Public RunWhen As Date
Public RunWhat As String
Public Executed As Boolean
Public Sub GetSavesStarted()
Set Wb = ThisWorkbook
RunWhat = "SaveThis"
Executed = True
Call SaveThis
End Sub
Public Sub SaveThis()
Application.DisplayAlerts = False
Wb.Save
Application.DisplayAlerts = True
RunWhen = Now + TimeValue("00:05:00")
Application.OnTime EarliestTime:=RunWhen, Procedure:=RunWhat, _
        Schedule:=True
End Sub

Module 2

Public Wb As Workbook
Public RunWhen As Date
Public RunWhat As String
Public Executed As Boolean
Public Sub GetSaves_Started()
Set Wb = ThisWorkbook
RunWhat = "SaveMe"
Executed = True
Call SaveMe
End Sub
Public Sub SaveMe()
Application.DisplayAlerts = False
Wb.UpdateLink Name:=Wb.LinkSources
Application.DisplayAlerts = True
RunWhen = Now + TimeValue("00:00:30")
Application.OnTime EarliestTime:=RunWhen, Procedure:=RunWhat, _
        Schedule:=True
End Sub

So far, when the work book is shared (allowing changes by more than one user) it appears to work fine, updating/refreshing the values every 30 seconds and then saving every 5 minutes.
The problem comes when closing the worksheet, i have the following error - "Complie Error: Ambigious name detected; ~"
Which highlights the following line in yellow -

Private Sub WorkBook_BeforeClose(Cancel As Boolean)

If i simply close the debugger then excel asks me if i want to save, i click yes, it saves and closes, job done.
What i would like to know is is the error going to casue me an issue, and either way, how do i prevent it?
I cant post work book yet as i would need to strip all data out of it, but the data inside the sheet is all manual entry with very few formula's, basically the code doesnt modify anything inside the sheet apart from updating values.

Kind Regards.
galvinpaddy

[galvinpaddy]

Good morning happy campers,

Would really like some help in resolving this

regards, galvinpaddy

[rkey]

Hi Galvinpaddy,

Can you give it a try without these rows in module2?

Public Wb As Workbook
Public RunWhen As Date
Public RunWhat As String
Public Executed As Boolean

Looks like some double public declarations

[galvinpaddy]

hhhmmmm, i changed my code to read -

Public Sub GetSaves_Started()
Set Wb = ThisWorkbook
RunWhat = "SaveMe"
Executed = True
Call SaveMe
End Sub
Public Sub SaveMe()
Application.DisplayAlerts = False
ActiveWorkbook.RefreshAll
Application.DisplayAlerts = True
RunWhen = Now + TimeValue("00:00:30")
Application.OnTime EarliestTime:=RunWhen, Procedure:=RunWhat, _
        Schedule:=True
End Sub

And now receive Run-time error '1004': Method of object '_Workbook' failed.
The sheet is not protected it is only shared.

[galvinpaddy]

Hi, thanks for that!!!
BUT -
Whilst i no longer receive that error, Now, when the workbook is shared the changes dont update from another user.
I think i may have added a wrong sectiopn of code though -

Wb.UpdateLink Name:=Wb.LinkSources

should this be

    ActiveWorkbook.RefreshAll

??

[rkey]

Hi Galvinpaddy,

Check this tread: http://www.excelforum.com/excel-gene...-question.html

Regards,
Rkey

[galvinpaddy]

?? lol, no link in your post :D

[rkey]

Got fat fingers, hitted the enter :P

[galvinpaddy]

hehehe
ok, so from their i get

Option Explicit
Private Sub Worksheet_Activate(ByVal Sh As Object)
ThisWorkbook.RefreshAll
End Sub

But how does this fit in with my code??

[rkey]

I'm on a curve here as well.
I read a lot of disregards towards sharing a book :P.
However, i just came across this one.
Can you try this?

Public Sub SaveMe()
Application.DisplayAlerts = False
If ActiveWorkbook.MultiUserEditing Then
 Wb.AcceptAllChanges
 Wb.Save
End If
Application.DisplayAlerts = True
RunWhen = Now + TimeValue("00:00:30")
Application.OnTime EarliestTime:=RunWhen, Procedure:=RunWhat, _
        Schedule:=True
End Sub

[galvinpaddy]

lots of curves around shared books as far as i can see lol

That code does save the sheet but does not appear to update changes made by others

[royUK]

You are declaring your variables as Public, so you have identical variables because you repeat it in two modules.

[galvinpaddy]

Hi RoyUK,
Thanks for that, would you be kind enough to 'repair' my coding?

regards

[royUK]

attach the file

[jaslake]

Hi Richard
This is code I use for timer events. I don't know from shared workbooks...not certain what the code will do in that regard. Replace your code with this code. Let me know of issues.

In ThisWorkbook Module

Option Explicit
Private Sub Workbook_Open()
    Call StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopTimer
    ActiveWorkbook.Save
End Sub

In a General Module

Option Explicit
Dim RunTime

Sub StartTimer()
    Application.ScreenUpdating = False
    ActiveWorkbook.RefreshAll
    RunTime = Now + TimeValue("00:05:00")
    Application.OnTime RunTime, "RefreshTime"
    Beep
    Application.ScreenUpdating = True
End Sub

Sub StopTimer()
'  http://www.cpearson.com/excel/OnTime.aspx
    On Error Resume Next
    Application.OnTime RunTime, "RefreshTime", Schedule:=False
    On Error GoTo 0
End Sub

Sub RefreshTime()
    ActiveWorkbook.Save
    StartTimer
End Sub

[jaslake]

Hi Richard
Been playing with shared workbooks...change this code...it appears to perform as expected

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopTimer
    ActiveWorkbook.Close True
'    ActiveWorkbook.Save
End Sub

[galvinpaddy]

Good morning John,

Having made the following changes:
THIS WORKBOOK

Option Explicit
Private Sub Workbook_Open()
    Call StartTimer
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopTimer
    ActiveWorkbook.Close True
'    ActiveWorkbook.Save
End Sub

MODULE1

Option Explicit
Dim RunTime
Sub StartTimer()
    Application.ScreenUpdating = False
    ActiveWorkbook.RefreshAll
    RunTime = Now + TimeValue("00:00:20")
    Application.OnTime RunTime, "RefreshTime"
    Beep
    Application.ScreenUpdating = True
End Sub
Sub StopTimer()
'  http://www.cpearson.com/excel/OnTime.aspx
    On Error Resume Next
    Application.OnTime RunTime, "RefreshTime", Schedule:=False
    On Error GoTo 0
End Sub
Sub RefreshTime()
    ActiveWorkbook.Save
    StartTimer
End Sub

Once shared & saved, the following error comes up - "Run-time error '1004': Method 'RefreshAll' of object '_Workbook' failed. i cant debug (as its shared) to show you what is highlighted.

Please advise.
Kind Regards, galvinpaddy

[jaslake]

Hi Richard
Comment out this line of code

ActiveWorkbook.RefreshAll

[galvinpaddy]

John, you sir are a legend yet again.

So many thanks indeed.

[jaslake]

You're welcome...glad I could help.