Launch Excel and Reference UserForm object from MS Access

Question

I have an MS Access database application here and I'd like to launch an Excel file using VBA and populate fields on a UserForm object within this file with default values. I'm struggling to find the syntax I can use to reference the UserForm object from outside the Excel application.

Hopefully this makes sense. Here's my simple code so far.

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

Set xlApp = CreateObject("Excel.Application")
Set xlWB as xlApp.Workbooks.Open("[My file path goes here]")

xlApp.Visible = True

What I'm attempting to do at this point is access a UserForm object within this file named UserForm1 that contains a textbox called TextBox1 and set it's value.

I can do this using VBA within Excel to UserForm1.TextBox1 = "Test" and this works. Trying to do this externally from MS Access if I can. Am I missing something obvious here?

Answer 1

I will describe here a method that works, I am not sure if there are other methods to achieve this, but this solution is tested and provides a general mechanism to invoke a form in one VBA application from another VBA application.

1) In the excel workbook, add a macro, call it for example ShowFormWithParams(param1, param2 etc). This macro shows the form in non-modal mode (non-blocking), so that you can continue executing your code while the form shows up.

    ' placed in code Module1
    Sub showFormWithValues(lbl1 As String, txt1 As String, chk1 As Boolean)
        With UserForm1
            .Show False
            .Label1.Caption = lbl1
            .TextBox1.Text = txt1
            .CheckBox1.Value = chk1
        End With
    End Sub

2) In the other application, say Word, Access, or even another Excel Workbook, you can invoke this macro and give it the appropriate parameters in the following way:

Sub mySub()
    Dim xlApp As Excel.Application, xlWB As Excel.Workbook
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open("C:\Test.xlsm") ' specify file
    xlApp.Visible = True
    xlApp.Run "Module1.showFormWithValues", "ABCD", "foooo", True
End Sub

Now the form shows up and displays already the desired values.

I should say that there must be some way to get a handle to the userform and control it explicitly from VBA code, but that seems a difficult task and I did not try it yet. Hope this helps.

Answer 2

yes. You could create a public procedure in excel that opens and sets the value of the controls on the excel form. You can then call this procedure from access.
Perhaps providing arguments that are used to set the control values.

Sub ASubInAccess()


    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook

    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open("C:\Users\Harvey\OneDrive\My Tasks\StackOverflow\testuserform.xlsm")

    xlApp.Visible = True

    xlWB.Application.Run "AnSubInExcel", "Hello"


End Sub

Public Sub AnSubInExcel(ValueFor_textBox1 as string )
    UserForm1.TextBox1 = ValueFor_textBox1

    UserForm1.Show
End Sub

Sorted?

If not, there is another approach see the answer here but this looks tricker.

Also see here which has useful code. The benefit of this last link is that you can specify the name of the excle userform from access using chip's procedure:

Sub SetPropertyAtRunTime()

    Dim FormName As String
    Dim ControlName As String
    Dim ProcName As String
    Dim CallType As VbCallType
    Dim Res As Variant
    Dim Value As Variant

    FormName = "UserForm1"
    ControlName = "Label2"
    ProcName = "Caption"
    CallType = VbLet
    Value = "New Caption Text"

    Res = ControlValueByName(FormName:=FormName, ControlName:=ControlName, _
        ProcName:=ProcName, CallType:=CallType, Value:=Value)
    ShowAnyForm FormName

End Sub