We help IT Professionals succeed at work.

Need Help With Running A Userform in A Different Workbook

Noah
Noah asked
on
100 Views
Last Modified: 2019-03-24
Greetings Experts,

I have two excel Macro enabled workbooks, however, I have a userform in one workbook called "PART 1 - REFRESH COPY & PASTE" and I need to run the userform in another workbook called "example".

Is it possible to make a VBA script to do this?

Any help is much appreciated :)
example.xlsm
PART-1---REFRESH-COPY---PASTE.xlsm
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Yes you can. Note the punctuation.

Sub YesYouCan()
' The test macro opens a userform
Application.Run "'C:\MyPath\MyWorkbook.xlsm'!Test" 
End Sub

Open in new window

NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
This code needs to placed into a new module in the workbook called "PART 1 - REFRESH COPY & PASTE" where the Userform is?
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
In the workbook 'PART-1---REFRESH-COPY---PASTE.xlsm' put this code in a standard module.
Sub OpenForm
    UserForm1.Show
End Sub

Open in new window


In the workbook 'Example.xlsm' put this code in a standard module.
Sub ShowForm()
    Application.Run "'PART-1---REFRESH-COPY---PASTE.xlsm'!OpenForm"
End Sub

Open in new window


Now if you run if both workbooks are opened you can run ShowForm from 'Example.xlsm' to open the form in 'PART-1---REFRESH-COPY---PASTE.xlsm'
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
If the two workbooks are named WB1 and WB2 then if the userform is in WB2 you would put my macro in WB1.
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
Hi, there seems to be a similar issue with both of your codes.
1.JPG2.JPG
My file path is "C:\Users\ESPZYONG\Documents" and my file name is "PART-1 REFRESH COPY & PASTE"


This is the current code
Sub Test()
Application.Run "'C:\Users\ESPZYONG\Documents\PART-1 REFRESH COPY & PASTE.xlsm'!Test"
End Sub

Open in new window

NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
There's no filepath in the code I posted.
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
@Norie I have tried again and the problem still persists
Capture.JPG
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
This error pops up the first time I run itCapture.JPG
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
The code I posted relies upon the workbook with the userform being open.

If it isn't you can use the code Martin suggested but you need to ensure you specify the correct path for the workbook.

PS Are the 2 workbooks located in the same folder?
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
Both workbooks are in the same folder and I made sure both were open
Analyst Assistant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
I have tried renaming and saving new files but nothing has worked so far. I have put the code in a module which is in Example.xlsm

I have also renamed and save the file with the userform to userform.xlsm

Sub ShowForm()
    Application.Run "'" & ThisWorkbook.Path & "\userform.xlsm'!OpenForm"
End Sub

Open in new window

NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
I had ensured that I the file was saved as Macro-enabled workbook and enabled macros before running
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
What code did you put in the workbook with the userform in it?
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
There is a single command button in the userform

Private Sub CommandButton1_Click()

Call Button1_Click

End Sub

Sub Button1_Click()
    Application.ScreenUpdating = False
    Call CopyPasteValuesInTab("Allocation")
    Call CopyPasteValuesInTab("By Ctry-EIN")
    Call CopyPasteValuesInTab("By Ctry-EMSB")
    Call CopyPasteValuesInTab("By Ctry-ETH")
    Call CopyPasteValuesInTab("By Ctry-EPC")
    Call CopyPasteValuesInTab("ESD Trf Qty")
    '...
    ActiveSheet.Calculate
    Application.ScreenUpdating = True
    
    MsgBox "Done!"
End Sub

Sub CopyPasteValuesInTab(ByVal SheetName As String)
    On Error GoTo EH
    Dim ws As Worksheet
    Set ws = Sheets(SheetName)
    ws.Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    Exit Sub
EH:
    Debug.Print Err.Number & ": " & Err.Description
    Application.CutCopyMode = False
End Sub

Open in new window

NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
You need code in a standard module to show the form.

Something like this, which is from my original post.
Sub OpenForm()
    UserForm1.Show
End Sub

Open in new window

That code goes in the workbook with the userform.
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
I have put in the code but it's not working, it keeps saying macros are not enabled or macros are not available.
Even when I try other variations of codes such as this one
Sub myMacro()
Workbooks("userform.xlsm").Activate
Application.Run ThisWorkbook.Name & "!OpenForm"
End Sub

Open in new window

NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Can you upload the workbooks again?
CERTIFIED EXPERT

Commented:
it keeps saying macros are not enabled or macros are not available.
you can add in the new location to the Trusted Location in Trust Center for where your Excel file (.xlsm) located

Untitled.jpg
and see if you now able to run macro by default?
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
Here there are
The userform: userform.xlsm
The data: data.xlsm
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
@Ryan Chong The error is still persisting, I don't think it had to do with trust issues as other macros in other file locations could work prior
CERTIFIED EXPERT

Commented:
just now it worked for me to call other workbook's macro by setting the Trust Location with the following codes:

Sub ShowForm()
    Application.Run "'" & ThisWorkbook.Path & "\userform.xlsm'!OpenForm"
End Sub

Open in new window


but now I try to remove the settings and do it again and now I can't reproduce the same result lol....

I got to re-try that again
CERTIFIED EXPERT

Commented:
it seems worked fine for me now, with exactly same settings I mentioned above.
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
@Ryan Chong It still doesn't work for me, I think I will try it on my other laptops
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
@Ryan Chong I have tried on another laptop but it shows the exact same problem
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
Hi everyone, I will be closing this thread and opening a new one to ask about my error since @Ryan Chong has stated that it works for him.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Application.Run "'C:\Users\ESPZYONG\Documents\PART-1 REFRESH COPY & PASTE.xlsm'!Test"
In the workbook that has the userform, is the procedure that opens it called 'Test? if not then change 'Test' to the procedure name.
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
@Martin Liss I have already tried both test and showform commands but it doesn't work
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I have already tried both test and showform commands but it doesn't work
What is the name of the procedure that opens your userform? Do you have one?
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
I think I have removed it previously. I have opened a new thread regarding this issue so let's continue it there :)
https://www.experts-exchange.com/questions/29140411/Error-With-Running-A-Userform-in-A-Different-Workbook.html

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.