Avatar of Noah
NoahFlag for Singapore asked on

Need Help With Running A Userform in A Different Workbook

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
VBA

Avatar of undefined
Last Comment
Noah

8/22/2022 - Mon
Martin Liss

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

ASKER
Noah

This code needs to placed into a new module in the workbook called "PART 1 - REFRESH COPY & PASTE" where the Userform is?
Norie

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'
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Martin Liss

If the two workbooks are named WB1 and WB2 then if the userform is in WB2 you would put my macro in WB1.
ASKER
Noah

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

Norie

There's no filepath in the code I posted.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Noah

@Norie I have tried again and the problem still persists
Capture.JPG
ASKER
Noah

This error pops up the first time I run itCapture.JPG
Norie

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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Noah

Both workbooks are in the same folder and I made sure both were open
ASKER CERTIFIED SOLUTION
Norie

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Noah

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

ASKER
Noah

I had ensured that I the file was saved as Macro-enabled workbook and enabled macros before running
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Norie

What code did you put in the workbook with the userform in it?
ASKER
Noah

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

Norie

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
Noah

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

Norie

Can you upload the workbooks again?
Ryan Chong

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Noah

Here there are
The userform: userform.xlsm
The data: data.xlsm
ASKER
Noah

@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
Ryan Chong

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Ryan Chong

it seems worked fine for me now, with exactly same settings I mentioned above.
ASKER
Noah

@Ryan Chong It still doesn't work for me, I think I will try it on my other laptops
ASKER
Noah

@Ryan Chong I have tried on another laptop but it shows the exact same problem
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Noah

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 Liss

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.
ASKER
Noah

@Martin Liss I have already tried both test and showform commands but it doesn't work
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Martin Liss

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?
ASKER
Noah

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