Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error Message Show (Excel 2000 SP-3)

    In "Electrical Estimate.xls" I have Workbook_Open code that loads another sheet "Standard Costs.xls". I can't find any code that will eliminate the attached message. The problem is in the fact that the named range "ElectricalLookup" is a dynamic range. I can't think of anything else to try. Anybody got an idea"

    Private Sub Workbook_Open()

    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Set WkBks = Application.Workbooks
    DBOpened = 0
    For Each WkBk In WkBks
    If UCase(WkBk.Name) = UCase(wbStandard) Then
    DBOpened = 1
    End If
    Next
    If DBOpened = 0 Then
    Workbooks.Open ThisWorkbook.Path & "" & wbStandard
    'ActiveWindow.Visible = False
    ThisWorkbook.Activate
    End If
    AddMenusToMenubar
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic

    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Message Show (Excel 2000 SP-3)

    If you run the specific code that causes the error message to occur *AFTER* a recalc of the workbook, does the problem stay?
    I see no references to the defined name in your code. Exactly when does the error surface?

    Does the workbook that is being opened contain code?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Error Message Show (Excel 2000 SP-3)

    This may or may not be relevant. You suggest that the dynamic RangeName is ElectricalLookup, whereas in the error message the missing name is ElectricalLook (i.e. no "up" at the end).

    Search the entire VBAProject for ElectricalLook as it seems the name is referenced somewhere in code, probably a worksheet Activate event.

    Andrew C

  4. #4
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Message Show (Excel 2000 SP-3)

    Jan and Andrew,

    The workbook being opened contains NO code. The error occurs in a cell Lookup formula in the "Electrical Estimate.xls" that references the "Standard Costs.xls". Obviously this is being evaluated prior to the Workbook)Opem event. The "ElectricalLookup" was a typo it actually is "ElectricalLook".

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Message Show (Excel 2000 SP-3)

    When you open the workbook containing the named range manually and press F5 and enter the name and press OK, are you taken to the correct range?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Message Show (Excel 2000 SP-3)

    Jan,

    Yes it selects the range as expected. The dynamic range is defined by =OFFSET(Electrical!$B$6,0,0,COUNTA(Electrical!$B:$[img]/forums/images/smilies/cool.gif[/img],7).

Posting Permissions

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