For Macro - Specify Userform Workbook

[rokuk]

Hi there,

I have several versions of a workbook I am working with, so I have made several copies of the same .XLS file and renamed them. They all have the same Userforms in them, and I have a button on my worksheets that runs a macro "UserForm1.Show"

It works, but it tries to open the UserForm1 from the original .XLS file instead of using the UserForm1 from the version of the .XLS file I already have open. How can I specify a path / force the macro to open the UserForm1 from my current open workbook without having to load the original?

Example: My first XLS is called EntryMaster, and one of my other versions is EntryVersionA. When I try to open my UserForm1 from EntryVersionA, it attempts to open the UserForm1 from EntryMaster instead...

I've tried something like this in the macro:

Please Login or Register  to view this content.

but this is the wrong syntax for this. Anyone have any idea to stop this behaviour? Thanks!

[Gabor]

It should work with either a CommandButton1, with the macro on the worksheet module:

Please Login or Register  to view this content.

or with a macro on a normal module assigned to an object, e.g a Textbox :

Please Login or Register  to view this content.

With a Private Sub it should attempt to open the UF from the workbook.

[royUK]

Your line of code should open the userform from the workbook containing it. I think you need to attach the workbook for someone to check.

[rokuk]

Ahh... I've found the problem, but haven't found a way around it.

The macro is fine, but on the custom menu ("Assign Macro" dialog box) I have it launching the macro from:

Please Login or Register  to view this content.

instead of from EntryVersionA.xls.


In order to assign the correct workbook (.XLS) I could manually change this to say:

Please Login or Register  to view this content.

but this would require me to do this manually for each workbook.


Is there a way to change this to say something like "use the macro OpenAddUpdateForm from the current workbook" instead of having to specify the .XLS file? In VBA terms, something that might work akin to:

Please Login or Register  to view this content.

that would allow for a dynamic .XLS filename?



Ideally each file would be able to work independent from any other files. I just realized this may be related to a question I had before but I didn't get a working answer to. I think someone had mentioned using a method requiring a secondary file containing personal / custom macros that would need to tag along with the workbook, but I'd like to keep everything confined to one file if at all possible, as the .XLS files will be circulated to people who are not technical and telling them anything other than "just open YourVersion.XLS" file may be starting to stretch things for them.


Thanks for the feedback so far

[Gabor]

Not the best way to launch the macro.
Do not use custom menu bar button !
Use an object, e.g. a TextBox on the worksheet and assign the macro to this object.
Then it will launch the userform from the actual workbook.
By this method all your workbooks will be independently operational at every user without any tricks on the customized menu or in personal.xls.
The other way is to use a CommandButton, which is a so called activecontrol, as I wrote before.
Gabor

[royUK]

Create an addin containing all the code and userform

[rokuk]

very helpful, thank you again.

I was originally trying to do it from a custom menu item because I thought it looked more professional and also would not require any space on the spreadsheet for a button, but it not working correctly would be far worse = )

I will also look into add-in's and learn more about that as well as an option. Thank you for the suggestions!

[Gabor]

Custom menu item is OK on your pc only...
Believe me the best solution is a simple textbox and the macro is assigned to it.
Reason:
This goes with the workbook, so the users do not have to apply any magic, just activate macros when opening the file.
The object can be placed on the heading of your worksheet so anyone can launch the macro easy.
I use this method very fequently, so functionality is guaranteed.
Downside of an add-in it has to be activated per every user. So, in this case not effective.
If you zip a file to your post I can set that up no worries.
Cheers, Gabor