Userform called in other workbook interrupts the procedure.

[davout]

Hi All,
Usually using search engine i'm able to find a solution to my problems, but now i have no idea how to solve this issue. Namely, i have a procedure that opens workbooks from a list and then searchs for a phrase given by user. If nothing is found it closes workbook and opens next one. If phrase is found it highlights it on workbook. I wanted to give user some options when phrase is found: to search further in next workbook and to search further, but leaving current workbook open. Here my misery starts. The only way i made up to perform it was to use Userform and code: userform.ActiveControl.Name. But it seems that Userform called in other workbook than one where the module is, interrupts somehow the procedure. When workbook, where the userform was used, closes the procedure doesn't go on. Evidently Userform is the issue here as without it, macro works.
Do you have an idea why this happens? Maybe you have some other way to perform described actions? Please see my code below (i simplified it a bit to make it easier to read):

Please Login or Register  to view this content.

Thanks in advance,
Davout

[LJMetzger]

Hi Davout,

Your post brings up two very interesting concepts:
a. UserForm 'ActiveControl'
b. Referencing a UserForm in a different Workbook

I couldn't get the Macro to fail at the line you had problems with:

Please Login or Register  to view this content.

I mad a few small changes; the Macro seemed to work ok for me.

a. Use of 'Option Explicit':
To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

b. 'Set c = .FindNext' in the 'leave' code should probably be 'Set c = Range(firstAddress)'. The 'FindNext' logic did not seem to exit unless you were at the last item.

c. The UserForm code probably did not need any changing. I added the 'myUserForm' object for future reference for when you REALLY want to reference a UserForm in a different file.

d. Cleared the object pointers at the end of the Macro. It is debatable whether this is necessary or not.

See the attached sample file, and code that follows if others are interested.

Lewis

UserForm Code:

Please Login or Register  to view this content.

Ordinary Module Code:

Please Login or Register  to view this content.

[davout]

Hi Lewis,

Thank you that you devoted your time and took into my problem.

Unfortunately, the changes you provided cause some errors, atleast at my PC. Firstly, there was an error "Method 'VBProject' of object '_Workbook' failed"
on line "Set myUserForm = ThisWorkbook.VBProject.VBComponents("UserForm1")", though i enabled check box "Trust Access to the VBA project object model" in excel options and it solved this error.
Nevertheless, now appears different error - 438, "Object doesn't supporrt this property or method" on line "myUserForm.Label1.Caption = "There are...", so i guess that something related to userform. Any ideas?

Regarding point B you proposed, I simplified code. Normally button "leave" will be shown after last match found, therefore there is a "FindNext" there.

Thank You,
Davout

[LJMetzger]

"Set myUserForm = ThisWorkbook.VBProject.VBComponents("UserForm1") problem"

I thought it might required a library reference on your part. The code should work fine (worked fine on my machine) with the old references intact (just using Userform1 instead of the new 'myUserForm'.

Nevertheless, now appears different error - 438

This error will probably go away, if you revert to the original 'UserForm1' code as stated above.

-----------------

Unfortunately, I've done as much as I can with the information I have. If you upload a sample workbook, I might be able to figure out where the -438 error comes from.

Lewis

[davout]

I made suggested changes and your procedure worked. I compared my code with your and i didn't notice much difference and my macro still didn't want to work. Finally i found what caused my issue, it was that line in userform code:

Please Login or Register  to view this content.

it was the code i found on internet which allows to end procedure by clicking on "X" button. When i removed it, procedure works. Hard to say for me why it caused such troubles.

Thank you Lewis for uploading your version. Otherwise we wouldn't be able to solve this.

I guess my case can be deemed as solved.

Once again, thanks!

Regards,
Davout

[LJMetzger]

I'm glad it worked out for you. In the future, you'll probably get better and quicker responses if you upload a sample workbook that exhibits the problem you are having.

Good luck, and please feel free to ask if you have any more problems.

Lewis

[coolblue]

@LJMetzger is there a reason you didn't do like this:

Please Login or Register  to view this content.

Sent from my iPad using Tapatalk

[LJMetzger]

Hi Cool,

There's a very good reason. I didn't know about the construction:

Please Login or Register  to view this content.

I'm an implementer, and not much of an original thinker. When I did an Internet search for the problem of making a UserForm accessible across workbooks, and came up with a solution that I tried that worked, I added that solution to my bag of tricks.

Thanks for the new trick.

Lewis

[coolblue]

No problem Lewis.

Actually, its a bit more than just a single trick, its more like a paradigm shift - or it was for me anyway...
If you realise that the the things you define in the Forms section in VBE are not actually UserForms, but are in fact Class Modules, then it opens up a lot of possibilities.
For example, you can always have a direct reference to them and you can use that reference to query public properties and call Public methods on the Form.
You can also add Public getter and setter properties to the Form Class...
You can also invoke multiple instances of the same class and keep track of them by reference...

For example,
In a UserForm Module...

Please Login or Register  to view this content.

In a standard Module...

Please Login or Register  to view this content.

...and thats just scratching the surface.