Update an excel VBA userform from MS Access VBA

[DaveBenson]

Hi,

Does anyone know how to update the value of an excel vba userform control from access VBA?

 

[PHV]

Provided the UserForm is loaded:
UserForm1.NameOfControl.Value = UpdatedValue

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DaveBenson]

Hi,

Thanks for this, although it doesn't seem to work. If I'm in Excel VBA and have the userform open it does. But with Access VBA I don't think it's recognising 'userform1' as an excel userform. I need some way of pointing MS Access to the userform in the MS excel file.

 

[SkipVought]

How about posting ALL your code so we can see exactly what you are referring to.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

What about something like this ?
yourXLobj.UserForm1.NameOfControl.Value = UpdatedValue

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DaveBenson]

OK, there's lots of ways I will use this code once I get it working. But to give an example, the excel file that is open is called "pivot control.xls". Within this, there is a loaded userform called "dk", with a textbox called "txtname".
From a module in my Access file I want to be able to control the value of "txtname". If I try:

Public Sub example()
Dim XL_APP As Object
Set XL_APP = GetObject(, "Excel.Application")
Dim XL_BOOK As Excel.Workbook
Set XL_BOOK = XL_APP.Workbooks("Pivot control.xls")
XL_BOOK.dk.txtname.Value = "Test"

End Sub

If I try to run this, I get the message "Object doesn't support this property or method" for the last line.

 

[SkipVought]

Have you looked at the Object Browser? Objects have PARENT objects. You have to have the correct parent.

XL_BOOK.dk.[b][i]SomeFormObject[/i][/b]txtname.Value = "Test"

assuming that txtname is a Control on a FORM.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DaveBenson]

Hi Skip,

I don't quite follow (admittidly I'm a bit hazy on objects etc!)
"txtname" is a textbox control within the userform "dk"
I loosely thought that this would make "dk" the parent object of "txtname". Does there need to be something else in there as well?
I had thought that the problem was higher up in the hirachy ie xl_book.dk is not being recognised as the userform dk - if I was trying to do the update from Excel VBA, dk.txtname.value="test" would work ok.

Thanks,

Dave