Need Help With Running A Userform in A Different Workbook

Yes you can. Note the punctuation.

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

Select allOpen in new window

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

In the workbook 'PART-1---REFRESH-COPY---PASTE.xlsm' put this code in a standard module.

Sub OpenForm
    UserForm1.Show
End Sub

Select allOpen 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

Select allOpen 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'

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

Hi, there seems to be a similar issue with both of your codes.

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

Select allOpen in new window

There's no filepath in the code I posted.

@Norie I have tried again and the problem still persists

This error pops up the first time I run it

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?

Both workbooks are in the same folder and I made sure both were open

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

Select allOpen in new window

I had ensured that I the file was saved as Macro-enabled workbook and enabled macros before running

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

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

Select allOpen in new window

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

Select allOpen in new window

That code goes in the workbook with the userform.

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

Select allOpen in new window

Can you upload the workbooks again?

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


and see if you now able to run macro by default?

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

@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

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

Select allOpen 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

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

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

@Ryan Chong I have tried on another laptop but it shows the exact same problem

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.

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.

@Martin Liss I have already tried both test and showform commands but it doesn't work

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?

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