compile error- "user-defined type not defined"

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
457
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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 :
23locuu.jpg



Here is the code that its tripping on and causing the error:
2mee4pg.jpg
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi, what version of Excel are you using? As there is no code in that sub, can't you just delete it?
 
Upvote 0
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"

sbrae8.jpg


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:
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top