• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Re: open user Form in workbook1 from workbook2

D

dariusd7

New Member
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?
 

Attachments

  • UserformExample.xlsm
    39.8 KB · Views: 4
  • FSO Open Report.xlsx.xlsm
    47.1 KB · Views: 3
Need to change the strings/names to fit your setup, but here's the syntax you need:
Code: 
'Give the full address showing VBE how to find the userform
'If workbook is closed, will need to include full folder
'address as well
Workbooks("Book1").VBProject.VBComponents("UserForm1").Activate
 
Luke M said:
Need to change the strings/names to fit your setup, but here's the syntax you need:
Code: 
'Give the full address showing VBE how to find the userform
'If workbook is closed, will need to include full folder
'address as well
Workbooks("Book1").VBProject.VBComponents("UserForm1").Activate
Click to expand...


Ok so am I putting this code in the command-button event?

Code: 
Workbooks("UserformExample.xlsm").VBProject.VBComponents("frmUsrDataSheet").Activate

Also the full address would that be entered like this

Workbooks(C:\Users\ddempsey\Documents\UserformExample.xlsm").VBProject
 
Yes, would be the command button event. You should have some sort of UserForm_Activate code setup then to handle whatever it is you want to do when you go back to it.
 
dariusd7 said:
Ok so am I putting this code in the command-button event?

Code: 
Workbooks("UserformExample.xlsm").VBProject.VBComponents("frmUsrDataSheet").Activate

Also the full address would that be entered like this

Workbooks(C:\Users\ddempsey\Documents\UserformExample.xlsm").VBProject
Click to expand...


Ok so would I enter the full address as such?

Workbooks(C:\Users\ddempsey\Documents\UserformExample.xlsm").VBProject
 
Don't forget the double quotation at beginning, but yes, if the UserformExample workbook is closed, will need full path:
Code: 
Workbooks("C:\Users\ddempsey\Documents\UserformExample.xlsm").VBProject.VBComponents("frmUsrDataSheet").Activate
 
Luke M said:
Don't forget the double quotation at beginning, but yes, if the UserformExample workbook is closed, will need full path:
Code: 
Workbooks("C:\Users\ddempsey\Documents\UserformExample.xlsm").VBProject.VBComponents("frmUsrDataSheet").Activate
Click to expand...

Ok thanks will give this a try, and let you know how it goes.
 
dariusd7 said:
Ok thanks will give this a try, and let you know how it goes.
Click to expand...

Ok I am getting subscript out of Range era.

Here is the code

Code: 
Private Sub MySub()

Workbooks("C:\Users\ddempsey\Documents\UserformExample.xlsm").VBProject.VBComponents("frmUserformExample").Activate

'Application.Run "UserformExample.xlsm!ShowDataSheetForm"
End Sub
 
dariusd7 said:
Ok I am getting subscript out of Range era.

Here is the code

Code: 
Private Sub MySub()

Workbooks("C:\Users\ddempsey\Documents\UserformExample.xlsm").VBProject.VBComponents("frmUserformExample").Activate

'Application.Run "UserformExample.xlsm!ShowDataSheetForm"
End Sub
Click to expand...

and I tried
Code: 
Private Sub MySub()

Workbooks("C:\Users\ddempsey\Documents\UserformExample.xlsm").VBProject.VBComponents("frmUserDataSheet").Activate

'Application.Run "UserformExample.xlsm!ShowDataSheetForm"
End Sub
 
issue solved.
Re: How do you call a user-form from a different workbook.

quote_icon.png
Originally Posted by royUK
In the other workbook add rthis code

VB:
Private Sub MySub()

' from other excel file
Run "UserformExample.xlsm!ShowDataSheetForm"

End Sub


In the userform workbook you have two macros called ShowDataSheetForm, delete one of them
 
You must log in or register to reply here.
Back
Top