How to reference an Excel UserForm from Access VBA

2
votes

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.

2
excelvbams-accessuserform
Don't use underscores in method names! Application.Run "'" & xlWb.Name & "'!Apps.RefreshUserForm"will work. – ComputerVersteher
I 've tried this first but there was no change at all. Thank you though. – dimitris
Strange. Now it works for me to with underscore, but it stopped working as I added one to function. Never the less, don't use underscores in methods as at least when you use interfaces, it fails see rubberduckvba.wordpress.com/2019/03/27/…. – ComputerVersteher

2 Answers

1
votes

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

0
votes

Please try the following:

In userform2, add a "Refresh" button and the following procedure:

Private Sub CommandButton_Refresh_Click()
    Me.Repaint
End Sub

If you have a UserForm_Initialize sub and UserForm_Terminate sub , you can include the procedures to simulate the unload-reload action.

Private Sub CommandButton_Refresh_Click()
    Call UserForm_Terminate
    Call UserForm_Initialize
    Me.Repaint
End Sub

If you need to unload the form and call it back later manually, then you will have to call it back from the actual loaded Excel window (e.g. with a form Button on the worksheet), but cannot be from Access or the xlWb hook.