How to reference an Excel UserForm from Access VBA

Question

CASE:

• I have created an Access database.
• Using Access-VBA I open an Excel workbook to do stuff.
• This workbook (xlWb) does complex calculations and also loads an Excel Userform (UserForm2) on open.
• So far everything OK. I open xlWb, UserForm2 loads, I do stuff.

GOAL:

• After this, I need to "refresh" Userform2.

• By the term "refresh" I mean either call a custom sub of this UserForm, or unload and reload UserForm2.

QUESTION:

• How can I can reference UserForm2 from my Access-VBA code?

WHAT I 'VE TRIED:

By searching I 've only found how to reference a UserForm from another workbook.

• The first suggestion was to use VBA.UserForms to get a loaded UserForm.

So I 've tried the following references: VBA.UserForms("UserForm2"), VBA.UserForms.Item("UserForm2"), VBA.UserForms(0), VBA.UserForms(1), all of which threw error: "subscript out of range", which implies that what I 'm writing is not a member of the collection.

• Another suggestion was to create a function that loads and unloads the object.

So I wrote inside an xlWb's module named Apps this:

Public Sub Refresh_UserForm()
    Unload Userform2
    Userform2.Show
End Sub

and in the access sub this:

Application.Run "'" & xlWb.Name & "'!Apps.Refresh_UserForm"

This throws a

Run-time error 2517 cannot find the procedure 'calc_8.4.xls'!Apps.Refresh_UserForm'

• The same error is also generated when I tried a 3rd similar suggestion to create a function that returns an instance of the object.

Every suggestion is very welcome thank you.

Answer 1

So this works for me:

---

Note: for demonstration purposes I created a xlsm file called "Map1.xlsm" with a userform called "UserForm1". Also, you'll need the "Microsoft Excel 16.0 Object Library" reference turned on.

---

First, as mentioned above I created an Excel file with the below macro in Module1:

1). To open the UserForm for the first time (and refresh):

Sub Refresh_UserForm()
    Unload UserForm1
    UserForm1.Show False
End Sub

---

Within Access-VBA I created the following two macros (based on this):

1). To open and use the Excel UserForm

Option Compare Database
Dim xlApp As Excel.Application, xlWB As Excel.Workbook

Sub mySub()
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open("C:\...Path...\Map1.xlsm") 'Specify Path
    xlApp.Visible = True
    xlApp.Run "Map1.xlsm!Refresh_UserForm"
End Sub

2). To Refresh the Excel UserForm

Sub Refrsh()
    Set xlApp = GetObject(, "Excel.Application")
    xlApp.Run "Map1.xlsm!Refresh_UserForm"
End Sub

---

Hopefully this helps