For Macro - Specify Userform Workbook

**rokuk** · 11-26-2007, 10:29 PM

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:

Excel.ActiveWorkbook.UserForm1.Show

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

**Gabor** · 11-27-2007, 12:25 AM

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

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

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

Private Sub LaunchMyUserForm1()
UserForm1.Show
End Sub

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

**royUK** · 11-27-2007, 02:29 AM

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** · 11-27-2007, 02:42 PM

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:

'C:\Documents and Settings\Desktop\EntryMaster.xls'!OpenAddUpdateForm

instead of from EntryVersionA.xls.

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

'C:\Documents and Settings\Desktop\EntryVersionA.xls'!OpenAddUpdateForm

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:

Excel.ActiveWorkbook!OpenAddUpdateForm

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** · 11-27-2007, 05:14 PM

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