Results 1 to 8 of 8
  1. #1
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Menu Code Question (XL97SR2h)

    Check to make sure you have the reference for Microsoft Office and Excel object libraries checked in VBE/Tools/References.
    Legare Coleman

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Menu Code Question (XL97SR2h)

    Thank you, that was the problem, Legare, the Office Library was not checked. If one of the three computers I have been using to work on this project either does not not have these all libraries selected or installed, could this cause the this particular workbook to get "unreferenced"? Any ideas what else might have caused it?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Menu Code Question (XL97SR2h)

    I'm not 100% sure, but I don't think References are stored with the workbook. I think those are global on the system Excel is installed on. So, the reference was probably not checked before you moved the workbook to the system.
    Legare Coleman

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

    Re: Menu Code Question (XL97SR2h)

    References are workbook-specific and should travel to another system. If those resources are missing from that system, you'll get an error and end up with the references dialog. If you then uncheck the missing one(s) and save, well...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Menu Code Question (XL97SR2h)

    Thanks, that's good to know.
    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Menu Code Question (XL97SR2h)

    Code on the attached (stripped down) [attachment deleted in view of reason the code wasn't running] was working until I migrated it from where I was testing; it now errors out on "Dim tempFETB As CommandBar" with the message 'User-defined type not defined'. I'm confused, as I thought CommandBar is a built in object. (Note, since it's called on Workbook_Open(), it'll error out as soon as you open, and since it doesn't run, it'll error out when you Workbook_Close.) For that matter I can re-export it to a new workbook and it works, and the workbook is much smaller. What havoc have I created? <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Menu Code Question (XL97SR2h)

    Which leaves me a bit mystified about what happened, but a solution is a solution. Thanks again.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Menu Code Question (XL97SR2h)

    I added code to my workbook_open event that checked if the required references were set or not. If any are missing, I issue a warning to the user and close the workbook. At least this way they are able to tell me what the problem was instead of stumbling into weird errors.

    I got this from John Green's Excel 2000 VBA book (page 319) and then modfied it a bit:

    <pre>Public Function ExtReferencesValid() As Boolean
    Dim objRef As Object, stDescn As String

    ExtReferencesValid = True
    For Each objRef In ThisWorkbook.VBProject.References
    ' Debug.Print "Checking references for: " & objRef.Name
    If objRef.IsBroken Then
    'some broken links don't have descriptions, ignore
    On Error Resume Next
    stDescn = "<Not know>"
    stDescn = objRef.description
    On Error GoTo 0

    'display message to install missing item
    ExtReferencesValid = False
    MsgBox "Missing reference to: " & vbCrLf & "Name: " & _
    stDescn & vbCrLf & "Path: " & objRef.FullPath & vbCrLf & _
    "Please re-install this file. The model will not work without it", _
    vbCritical + vbOKOnly, "Missing Required File"
    End If
    Next objRef

    End Function
    </pre>


    I'd recommend adding this to your workbook as a safety measure.

    Deb <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

Posting Permissions

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