“method worksheets of object_global failed” error when calling macro from workbook_open event

[DWW28029]

I'm getting a "method worksheets of object_global failed" error when a macro is called from the Workbook_Open event. What really confuses me is that the wc_user_update macro runs fine when it is not called from the workbook_open event. Also, when I comment out the password protection in the workbook open event it still doesn't work. So the problem seems to be in how I am calling the wc_user_update macro from the Workbook_Open event. Am I missing something obvious? Thanks in advance!
Code in ThisWorkbook:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">[COLOR=#00008B]Private[/COLOR] [COLOR=#00008B]Sub[/COLOR] Workbook_Open()    
[COLOR=#808080]    'Dim ws As Worksheet[/COLOR]    
[COLOR=#808080]    'For Each ws In Worksheets[/COLOR]        
[COLOR=#808080]    'ws.Protect password:="x", UserInterfaceOnly:=True[/COLOR]    
[COLOR=#808080]    'Next ws[/COLOR]    
    wc_user_update[COLOR=#00008B]End[/COLOR] [COLOR=#00008B]Sub[/COLOR]</code>

Code in a standard module:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">[COLOR=#00008B]Public[/COLOR] [COLOR=#00008B]Sub[/COLOR] wc_user_update()    
[COLOR=#808080]    'Ask user if he wants to update usernames. If yes, prompt for credentials[/COLOR]     
[COLOR=#00008B]    Dim[/COLOR] update [COLOR=#00008B]As[/COLOR] [COLOR=#00008B]Variant[/COLOR]    
[COLOR=#808080]    'On Error GoTo ErrorHandler[/COLOR]    
[COLOR=#00008B]    With[/COLOR] Worksheets([COLOR=#800000]"WC_USERS"[/COLOR])        
        update = MsgBox([COLOR=#800000]"Usernames have not been updated in "[/COLOR] & .Range([COLOR=#800000]"wc_names_last_updated_days"[/COLOR]).Value & [COLOR=#800000]" days. Would you like to update now?"[/COLOR], vbYesNo)    
[COLOR=#00008B]    End[/COLOR] [COLOR=#00008B]With[/COLOR]    
[COLOR=#00008B]    If[/COLOR] update = vbYes [COLOR=#00008B]Then[/COLOR]        
[COLOR=#808080]   'show the authentication userform centered in the excel window
[/COLOR]        [COLOR=#00008B]With[/COLOR] wc_auth               
            .StartUpPosition = [COLOR=#800000]0[/COLOR]            
            .Left = Application.Left + ([COLOR=#800000]0.5[/COLOR] * Application.Width) - ([COLOR=#800000]0.5[/COLOR] * .Width)
            .Top = Application.Top + ([COLOR=#800000]0.5[/COLOR] * Application.Height) - ([COLOR=#800000]0.5[/COLOR] * .Height)
            .Show
        [COLOR=#00008B]End[/COLOR] [COLOR=#00008B]With[/COLOR]    
[COLOR=#808080]        'Do stuff[/COLOR]    
[COLOR=#808080]        'update "last updated date" cell[/COLOR]    
        Worksheets([COLOR=#800000]"WC_USERS"[/COLOR]).Range([COLOR=#800000]"wc_names_last_updated_date"[/COLOR]).Value = [COLOR=#00008B]Date[/COLOR]    
        Unload wc_auth    
[COLOR=#00008B]    Else[/COLOR]        
        MsgBox ([COLOR=#800000]"usernames not updating"[/COLOR])    
[COLOR=#00008B]    End[/COLOR] [COLOR=#00008B]If[/COLOR]    
    ErrorHandler:
[COLOR=#00008B]    End[/COLOR] [COLOR=#00008B]Sub
[/COLOR]</code>

 

[dmt32]

Hi,
Only thing that I can see in your procedure that may (or may not) be a cause of your problem is that you have declared "Update" as a variable. Update method is used to update a link or piviot table report & this may be reason your code fails.

Try renaming your variable to something Like:UserUpdate

& see if that makes any difference.

The only other thought is one or both of your named ranges may be corrupt.

Hope helpful

Dave

 

[RichardMGreen]

You could also try using:
Call wc_user_update

instead of just:
wc_user_update

in the workbook open event.

Is it possible you have two procedures with the same name?
You could also use:-
Call Module1.wc_user_update (you'll need to update Module1 to whatever the name of your module is)

which may get around the problem.

 

[RoryA]

Try qualifying it with the ThisWorkbook object:

Public Sub wc_user_update()
'Ask user if he wants to update usernames. If yes, prompt for credentials
    Dim update                      As Variant
    Dim ws                          As Worksheet

    Set ws = ThisWorkbook.Worksheets("WC_USERS")

    'On Error GoTo ErrorHandler
    With ws
        update = MsgBox("Usernames have not been updated in " & .Range("wc_names_last_updated_days").Value & " days. Would you like to update now?", vbYesNo)
    End With
    If update = vbYes Then
        'show the authentication userform centered in the excel window
        With wc_auth
            .StartUpPosition = 0
            .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
            .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
            .Show
        End With
        'Do stuff
        'update "last updated date" cell
        ws.Range("wc_names_last_updated_date").Value = Date
        Unload wc_auth
    Else
        MsgBox "usernames not updating"
    End If
ErrorHandler:
End Sub

 

[DWW28029]

Yep, it looks like the problem was with using "update" as a variable name. Thanks everyone!

EDIT: Actually I spoke too soon. Still getting the error. I'll have to take a closer look

 

[DWW28029]

I also qualified the "WC_USERS" worksheet with ThisWorkbook per RoryA's post. It seems to be working now (knock on wood). Just out of curiousity, why is it that in this situation I need to fully qualify the worksheet with the workbook? Should I always be including the workbook in the worksheet reference?

 

[RoryA]

Generally speaking, yes it's best to be as specific as possible with object references, so include the workbook.