Unable to open the userform inside a XLAM add-in

[CraigsWorld]

Hi there, not sure what I am doing wrong here...

I have created an excel app that I have saved in .xlam format and placed in in my add-in folder. I have tried a few methods to access the userform in the add-in workbook when I open a new workbook (I have not tried to run code from the new workbook).

I have placed this code in a module and thisworkbook in the xlam workbook and have not been able to get the userform1 to open.

Please Login or Register  to view this content.

I have tried a Custom UI Builder and can get to the macro... but will not open the userform (creates run-time Error)!




The add-in is active and shows in the new workbooks (I might add that it is password protected).

What I would really like to do is add the macro to open the userform in the VBEditor toolbar (like MZ-Tools and VBE Editor) as the add-in creates controls for userforms to help userform design. I have been able to place a link on the workbook ribbon (not the desired placement) but it still doesn't open the userform in the xlam file.

Any idea what I am doing wrong?

Craig

[gmr4evr1]

Not sure if this will do it, but change the Sub title to

Please Login or Register  to view this content.

And place it in the ThisWorkbook module.

[CraigsWorld]

I placed the Userform1.show in the WorkBook_Open code of ThisWorkBook... the userform did open when I opened another workbook. This is not the desired result though, I would like the userform to only run when the user wants to open it.
Another issue with this is when the userform opened, it also opened and displayed the entire ??????.xlam workbook. When I closed the userform, the xlam file closed and I was back to book1.xlsx. I want the userform to open without distracting from the current workbook.

I am definitely going about this all wrong.

[gmr4evr1]

So you basically need a button or something that will show in the ribbon of any workbook that is being used, and when the button is clicked, the UserForm shows?

[xlnitwit]

Hi,

Can you provide the .xlam file for us to peruse?

[CraigsWorld]

Yes to gmr4evr1, a button that will open the userform from my add in file, preferably in the VBE menus, not on the workbook ribbon.

xlnitwit, here is a striped down version of the xlam file which has the userform to open, once opened... you can click 'Generate'. This will create a userform in a new workbook with a single control. This works as it should, except I don't want to open the userform until the user chooses (the reason a button on the menu is needed).

One thing the code does now that I don't like, is that it creates a new workbook to put the new userform in... I would like the code to add the userform to the current users workbook opened (the name of the workbook would be unknown).

Craig

[xlnitwit]

Craig,

Why do you wish to add the userform to the workbook rather than using a form in the add-in itself?

[CraigsWorld]

My add-in generates a series of controls that can be used by the user in their own projects. The add-in allows the user to preview what the controls will look like on their screen prior to generating the output. They can adjust all properties available to each control, the controls are named in a way (the user has some control over this) that it make it easy to loop through the controls later. Once they have created the controls to their liking, they can generate the output to a new userform that they can copy to their projects. In the finished project, they will be able to generate event code (if they choose not to use class modules) to go along with the controls created. I would prefer to generate the output controls to the users currently open project (not to a new workbook). If the UserForm was generated within the xlam add-in, they would only be able to see the control but have no access to using them manually and having the ability to manipulate them more if needed.

Hopefully this explains what I am trying to do... I have attached a couple of screen shots (Preview.png show the xlam file in action, the output.png file show what the user will receive when they click generate).

Craig

[xlnitwit]

The reason I ask is that it is easy enough, depending on the user's security settings, to add code and components to another workbook but providing a means to run the userform can be problematic if you wish to use the Ribbon.

[CraigsWorld]

Add-ins like MZ-Tools that I currently use add items to run in the VBE menu structure and with toolbars which some of the items open userforms. So it must be possible...

Craig

[xlnitwit]

Yes but they are COM add-ins, not workbooks.

Any button that you add to a commandbar in the VBE will require sinking CommandBarEvents. If your add-in changes a project, there will likely be a state loss which will unhook your button. It is possible to work around that using Application.OnTime to reset the sink but it would be much easier to do this type of work in a COM add-in.

[CraigsWorld]

I think I just found a way which expands on your last thoughts... funny, I was testing this as you replied.

I will test this with my add-in but I did do a quick test with a blank userform with success.

Here is where I found the examples from Chip Pearson's website


Craig