1

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?

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Hey, you wrote the same answer as me! I wish SO would show how many people are typing answers!
@HarveyFrench yeah... I was working on it since some while because I was asked about it in another post... :). But i was minutes ahead of you :P
@HarveyFrench notice I prefer to lauch the form in non-modal mode here, to unblock any further code in the original application... But this depends on what the programmer wants to do
I find it quite surprising that you can open a xlsm workbook using VBA from another workbook/access and run VBA code in a module it stores. When I open the same workbook manually, my trust centre settings cause a a security warning to be given and have to click enable content! This can't secure surely!
Yes, the user needs to do some setup previously in the trust center, such as add the location holding the Excel file to the trusted locations. But this wouls have been another issue.
|
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
Improve this answer

1 Comment

Thanks @HarveyFrench! Appreciate the feedback.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.