user-defined type not defined

[whburling]

hi

i am coding my first userform.

i began writing it in another workbook, but moved the userform into my personal.xlsb so that once i get it working it will be available to all my workbooks.

i then created a new workbook to test the code out. the workbook is attached.

The very first dim statement is failing with the code, "user-defined type not defined".

Sub AcquireData_ForNumberingGroupedCells()
Dim form As New UserForm_NumberingGroupedCells
.
.
.
End Sub

I have a feeling that the compiler is not finding the userform in Personal.xlsb but in the VBA project manager I can see the personal.xlsb file !

Please help me understand what i am doing incorrectly.

[Bernie Deitrick]

To load a form from your Personal.xlsb workbook, use a macro to call a macro in your Personal.xlsb

Please Login or Register  to view this content.

Wtih this code in your Personal.xlsb to open the form:

Please Login or Register  to view this content.

And all the code in the userform needs to address objects in the calling workbook by referencing

Workbooks(strName)

You may need to load the workbook name value into a local variable....

[whburling]

Thank you, Bernie...

[whburling]

Still encountering the error "user-defined type not defined"

attached actual code

summary:
Sub AcquireData_ForNumberingGroupedCells()
Dim form As New UserForm_NumberingGroupedCells
Application.Run "Personal.xlsb!UserForm_NumberingGroupedCells", ThisWorkbook.Name
End Sub

[Bernie Deitrick]

You don't declare the form - all that code is in the form in Personal.

So remove the line

Dim form As New UserForm_NumberingGroupedCells

[whburling]

Bernie,
I removed the Dim -
still not sure why i am putting the Dim line of code in form code.
This is my interpretation of what is happening when Dim line of code executes...
Code that wants to use the userform needs to create an instance of the form, UserForm_NumberingGroupedCells
Once the instance is created in memory, the code that uses the userform then can ask exel to show it (which also loads it)
Then the code that wants to use the userform waits for the user to interact with the form by clicking or filling in a textbox

if i take out the Dim line of code, as you suggested,
I then get an error
Cannot run the macro
'Personal.xlsb!UserForm_NumberingGroupedCells'
The macro may not be available in this workbook or all macros may be disabled.'

I can see the macro in the personal.xlsb file in the vba project manager....

Please continue to help
Thank you
bil

[Bernie Deitrick]

Make sure the macro in your Personal.xlsb is in a standard codemodule, not in the ThisWorkbook's codemodule or in any sheet's codemodule.

[whburling]

Good Morning Bernie !!!!
How are you doing today? Thank you for replying

please see the two attached photos

The first photo shows the code in a workbook that calls the userform and extracts the userform properties.

The second photo shows the location of the userForm. You can see it is IN Personal.xlsb but it is NOT in a std module.
I put it in the "FORMS" module of Personal.xlsb. Does it make sense that it be there?

mmmm can't see the pictures. let me try downloading them once again.

can't upload them. am now trying to figure out how to send lower resolution images on the assumption that the images are too large

[Bernie Deitrick]

You are not reading my instructions clearly. You are trying to run a userform, not a macro.

This is the macro, stored in a standard codemodule in your workbook - that calls another macro that resides in your personal.xlsb - Persoanl.xlsb is the file with the userform that you want to use: The macro in your personal.xlsb needs to be named "UserformCode"

Please Login or Register  to view this content.

This macro code in your Personal.xlsb - place it in Module1 - is what is used to actually open the form:

Please Login or Register  to view this content.

[whburling]

Hi Bernie!!!

I thought i had responded. so let me try again.

Clearly i am not hearing you. So I am pushing aside all my thoughts and code and just focusing on what i thought you are saying.


Essence:
There are THREE places code is placed in order to use a userform.

(1) The ANYWORKBOOK which wants user data through a userform
(2) The Personal.xlsb....Folder Modules
(3) The Personal.xlsb....Folder Forms

The ANYWORKBOOK contains code that calls a subroutine stored in Personal.xlsb->Modules->module1
The code in ANYWORKBOOK is something like, 'Application.Run "Personal.xlsb!UserformCode", ThisWorkbook.Name"


The code in Personal.xlsb located in Modules, module1 (for example) is the code that actually loads the userform into memory and shows it to the user

The code in Personal.xlsb located in Forms, (name of userform is placed here)....is teh code that captures user actions and places them into the userform object properties
that are accessible to ANYWORKBOOK. Methods also exist in this location. The methods could alter the raw input data or are accessible by ANYWORKBOOK.

Do I understand you?
bil

os

ps.
Thank you for hanging in there, Bernie

[whburling]

Bernie,
First....Thank you for all this time you have allocated to me. I am very grateful.

I have implemented what I believe is your advice. I have ATTACHED the actual current
code. In addition, Below i have written the changed code and included questions. At
the end of this blurb, I noted a compile error that is not associated with any of
your suggestions but is occurring in the form code itself.


THE CHANGES I IMPLEMENTED
In the workbook(JunkTestMacro.xlsm) that wishes to use
a Userform (UserForm_NumberingGroupedCells) stored in Personal.xlsb,
is code that calls a module(UserFormCode) also stored in Personal.xlsb which actually
runs the form code(UsesrForm_NumberingGroupedCells)


Sub AcquireData_ForNumberingGroupedCells()
Application.Run "Personal.xlsb!UserFormCode", ThisWorkbook.Name
End sub


In Personal.xlsb, in Module UserFormCode, I have placed the following code. Without
the declaration, “Dim form as UserForm_NumberingGroupedCells”. Without it, I can’t
understand how the code knows which form to show


Sub UserFormCode(Strname As String)
'Dim form As UserForm_NumberingGroupedCells
form.Show vbModal
'check of the user cancelled the userform
If form.Cancelled = True Then
MsgBox "The UserForm, NumberingGroupedCells, was cancelled"
End If

'cleanup
Unload form
Set form = Nothing
End Sub


THE COMPILER COMPLAINS with an error that is not in the above code but is in the
UserForm_NumberingGroupedCells. The compiler error is
“Member already exists in an object module from which this object module derives”

The error highlights a method in the form code which is:

Private Sub CommandButton_InputData()
Hide
End Sub

BOTTOM LINE
(1) Did i adopt your suggestions?
(2) Can you help me understand why i do not need the DIM statement you have singled out for me.
(3) Can you help me understand the compiler error. It does not make sense to me.

thank you very much.
bil