User-defined type error

rjplante

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
574
Office Version
  1. 365
Platform
  1. Windows
I have a workbook that I have been using and I am running into an error when I run the macro that executes on close. I get the following error: "User-defined type not defined." My VBA window appears when this error pops up and then when I click on the ok button, it returns me to the excel window of my workbook. When I go to the VBA window again no, code window is displayed and no code line is highlighted which would indicate where I should look to repair my code.

What can I do to remedy this issue?

Thanks

Robert
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Robert

There might be a MISSING reference somewhere.

Have a look for that under Tools>Reference... in the workbook that has the problem.
 
Upvote 0
Here are all the macros that I believe could be causing the error.

Thisworkbook code:
<code>
Code: 
Private Declare Function FindWindowEx& Lib "User32" Alias "FindWindowExA" _
(ByVal hWnd1&, ByVal hWnd2&, ByVal lpsz1$, ByVal lpsz2$)
Private Declare Function ShowWindow& Lib "User32" (ByVal hWnd&, ByVal nCmdShow&)
Private Sub Workbook_Open()

Application.ScreenUpdating = False

On Error Resume Next
For Each s In Worksheets
s.Visible = False
If s.Name = "Intro Page" Then s.Visible = True
Next s

Sheets("Intro Page").Activate
Sheets("Intro Page").Range("W42").Value = "no"
Range("C6").Select

'disable access to VBA editor
Application.OnKey "%{F11}", "Access_VBA_Editor"
Application.OnKey "%{F8}", "Access_Macro_List"

Application.ScreenUpdating = True

Call TaskBar_Hide

Call GetStarted


If Range("T28").Value = "BLANK" Then
    Range("R15").ClearContents
    Range("R17").ClearContents
    Range("AR15").ClearContents
    
    UserForm1.Show

Else

    Range("R15").ClearContents
    Range("R17").ClearContents
    Range("AR15").ClearContents
    
    UserForm5.Show

End If

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Sheets("Intro Page").Range("W42").Value = "no" Then
     Cancel = True
     
ElseIf Sheets("Intro Page").Range("W42").Value = "yes" Then

     Application.OnKey "%{F11}", ""
     Application.OnKey "%{F8}", ""
     Cancel = False
     
End If

End Sub

<code>==================================================================

Close workbook command macro
<code>
Code: 
Sub EXIT_Workbook()

Sheets("Intro Page").Range("W42").Value = "yes"
'Application.ScreenUpdating = False


i = MsgBox("This will save and close the QC Processing Workbook!" & vbCrLf & "Are you ready to leave?", vbYesNo + vbExclamation + vbDefaultButton2)

If i = 7 Then 'NO
Sheets("Intro Page").Range("W42").Value = "no"
If Sheets("Intro Page").Range("R15").Value = "" Or Sheets("Intro Page").Range("R17").Value = "" Then
UserForm5.Show
Else
    Exit Sub
End If

ElseIf i = 6 Then 'YES

'   clear username field and password field
Range("R15").ClearContents
Range("R17").ClearContents

'   log the time out
Sheets("USER HISTORY").Activate
ActiveSheet.Unprotect Password:="bioe1025"

    Range("B1").End(xlDown).Offset(0, 3).Value = Time

ActiveSheet.Protect Password:="bioe1025"
ActiveWorkbook.Save

Call TaskBar_Show

Call FinishClose

ActiveWorkbook.Close

End If

'Application.ScreenUpdating = True

End Sub

==========================================================================================<code>

Userform1
<code>
Code: 
 Private Sub UserForm_QueryClose(Cancel As Integer, _
  CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please select the CANCEL button on the form!"
  End If
End Sub

Private Sub CommandButton1_Click()

UserForm1.Hide

If Range("R15").Value = "" Then
    MsgBox "No user selected, please try again.", vbCritical, "ATTENTION!"

    UserForm1.Show
End If

'   ** SAVE THE FILE **

Dim sPath As String
     
'Specify directory
sPath = "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\" & Range("T21").Value
     
'Test if directory exists
If Dir(sPath, vbDirectory) = "" Then
    
    MkDir sPath
    
    ChDir "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\" & Range("T21").Value
    
    'Format(Month(today), "MM") & Format(Day(today), "DD") & Format(Year(today), "YYYY")
    
    ActiveWorkbook.SaveAs Filename:=(Range("T20").Value & " LOT# " & Range("T21").Value & " RM# " & Range("T23").Value & ".xls")
    MsgBox ("File was saved with the following name: " & Range("T20").Value & "LOT#" & Range("T21").Value & " RM#" & Range("T23").Value & ".xls")
    
Else
    
'Test if file exists
  
    Dim sFile As String
    
' Name file
    sFile = "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\" & Range("T20").Value & "LOT#" & Range("T21").Value & " RM#" & Range("T23").Value & ".xls"

    If Dir(sFile) <> "" Then

        Activeworbook.Save
    End If
End If
    
Range("B4").Select

End Sub

Private Sub CommandButton2_Click()

UserForm1.Hide

Call EXIT_Workbook

End Sub

Private Sub CommandButton3_Click()

Sheets("Intro Page").Range("AR15").ClearContents
Sheets("Intro Page").Range("R17").ClearContents

UserForm1.Hide

UserForm2.Show

End Sub

<code>===================================================================================

Userform2
<code></code></code></code></code></code></code></code><code><code><code><code><code>
Code: 
</code></code></code></code></code>
<code><code><code><code><code><code><code>   Option Explicit
Private Sub UserForm_QueryClose(Cancel As Integer, _
  CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please select the CANCEL button on the form!"
  End If
End Sub

Private Sub CommandButton1_Click()

UserForm2.Hide

If Range("Q5").Value = True Then
    UserForm3.Show
Else

    MsgBox "Number entered is not correct, please try again.", vbCritical, "ATTENTION!"
    UserForm2.Show

End If

End Sub

Private Sub CommandButton2_Click()
UserForm2.Hide

Sheets("Intro Page").Range("R17").ClearContents

UserForm5.Show
End Sub
</code></code></code></code></code></code></code><code><code><code><code><code>
</code></code></code></code></code>
<code><code><code><code><code><code><code> <code>
=====================================================================================================

Userform3
<code>
</code></code></code></code></code></code></code></code></code><code><code><code><code><code>
Code: 
</code></code></code></code></code>
<code><code><code><code><code><code><code><code><code>   Option Explicit
Private Sub UserForm_QueryClose(Cancel As Integer, _
  CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please select the CANCEL button on the form!"
  End If
End Sub

Private Sub CommandButton1_Click()

If Sheets("Intro Page").Range("R17").Value = Sheets("Intro Page").Range("R18").Value Then

'Loop to find row
    UserForm5.Hide

    Sheets("PASSWORD ENTRY").Range("D2").Select

    Do

    If ActiveCell.Value <> Range("J15").Value Then
        Selection.Offset(1, 0).Select
    End If

    Loop Until ActiveCell.Value = Range("J15").Value

    ActiveCell.Offset(0, 3).Select
    ActiveCell.ClearContents
    ActiveCell.Value = ActiveCell.Offset(0, -2).Value & Sheets("Intro Page").Range("R17").Value

    MsgBox "Password has been changed to your initials plus the 5 digit number you entered.", vbExclamation, "ATTENTION!"

End If

UserForm5.Show

End Sub

Private Sub CommandButton2_Click()
UserForm3.Hide
UserForm5.Show
End Sub
</code></code></code></code></code></code></code></code></code><code><code><code><code><code>

</code></code></code></code></code><code><code><code><code><code><code><code><code><code> <code>================================================================================================

Userform5
<code>
</code></code></code></code></code></code></code></code></code></code></code><code><code><code><code><code>
Code: 
</code></code></code></code></code>
<code><code><code><code><code><code><code><code><code><code><code>   Private Sub UserForm_QueryClose(Cancel As Integer, _
  CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please select a user and enter the correct password." & vbCrLf & "Click the ""EXIT"" button to leave the workbook."
End If
End Sub
Private Sub CommandButton1_Click()

UserForm5.Hide

If Range("R15").Value = "" Then
    MsgBox "No user selected, please try again.", vbCritical, "ATTENTION!"

    UserForm5.Show
End If

If Sheets("PASSWORD ENTRY").Range("M15").Value = False Then

    MsgBox "Password entered in incorrect, please try again.", vbCritical, "ATTENTION!"

    Sheets("Intro Page").Range("R17").ClearContents
    
    UserForm5.Show
End If

Sheets("USER HISTORY").Activate
ActiveSheet.Unprotect Password:="bioe1025"

If Range("B2").Value = "" Then
    Range("B2").Value = Sheets("Intro Page").Range("R15").Value
    ActiveCell.Offset(0, 1).Select
    Selection.Value = Date
    Selection.Offset(0, 1).Select
    Selection.Value = Time

Else
    Range("B1").End(xlDown).Offset(1, 0).Select
    Selection.Value = Sheets("Intro Page").Range("R15").Value
    ActiveCell.Offset(0, 1).Select
    Selection.Value = Date
    Selection.Offset(0, 1).Select
    Selection.Value = Time

End If

ActiveSheet.Protect Password:="bioe1025"

End Sub

Private Sub CommandButton2_Click()

UserForm5.Hide

UserForm2.Show

End Sub

Private Sub CommandButton3_Click()

UserForm5.Hide

Call EXIT_Workbook

End Sub
</code></code></code></code></code></code></code></code></code></code></code><code><code><code><code><code>
</code></code></code></code></code>
<code><code><code><code><code><code><code><code><code><code><code>
<code></code></code></code></code></code></code></code></code></code></code></code></code>
 
Last edited:
Upvote 0
In the VBE, Tools > Options > General, tick Break in Class Modules, then try again.
 
Upvote 0
Robert

I can't see anything that would cause the error you describe.

I could have checked further by copying the code to VBA but without code tags that's pretty difficult.

It's pretty hard telling when the code from one module ends and another starts.

Anyway, I take it you don't get any debug option?

You could try going to Tools>Options... and selecting Break on all Errorrs on the General tab.

Then, fingers crossed, you might get a debug option next time the error happens.:)
 
Upvote 0
I notice one interesting tidbit is that when I run the compile VBA project it hangs and does not complete the compile. After a while I can hit the stop button and get it back, but it takes a while and I don't know why it hangs up.

BTW how do I post w code tags I place the following around my code:

"["code"]"

my macro code here

"["/code"]"

Is there more I should be doing to get it to display correctly?

I still have no debug error Highlighted.

I have found this on the web:

=====================
Problem

An Excel VBA subroutine that involves interaction with the Windows Clipboard fails to run with the error: “Compile error: User-defined type not defined.” The line of code highlighted by Excel as being the cause of the error includes a reference to the “DataObject” object type.
Solution

(Tested on my machine with Excel 2003 SP3)
In the Microsoft Visual Basic editor:

  • Stop debugging by clicking the “Stop” button on the toolbar (if debugging isn’t already stopped).
  • Tools menu | References
  • In the list of available references, find “Microsoft Forms 2.0 Object Library”, check it, and click OK. (For me, this was the 2nd unchecked item from the top of the list; it wasn’t sorted alphabetically in the list like most of the rest of the listed items.)
  • Run the macro again.
That’s it! Hope this helps!


==================================


but when I try to deselect this reference it says it cannot perform that action because it is in use.


Still lost and so confused.


Robert
 
Upvote 0
Robert

Have you tried anything that's been suggested?

eg checked for missing references
 
Upvote 0
It's at the top of the references, it's checked, and it starts with MISSING.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
User-defined type not defined when clicking on a Hyperlink that leads to a hidden tab
Replies
5
Views
202
Micron
M
T
  • Question Question
Macros disabled from spreadsheet copy
Replies
2
Views
208
travellerva
T
O
  • Solved
Code works EXCEPT when code is running??
Replies
6
Views
378
OxfordCurmudgeon
O
F
  • Question Question
Making a UserForm behave as a Tool Window for another process
Replies
3
Views
342
fitzmorrispr
F
S
  • Solved
VBA - User defined type not defined error
Replies
4
Views
561
ssh99
S

Forum statistics

Threads
1,224,552
Messages
6,179,484
Members
452,917
Latest member
MrsMSalt

Share this page

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