open user Form in workbook1 from workbook2

Basically I have a user form that opens up another workbook file. and then hide the user form. Now what I owuld like to do is have a button on the worksheet that will recall the userform and close the workbook.

The user form is frmUsreDataSheet It is in VBAProject(UserformExample.xlsm)

The file or workbook that gets opened is VBAProject(FSO Open Report.xlsx)

I searched the web and there seems to be a lot of conflict about whether it can be done. Some suggestion you create a reference in the FBAProject that houses the userform, and then create a code that references or something like that.

Help Please.

Thanks.

Oh and not sure if I should be using an Activex button or a form control button. but the button will exist on the worksheet.

Ok Izandol here is the code I came up with, but it doesn't work.

Private Sub CommandButton2_Click()
Application.Run " btnOpenDatasheetMenu_Click()frmUsrDataSheet.Show "
End Sub

obviously something is wrong.

Originally Posted by Izandol

In which module is btnOpenDatasheetMenu_Click routine?

It is in UserformExample.xlsm

Originally Posted by Izandol

Which module (Module1, or worksheet module, or...?), not which workbook?

It's not in any module.. I'm sorry but i click on everything in the VBa the code does not exist in a module it exist within the form..

I attached a picture of the vba with the code for that button opened.

I found these instruction for running code in another workbook, but I do not know where the application.run is located.


Go to the workbook that holds the routine you want to run and note the exact name of the macro/function, and any arguements required.
Copy the appropriate sub above.
In the Workbook that you want to trigger your macro from (not the one that holds the code you want to execute)...
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Choose Insert -> Module.
Paste code into the right pane.
Modify the NameOfFile and PathToFile variables to the appropriate workbook name and path of the file that holds the code you want to run.
Update the Application.run line to list your target macro's name, and arguments (If necessary.)
Press Alt + Q to close the VBE.
Save workbook before any other changes.

Ok Izandal I can create the module like you suggested, and are you saying to put the application.run code into the buttonclick command?

Ok I put the code into new module in thworkbook where the fomr exist, then I put the application.run code into the command button in the workbook where the button exist.

Now I am getting the following error run time error 1004 Method "Run" of object"_Application" failed.

I click the debug on the error code. I attached a picture of the code it highlighted.

It highlighted the application.run code...

How would it know "when" to reappear? Because the user will be working in the workbook or worksheet and when they are finished they would click button to get back to form. So how would VBA/Windows/Excel know when that is?

Not sure how that would work.. How owuld it know when to open the form?

Originally Posted by Izandol

Then you must create routine in normal module like:

Please Login or Register  to view this content.

then you may use:

Please Login or Register  to view this content.

Ok I just noticed in the error it has the following path ..c:\users\ddempsey\Documents\userformexample.xlsm.. This is not the correct path, why is it looking here for the form.

The form is in the following path. C:\Users\ddempsey\Desktop\notes

should i move it to the path noted in the error?

It worked when i changed the path to the path in the error box. Oh well this is close enough. I'm going to close this discussion. Its getting too long.