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

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • pod
    Contributor
    • Sep 2007
    • 298

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

    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.

    Code:
    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
    Recognized Expert Top Contributor
    • Nov 2006
    • 2379

    #2
    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!

    Comment

    • ADezii
      Recognized Expert Expert
      • Apr 2006
      • 8834

      #3
      Originally posted by 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.

      Code:
      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

      Code:
      Dim VBC As Object      'UserForm VBComponent
      Set VBC = Workbooks("Book1.xls").VBProject.VBComponents("UserForm1")
      
      VBC.Designer.Controls("Label1").Caption = "Help me Rhonda, please!"

      Comment

      • pod
        Contributor
        • Sep 2007
        • 298

        #4
        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

        Comment

        Working...