Results 1 to 5 of 5

Thread: compile error- "user-defined type not defined"

  1. #1
    Board Regular
    Join Date
    Dec 2016
    Location
    Houston, TX
    Posts
    246
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question compile error- "user-defined type not defined"

    I was sent a workbook from one of our suppliers and asked to complete it (its a questionnaire.) Whenever someone tries to choose a selction from one of the drop-down boxes on the worksheet (not in a form) we get the 'user-defined type not defined' compile error... same thing happens on any machine in our company (not limited to just mine.)
    After googling this issue, it appears due to being from a missing reference... but after trying mulitple types, I cannot find anything that will work (and there is no "missing reference" that pops up in the reference window that I usually see whenever I run into an issue like this...)

    Anyone have any clue as to what this is and how to fix it? (btw, I had it sent back to the originator and explained what is going on, and they sent back a different version that doesnt appear to be different at all as it is doing the exact same thing as the original one that I received yesterday.)

    Here is a screen shot of of the worksheet and one of the drop-down boxes that is not working and causing the error :



    Here is the code that its tripping on and causing the error:

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: compile error- "user-defined type not defined"

    Hi, what version of Excel are you using? As there is no code in that sub, can't you just delete it?
    [code]your code[/code]

  3. #3
    Board Regular
    Join Date
    Dec 2016
    Location
    Houston, TX
    Posts
    246
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: compile error- "user-defined type not defined"

    Quote Originally Posted by FormR View Post
    Hi, what version of Excel are you using? As there is no code in that sub, can't you just delete it?
    Thank you for the reply: Microsoft Office 2010, Version 14.0.7232.5000(32-bit)

    Throughout my company there are a few newer version (64bit) machines and I tried the code out on it too and it also crashed.

    Deleting the code:
    I tired that yesterday (and now today) and it then trips up with error:

    "Run-time error '40036':

    Application-defined or object-defined error"



    Here is the code in the module where the error occurs:

    Code:
    Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
    
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    ' To allow multiple selections in a Drop Down List in Excel (without repetition)
    Dim Oldvalue As String
    Dim Newvalue As String
    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Target.Row = 16 Then
      If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
        GoTo Exitsub
      Else: If Target.Value = "" Then GoTo Exitsub Else
        Application.EnableEvents = False
        Newvalue = Target.Value
        Application.Undo
        Oldvalue = Target.Value
          If Oldvalue = "" Then
            Target.Value = Newvalue
          Else
            If InStr(1, Oldvalue, Newvalue) = 0 Then
                Target.Value = Oldvalue & vbNewLine & Newvalue
          Else:
            Target.Value = Oldvalue
          End If
        End If
      End If
    End If
    Application.EnableEvents = True
    Exitsub:
    Application.EnableEvents = True
    If [B9] = "Service" Then
     Sheets("Services").Visible = True
     Else
     Sheets("Services").Visible = False
     End If
     If [B12] = "Yes" Then
     Sheets("Structure").Visible = True
     Else
     Sheets("Structure").Visible = False
     End If
     If [D14] = "No" Then
     Sheets("QMS").Visible = True
     Else
     Sheets("QMS").Visible = False
     End If
     If [F57] = "Yes" Then
     Sheets("Localization").Visible = True
     Else
     Sheets("Localization").Visible = False
     End If
     Dim celltxt As String
    'replace ("form") to the sheet name
    celltxt = Sheets("General").Range("C16").Text
    If InStr(1, celltxt, "Precision Machining (SRS-QA42)") Then
    'MsgBox ("found it")
    Sheets("Precision Machining").Visible = True
    Else
    Sheets("Precision Machining").Visible = False
    End If
    If InStr(1, celltxt, "Electronic Components and Assemblies (SRS-QA21)") Then
    'MsgBox ("found it")
    Sheets("Electrical").Visible = True
    Else
    Sheets("Electrical").Visible = False
    End If
    If InStr(1, celltxt, "Independent Electronic Component Distributors (Brokers) (SRS-QA22)") Then
    'MsgBox ("found it")
    Sheets("Electronic Component Dist").Visible = True
    Else
    Sheets("Electronic Component Dist").Visible = False
    End If
    If InStr(1, celltxt, "NonDestructive Testing (NDT) (SRS-QA30)") Then
    'MsgBox ("found it")
    Sheets("NDT").Visible = True
    Else
    Sheets("NDT").Visible = False
    End If
    If InStr(1, celltxt, "Welding, Brazing and Overlay (SRS-QA28)") Then
    'MsgBox ("found it")
    Sheets("Welding").Visible = True
    Else
    Sheets("Welding").Visible = False
    End If
    If InStr(1, celltxt, "Heat Treatment (SRS-QA31)") Then
    'MsgBox ("found it")
    Sheets("Heat Treatment").Visible = True
    Else
    Sheets("Heat Treatment").Visible = False
    End If
    If InStr(1, celltxt, "Chemicals (SRS-QA19)") Then
    'MsgBox ("found it")
    Sheets("Chemicals").Visible = True
    Else
    Sheets("Chemicals").Visible = False
    End If
    End Sub
    Last edited by kbishop94; Jun 4th, 2019 at 11:22 AM.

  4. #4
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: compile error- "user-defined type not defined"

    Quote Originally Posted by kbishop94 View Post
    Microsoft Office 2010, Version 14.0.7232.5000(32-bit)
    I think that the Worksheet_TableUpdate event isn't available in that version. As for the new error, difficult to debug from here - can you manually hide the sheet through the UI?
    [code]your code[/code]

  5. #5
    Board Regular
    Join Date
    Dec 2016
    Location
    Houston, TX
    Posts
    246
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: compile error- "user-defined type not defined"

    I did manage to open the workbook on a 64bit machine (one of our other plants have a couple newer workstations that are 64 bit ) using the 64bit version of Microsoft Office and it did work fine (well, it did give me a "cannot find project or library" error when first opened, but after I de-selected the MISSING reference box for that it then ran fine...)
    So at least I confirmed its due to the conflict of 32 vs 64 bit machines. I sent it back to the supplier and told them it still doesnt work... which, honestly, is really, REALLY surprising that they did this to begin with because this company is a... well, a H-U-G-E company and Im sure they have entire departments just for coding their own proprietary software and such, so I am kinda surprised that after I sent it back the first time and told them what it was doing that they would send back their "fixed" version and tell me that "this will work for you now" and it still didn't work... I mean, i'm just some regular guy who doenst know jack crap about this and have just managed to pick up what I do know about VBA from playing around with it over the years and following the mrexcel forum and utteraccess forums lol I mean, this isn't something new regarding the hiccups of gettng workbooks to run properly on 32bit vs 64bit... so like I said, its kinda surprising.
    Thanks for your help, FormR!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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