code works everywhere except Workbook_Open()

[JTBorton]

I want to run a test when the workbook opens to make sure the vital sheets are present before any more events happen. The For Each loop I am using seems to work everywhere else except in the Workbook_Open() section. Here is the code I am using. The bln---'s are used to check off if each sheet is present.

Runtime error '13'
Type mismatch

Private Sub Workbook_Open()
Dim wkSht As Excel.Worksheets, blnListedCities As Boolean, blnMyMailingLists As Boolean, blnMainMenu As Boolean, _
    blnContactManager As Boolean, blnAgendaManager As Boolean, blnAILManager As Boolean

    [highlight]For Each wkSht In Worksheets[/highlight] [COLOR=green]'Make sure critical worksheets are in the workbook[/color]
        Select Case wkSht.Name
            Case "Main Menu"
                blnMainMenu = True
            Case "Listed Cities"
                blnListedCities = True
            Case "Contact Manager"
                blnContactManager = True
            Case "AILManager"
                blnAILManager = True
            Case "AgendaManager"
                blnAgendaManager = True
            Case "My Mailing Lists"
                blnMyMailingLists = True
        End Select
    Next wkSht
End Sub

In another part of the project, I use the same loop to add worksheets into a combobox, which works perfectly:

Dim wkSht As Excel.Worksheet, objList As ComboBox
    
    Set objList = cboViewAIL
    objList.Clear
    For Each wkSht In Worksheets
        If LCase(Left(wkSht.Name, 3)) = "ail" And Not LCase(wkSht.Name) = "ail manager" Then
            objList.AddItem Mid(wkSht.Name, 4, Len(wkSht.Name) - 3)
        End If
    Next wkSht
    .
    .
    .

-JTBorton
Another Day, Another Disaster

 

[combo]

Try to change declaration:
Dim wkSht As Excel.Worksheet[!]s[/!]

combo

 

[SkipVought]

Hi,

SCOPE of your variables!!! Do some reading. These variables are ONLY scoped for this procedure.

Make them GLOBAL, by moving the declarations to a MODULE and declaring as PUBLIC.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I might also make this a boolean ARRAY variable. You might also consider making a LIST of your worksheets and running your loop against the list values to verify, rather than hard-coding the names in your code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JTBorton]

Combo - Good eye sniper! That fixed it right up.

Skip Vought - thanks for your insight. I didn't want to make the variables global because I am trying to cut down on an excess of global variables. The wkSht variable is not used enough to justify permanently chewing up memory. I've never even thought of doing a boolean array. I like it, it makes me feel all warm and fuzzy-ish inside.

What exactly do you mean by 'making a LIST of your worksheets and running your loop against the list values to verify?'

-JTBorton
Another Day, Another Disaster

 

[SkipVought]

dim ws as worksheet, r as range, bFound as boolean
for each ws in worksheets
  bfound = false
  for each r in [yourlist]
    if r.value = ws.name then 
      bfound = true
      exit for
    end if
  next
  if not bfound then msgbox ws.name & " not found"
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JTBorton]

Thanks Skip, I ended up doing it as follows, after switching your loops:

Dim wkSht As Excel.Worksheet, CriticalSheets() As String, R As Variant, blnFound As Boolean
    
    [COLOR=green]'Test to make sure critical worksheets are present.[/color]
    Call GetCriticalSheets(CriticalSheets)
    For Each R In CriticalSheets
        blnFound = False
        For Each wkSht In Worksheets
            If R = wkSht.Name Then
                blnFound = True
                Exit For
            End If
        Next
        If Not blnFound Then
            MsgBox R & " not found"
            [COLOR=green]'More to be added later[/color]
        End If
    Next

where GetCriticalSheets is a sub that ReDims the variable (by reference) and fills it in with the desired sheets.

-JTBorton
Another Day, Another Disaster