By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,012 Members | 988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,012 IT Pros & Developers. It's quick & easy.

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

pod
100+
P: 298
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.

Expand|Select|Wrap|Line Numbers
  1. Option Explicit ' this code is on a thisWB module
  2. Public toolWB As Workbook
  3. Public deskSHEET As Worksheet
  4. Public deskFRM As UserForm
  5. Sub setToolWB()
  6.     Set toolWB = toolWB
  7.     Set deskSHEET = toolWB.Sheets("Desk Bureau") ' this works
  8.     Set deskFRM = toolWB.[???]         ' HELP!
  9. End Sub
Thanks


P :oD
Nov 20 '09 #1

✓ answered by ADezii

@pod
It wasn't easy, but I figured out a Method to programmatically 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
Expand|Select|Wrap|Line Numbers
  1. Dim VBC As Object      'UserForm VBComponent
  2. Set VBC = Workbooks("Book1.xls").VBProject.VBComponents("UserForm1")
  3.  
  4. VBC.Designer.Controls("Label1").Caption = "Help me Rhonda, please!"

Share this Question
Share on Google+
3 Replies


Dököll
Expert 100+
P: 2,364
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!
Nov 21 '09 #2

ADezii
Expert 5K+
P: 8,623
@pod
It wasn't easy, but I figured out a Method to programmatically 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
Expand|Select|Wrap|Line Numbers
  1. Dim VBC As Object      'UserForm VBComponent
  2. Set VBC = Workbooks("Book1.xls").VBProject.VBComponents("UserForm1")
  3.  
  4. VBC.Designer.Controls("Label1").Caption = "Help me Rhonda, please!"
Nov 22 '09 #3

pod
100+
P: 298
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.VBComponents" @http://www.cpearson.com/excel/vbe.aspx

P:oD
Nov 23 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.