EXCEL - accessing workbook.A.userform from workbook.B

[pod]

Hello

What I want to do is:
from this workbook (thisWB), I want to access a userform's properties that are in another Workbook (toolWB)

I can do this for worksheets, but I want to do it as well for the userform

I have searched the help files, forums and went through all of the properties of workbooks and worksheets to no avail.

I have a way to go around my problem but not without rewriting a lot of code.

If someone has any ideas on the subject, please let me know.

Option Explicit ' this code is on a thisWB module
Public toolWB As Workbook
Public deskSHEET As Worksheet
Public deskFRM As UserForm
Sub setToolWB()
    Set toolWB = toolWB
    Set deskSHEET = toolWB.Sheets("Desk Bureau") ' this works
    Set deskFRM = toolWB.[???]         ' HELP!
End Sub

Thanks


P :oD

[Dököll]

Hey there pod!

Good job posting a sample code... Sending over to the Access forum for looks.

Good luck with this project, in a bit!

[ADezii]

Hello

What I want to do is:
from this workbook (thisWB), I want to access a userform's properties that are in another Workbook (toolWB)

I can do this for worksheets, but I want to do it as well for the userform

I have searched the help files, forums and went through all of the properties of workbooks and worksheets to no avail.

I have a way to go around my problem but not without rewriting a lot of code.

If someone has any ideas on the subject, please let me know.

Option Explicit ' this code is on a thisWB module
Public toolWB As Workbook
Public deskSHEET As Worksheet
Public deskFRM As UserForm
Sub setToolWB()
    Set toolWB = toolWB
    Set deskSHEET = toolWB.Sheets("Desk Bureau") ' this works
    Set deskFRM = toolWB.[???]         ' HELP!
End Sub

Thanks


P :oD

It wasn't easy, but I figured out a Method to programmaticall y access/set the Properties of a UserForm contained within the same Workbook as the code execution. It may not be the easiest partial solution, but it does work. It should not be a hugh leap to use this logic with an External Workbook. First a few pointers:

1. Tools
2. Macro
3. Security
4. Trusted Publishers Tab
5. Select ==> Trust access to Visual basic Project

Dim VBC As Object      'UserForm VBComponent
Set VBC = Workbooks("Book1.xls").VBProject.VBComponents("UserForm1")

VBC.Designer.Controls("Label1").Caption = "Help me Rhonda, please!"

[pod]

Thanks ADezii for pointing me in the right direction. I am not out of the woods yet, I am having a hard time now displaying the Userform, not to mention setting the rowsource to list controls on the form.
But I just found a good Web Reference on "VBProject.VBCo mponents" @http://www.cpearson.com/excel/vbe.aspx

P:oD