How to call a user form from a different workbook

dariusd7

New member
Joined
Feb 10, 2014
Messages
2
Reaction score
0
Points
0
Basically I would like the user to be able to call the user Menu form that exist in a different workbook from the command-button that exist on the worksheet that is open.

What happens is the user open a User menu form, from that form they then access a file that exist in a different workbook. When that file open the form closes. When they want to go back to the user-menu form they should be able to click on a command button that is on the worksheet that will then reopen the user-menu and close the current workbook that they are in.

example

the menu form is called formUserDataSheet, which is accessed from a parent user Menu form called frmUserFormExample in the workbook called userformExample.
The worksheet that is opened from the formuserDataSheet is called FSO Open Report.
the command button on the worksheet right now is just called command button


I am so close but not quite there. Currently If I open up the FSO Open Report directly and then click on the command-button on the worksheet, It will take me to the User-Menu form That exist in userFormExample.xlsx. However if I access the FSO Oen Report worksheet through the user-Menu form , and then click on the command button to get back to the user-form it does not work, because the worksheet will open in the workbook(userformExample) that the user Menu form exist in. So I'm thinking I have to some how actually put the command button event code in the same work book as the form..

Here is the code I currently have running in the command-button event in the FSO Open Report.

Code:
Private Sub MySub() 
  Application.Run "UserformExample.xlsm!ShowDataSheetForm"
End Sub
and to access the form in the userformExample workbook I have the following code in a VBA module in the userFormExample workbook.

Code:
Public Sub ShowDataSheetForm()
frmUsrDataSheet.Show
End Sub


Apparently since the worksheet will open in the userformExample workbook. I need to put the command-button event code inside the userformExample workbook.


Any suggestions?


The path to access the FSO Open Report is
strFileName = "C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\" & "\" & myfolder & "\" & myfile & ".xlsx" & ".xlsm"

where myfolder = month selected by user (in this case December) and myfile = file selected by user, (in this case FSO Open Report)
 

Attachments

  • FSO Open Report.xlsx.xlsm
    47.1 KB · Views: 141
  • UserformExample.xlsm
    39.8 KB · Views: 158
Last edited:

dariusd7

New member
Joined
Feb 10, 2014
Messages
2
Reaction score
0
Points
0
Cross-posted here:
Please read the forum rules about cross-posting. :)

I looked under FAQ and also searched Cross Posting, but I could not find anything about cross posting. so can you please tell me what it is.

Thanks

O)k wait.. Is this forum connected to Excelforum.com? is that what you mean
 
Top